I have to use SQLalchemy Core expression to fetch objects because ORM can't do "update and returning". (the update in ORM doesn't has returning
)
from sqlalchemy import update
class User(ORMBase):
...
# pure sql expression, the object returned is not ORM object.
# the object is a RowProxy.
object = update(User) \
.values({'name': 'Wayne'}) \
.where(User.id == subquery.as_scalar()) \
.returning() \
.fetchone()
When
db_session.add(object)
it report UnmappedInstanceError: Class 'sqlalchemy.engine.result.RowProxy' is not mapped
.
How do I put that RowProxy
object from sql expression into identity map of ORM
?
Simple case:
Possible quick solution: construct the object from
kwargs
of yourRowProxy
, since those are object-like.Given:
We might be able to do:
rowproxy.items()
returnstuples
ofkey-value
pairs;dict(...)
converts thetuples
into actualkey-value
pairs; andUser(...)
takeskwargs
for themodel
attribute names.More difficult case:
But what if you have a
model
where one of theattribute names
isn't quite the same as the SQLtable
column name
? E.g. something like:When we try to unpack our
rowproxy
into theUser
class, we'll likely get an error along the lines of:TypeError: 'id' is an invalid keyword argument for User
(because it's expectinguser_id
instead).Now it gets dirty: we should have lying around a
mapper
for how to get from thetable
attributes to themodel
attributes and vice versa:Here,
a.key
is themodel attribute
(andkwarg
), anda.class_attribute.name
is thetable attribute
. This gives us something like:Well, we want to actually provide the values we got back from our
rowproxy
, which besides allowing object-like access also allows dict-like access:And now we can do:
Errata:
session.commit()
right after callingupdate().returning()
to prevent long delays from your changes vs. when they get permanently stored in the database. No need tosession.add(user)
later - you alreadyupdated()
and just need tocommit()
that transactionobject
is a keyword in Python, so try not to stomp on it; you could get some very bizarre behavior doing that; that's why I renamed torowproxy
.