feel free to hang here, note that some of the "chat" happens on libera.chat #sqlalchemy
cur.execute('select 1', {}) where type(cur) is psycopg2.extensions.cursor
Hi,
We are trying to use SQLAlchemy compile to generate queries in different dialects. It is somehow not working for complex queries.
QUERY
SELECT partsupp.ps_supplycost, region.r_regionkey FROM region JOIN partsupp ON
(partsupp.ps_partkey 698) < region.r_regionkey
SELECT OBJECT
<class 'sqlalchemy.sql.selectable.Select'>
SELECT partsupp.ps_supplycost, region.r_regionkey
FROM region JOIN partsupp ON
(partsupp.ps_partkey + %(ps_partkey_1)s) < region.r_regionkey
USAGE
I've got a noob question.
https://gist.github.com/comargo/0ca997e45ce2d59ca456f556c6d5a07d
How can I make
relationship(..., backref="...") populate parent class before using it?
back_populates
I've been a relatively casual user of SQLALchemy until now, but I'm trying to really get a grip on Session behavior. I'm having trouble understanding a description of Session identity map behavior in the "Querying (1.x Style)" section of the documentation here: https://docs.sqlalchemy.org/en/14/orm/session_basics.html
The description I'm struggling with is this:
When ORM objects are returned in results, they are also stored in the identity map. When an incoming database row has a primary key that matches an object which is already present, the same object is returned, and those attributes of the object which already have a value are not re-populated.
I understand that first sentence - but I am struggling with the second one. It seems like its describing the behavior when a returned row has a database identity that matches an object that is already in the identity map...and maybe how it resolves conflicts if their attributes do not have matching values - but I can't quite understand it.
Can somebody reword that - or even better - suggest a code snippet that I can run to better understand what is going on? Thanks
engine1 = sa.create_engine(connection_uri)
engine2 = sa.create_engine(connection_uri)
class Thing(Base):
__tablename__ = "thing"
id = Column(Integer, primary_key=True, autoincrement=False)
txt = Column(String(50))
Base.metadata.drop_all(engine1)
Base.metadata.create_all(engine1)
with Session(engine1) as session1:
session1.add(Thing(id=1, txt="from session1"))
session1.commit()
thing1 = session1.get(Thing, 1) # re-retrieve object into identity map
with engine2.begin() as conn2:
# simulate update from external process
conn2.exec_driver_sql("UPDATE thing SET txt = '***updated***' WHERE id = 1")
x = session1.execute(select(Thing).where(Thing.id == 1)).scalar()
print(f"x and thing1 {'ARE' if x == thing1 else 'are NOT'} the same object")
# x and thing1 ARE the same object
print(f"thing1.txt is '{thing1.txt}'")
# thing1.txt is 'from session1'
session1.expire(thing1) # force reload on subsequent access
print(f"thing1.txt is '{thing1.txt}'")
# thing1.txt is '***updated***'@gordthompson Yes, thankfully (though not unexpectedly...as "thing1" and "x" are the same object) - their "txt" attributes have the same value. OK, so I think I understand what's going on here...and here is how I know have rewritten that 2nd sentence in my own mind:
When an incoming database row has a primary key that matches an object which is already present, the same object is returned, and those attributes of the object which already have a value will continue to return that value - even if the value returned by the row is different.
(In effect, the returned row does not overwrite the existing attribute value - should one already be set - on the object.
Have I got it right?
print(model,Cluster,type(model),type(Cluster),isinstance(model, Cluster),str(model)) yields <class 'app.models.Cluster'> <class 'app.models.Cluster'> <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'> <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'> False <class 'app.models.Cluster'> - not sure why isinstance evaluates to False as the type and string representation are the same
1 reply
