connect SQLAlchemy ORM with the objects from sql core expression?

Question

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 ?

Ytterbite

It seems like an oversight by SQLAlchemy that something like update-returning, which (typically) returns the whole table being updated, doesn't have a simple way to map back to objects. Unfortunate there's not a happier answer for this. – Toothsome

You may be able to get some mileage out of: obj = User(**dict(object.items())) but it doesn't appear to work in all instances. – Toothsome

Answer 1

Simple case:

Possible quick solution: construct the object from kwargs of your RowProxy, since those are object-like.

Given:

rowproxy = update(User)  \
    .values({'name': 'Wayne'})  \
    .where(User.id == subquery.as_scalar()) \
    .returning() \
    .fetchone()

We might be able to do:

user = User(**dict(rowproxy.items()))

rowproxy.items() returns tuples of key-value pairs; dict(...) converts the tuples into actual key-value pairs; and User(...) takes kwargs for the model attribute names.

More difficult case:

But what if you have a model where one of the attribute names isn't quite the same as the SQL table column name? E.g. something like:

class User(ORMBase):
    # etc...
    user_id = Column(name='id', etc)

When we try to unpack our rowproxy into the User class, we'll likely get an error along the lines of: TypeError: 'id' is an invalid keyword argument for User (because it's expecting user_id instead).

Now it gets dirty: we should have lying around a mapper for how to get from the table attributes to the model attributes and vice versa:

kw_map = {a.key: a.class_attribute.name for a in User.__mapper__.attrs}

Here, a.key is the model attribute (and kwarg), and a.class_attribute.name is the table attribute. This gives us something like:

{
    "user_id": "id"
}

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:

kwargs = {a.key: rowproxy[a.class_attribute.name] for a in User.__mapper__.attrs}

And now we can do:

user = User(**kwargs)

Errata:

• you may want to session.commit() right after calling update().returning() to prevent long delays from your changes vs. when they get permanently stored in the database. No need to session.add(user) later - you already updated() and just need to commit() that transaction
• object 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 to rowproxy.

Toothsome

Answer 2

I'm not sure there is a straight-forward way to do what you're describing, which is essentially to build an ORM object that maps directly to an database entry but without performing the query through the ORM.

My intuition is that the naive approach (just build init the ORM object with the values in the database) would just create another row with the same values (or fail to because of uniqueness constraints).

The more standard way to do what you are asking would be to query the row through the ORM first and then update the database from that ORM object.

user = User.query.filter(User.user_attribute == 'foo').one()
user.some_value = 'bar'
session.add(user)
session.commit()

I'm not sure if you have some constraint on your end that prevents you from using that pattern though. The documentation works through similar examples

Bagasse