pythonormsqlalchemyobject-relational-model

connect SQLAlchemy ORM with the objects from sql core expression?

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 ?

Solution

  • 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: