feel free to hang here, note that some of the "chat" happens on libera.chat #sqlalchemy
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
sqlacodegen --generator dataclasses
Hi, I am trying to run a SQL raw query in ORM method. But the SQL Alchemy returns error and that query isn't I am expecting. Only issue with the FROM part in the query, My raw query has only one table in FROM keyword and other two tables are in JOIN where as in sqlalchemy the JOIN is okey but two tables coming with FROM keyword.
SQL Alchemy query:
result = (
session.query(ProductionMaster.article, Barcode.size, sa.func.count(Barcode.size))
.join(Barcode, Barcode.serial_no == ProductionScan.serial_no)
.join(ProductionMaster, ProductionMaster.prod_code == Barcode.prod_code)
.filter(
sa.and_(
ProductionScan.date >= "2021-08-01 08:00:00",
ProductionScan.date <= "2021-08-25 08:00:00",
ProductionMaster.article.like("%3780%black%"),
)
)
.group_by(ProductionMaster.article, Barcode.size)
.all()Error message I am getting: The multi-part identifier "tbl_ProductionScan.serial_no" could not be bound. (4104) (SQLExecDirectW)')
returned QUERY with the error message:
SELECT [Tbl_ProductionMaster].[ProductionName] AS [Tbl_ProductionMaster_ProductionName], [Tbl_barcode].[Size] AS [Tbl_barcode_Size], count([Tbl_barcode].[Size]) AS count_1
FROM [tbl_ProductionScan], [Tbl_ProductionMaster]
JOIN [Tbl_barcode] ON [Tbl_barcode].serial_no = [tbl_ProductionScan].serial_no
JOIN [Tbl_ProductionMaster] ON [Tbl_ProductionMaster].[ProductionCode] = [Tbl_barcode].[Product_Code]
WHERE [tbl_ProductionScan].prod_date >= ? AND [tbl_ProductionScan].prod_date <= ? AND [Tbl_ProductionMaster].[ProductionName] LIKE ?
GROUP BY [Tbl_ProductionMaster].[ProductionName], [Tbl_barcode].[Size]The actual query I am expecting: (This works fine)
SELECT [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName]
,[barcode].[dbo].[Tbl_barcode].[Size]
,COUNT([barcode].[dbo].[Tbl_barcode].[Size])
FROM [barcode].[dbo].[tbl_ProductionScan]
INNER JOIN [barcode].[dbo].[Tbl_barcode] ON [barcode].[dbo].[Tbl_barcode].[Serial_no] = [barcode].[dbo].[tbl_ProductionScan].[serial_no]
INNER JOIN [barcode].[dbo].[Tbl_ProductionMaster] ON [barcode].[dbo].[Tbl_ProductionMaster].[ProductionCode] = [barcode].[dbo].[Tbl_barcode].[Product_code]
WHERE [barcode].[dbo].[tbl_ProductionScan].[prod_date] BETWEEN '2021-08-01 08:00:00' AND '2021-08-25 08:00:00' AND [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName] Like '%3780%black%'
GROUP BY [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName], [barcode].[dbo].[Tbl_barcode].[Size]
Hi everyone, i have a question about selecting only few columns from the Parent relationship,
I have this two tables:
class Parent(Base):
tablename = 'parent'
id = Column(Integer, primary_key=True)
label = Column(String)
children = relationship("Child", back_populates="parent")
class Child(Base):
tablename = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('public.parent.id'))
parent = relationship("Parent", back_populates="children")
when i use
db.query( models.Parent).first()
i get the parent object with the list of Children as i was expecting, but what i would like to do is to select only few columns like that:
db.query( models.Parent.id, models.Parent.children)
Hi all. I have a column (postgresql 14) that I thought would update on every row update (1.4.27), declared as follows:
class utcnow(expression.FunctionElement): # pylint: disable=W0223
type = DateTime()
...
updated_at = Column(
DateTime(True),
nullable=False,
onupdate=utcnow(),
server_default=utcnow(),
index=True,
)This was what I came up with. I had the impression it works... sometimes. What am I missing?
