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 ?

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. — dwanderson
– dwanderson, Commented Nov 9, 2017 at 23:15
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. — dwanderson
– dwanderson, Commented Nov 9, 2017 at 23:20

ACV · Accepted Answer · 2017-01-09 21:17:29Z

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

answered Jan 9, 2017 at 21:17

ACV

1,9852 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dwanderson · Accepted Answer · 2017-11-10 00:10:55Z

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.

Collectives™ on Stack Overflow

connect SQLAlchemy ORM with the objects from sql core expression?

2 Answers 2

Comments

Simple case:

More difficult case:

Errata:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Simple case:

More difficult case:

Errata:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related