feel free to hang here, note that some of the "chat" happens on libera.chat #sqlalchemy
sequence_ticket_number.next_value() part resolves
Hi all.
Using ORM.
I have a O2M relationship that I set to lazy="raise" (addresses in User table).
Now I want to use the relationship User.addresses in a query:result = await async_session.execute(User.addresses.statement)
But it complains saying AttributeError: 'InstrumentedList' object has no attribute 'statement'
I don't want to change the lazy so that I don't accidentally call it.
lzay="dynamic"https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#preventing-implicit-io-when-using-asyncsession
(Last point)
Another question: How to joinedload and selectinload on the same table?
Example:
User - O2M - Addresses
User - M2O (FK) - Group
This doesn't work:select(User).options(joinedload(User.group).selectinload(User.addresses))
select(User).options(joinedload(User.group).selectinload(User.addresses))
you should not chain them since aggresses is not part of Group. they should be separated by comma, since are both "top level" (of user) select(User).options(joinedload(User.group), selectinload(User.addresses))
I have 3 classes that all share some attributes - (id, entry time, owner, category) and I would like to be able to query them all at once.
So say I need to search for class where entry time < datetime I could easily retrieve all classes where that would be the case. Not knowing much about sqlalchemy I figured the easiest solution would be an abstract class that would then be used for querying but it seems that is not supported?
lazy="raise", which would raise an exception each time I try to access the respective attribute. Is there a way to load such an "unloaded" relationship after I have already obtained the object from the ORM? According to the documentation, Session.refresh()doesn't automatically refresh unloaded relationships. How do I load the relationship, then? I'm aware of joinedload() & friends, but in case of a large object graph I would like to avoid eagerly loading the whole database with every single query.
async with aiofiles.open(
directory, mode="r", encoding="utf-8", newline=""
) as afp:
async for new in AsyncDictReader(afp):
query = ScheduleService(
**{
"id": new["id"],
"name": new["name"],
"type": new["type"],
"title": new["title"],
"description": new["description"],
"date": datetime.strptime(
new["date"], settings.DATE
).date(),
"there_is": datetime.strptime(
new["there_is"], settings.DATETIME_FORMAT
),
"created_at": datetime.now(),
"sch_s_owner": new["sch_s_owner"],
"sch_s_service_id": new["sch_s_service_id"],
}
)
# ..
session.add_all(query)
session.refresh([query])
await session.commit()
self.mergte_result(..., load=False) I get: AttributeError: '<MyModel>' object has no attribute '_sa_instance_state' does anyone know if this is me making a mistake/wrong-assumption or is the tutorial that is missing something?
db.session.commit(), without needing an active DB connection?
class MyModel(sqlalchemy.Model) with type = db.Column(db.Text()) and I assign myModelObj.type = MyTypeEnum.MY_TYPE1. Before I do db.session.commit(), I see myModelObj.type == MyTypeEnum.MY_TYPE1 -- but after I do db.session.commit(), a transformation has happened and myModelObj.type == "MY_TYPE1"
Hello SQLAlchemy community, I am hoping to get some guidance on the issue below:
the metadata catalog tool Open MetaData https://docs.open-metadata.org/openmetadata/connectors/database/db2 is using SQLAlchemy to connect to a Db2 LUW database. The below packages are installed :
apt-get install libxml2
pip install "ibm-db==3.1.3"
pip install "ibm-db-sa==0.3.8"
But when it tries to connect, it leads to error below:
Failed to test connection due to {"error": "Connection error from Engine(db2+ibm_db://dt224374:@db2databaseurl:50000/TPJ) - Unknown error connecting with Engine(db2+ibm_db://dt224374:*@db2databaseurl:50000/TPJ) - (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "1.3.1"} / SELECT 42". Expected tokens may include: "<table_expr>". SQLSTATE=42601 SQLCODE=-104\n[SQL: / {"app": "OpenMetadata", "version": "0.11.3.1"} /\nSELECT 42]\n(Background on this error at: https://sqlalche.me/e/14/f405)."}
It is pointing to the SQLAlchemy page https://sqlalche.me/e/14/f405 but this does not help in identifying what the issue could be.
If we use these packages ibm-db, ibm-db-sa directly in a Python script to connect to this DB2, we get the same error. It seems as though the generated query 'SELECT 42' here is wrong. DB2 expects that query to be 'SELECT 42 FROM SYSIBM.SYSDUMMY1;'
Hello all.
I met a runtime error with msg: "deque mutated during iteration" which comes from sqlalchemy/event/attr.py line 255. (with sqlalchemy ver. 1.4.39)
The error shown since I added event listener on "before_commit" event as below:event.listen(my_scoped_session, "before_commit", my_func, propagate=True)
But I have no idea how to solve this.
Does anyone know the root cause?
This is my first time to leave msg here, it will be appreciated if anyone knows the root cause.
Hi folks, I'm facing a very high latency (200s/call) while doing INSERTs into a specific table in my DB. I did not face this issue while using Django ORM, but when I switched to SQLAlchemy ORM, it cropped up.
Here's the code doing the inserts:
class MyRepository():
def __init__(self):
_engine = create_engine(dburl, executemany_mode='batch', executemany_batch_page_size=10000, pool_size=20, max_overflow=10)
_session_maker = sessionmaker(bind=_engine)
self.session = _session_maker()
def bulk_create_records(self, objects): #max 50 objects passed
with self.session.begin():
self.session.bulk_save_objects(objects, return_defaults=True)
self.session.expunge_all()
return objectsInvocation: records = MyRepository().bulk_create_records(record_objects)
I'm connecting to a PostgreSQL RDS instance & the highest wait events with these inserts ("INSERT into mytable (cola, colb, ...) VALUES (?, ?, ...) RETURNING mytable.id;") are caused by SubtransControlLock and subtrans events.
What subtransactions are getting triggered here & how can I eliminate this issue?
Hi, I'm new to this sql package. I'm trying to connect to the sql server with the following link
connection_url = URL.create("mssql+pymssql://Username:Password@xx.103.xxx.xx/dbname")
engine = db.create_engine(connection_url)
connection = engine.connect()and get this error
dialect, driver = name.split(".")
ValueError: too many values to unpack (expected 2)