连接SQLAlchemy ORM与来自sql核心表达式的对象? [英] connect SQLAlchemy ORM with the objects from sql core expression?

问题描述

我必须使用SQLalchemy Core表达式来获取对象，因为ORM无法执行更新并返回". (ORM中的更新没有returning)

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()

何时

db_session.add(object)

它报告UnmappedInstanceError: Class 'sqlalchemy.engine.result.RowProxy' is not mapped.

如何将RowProxy对象从sql表达式放入ORM的标识映射中 ?

How do I put that RowProxy object from sql expression into identity map of ORM ?

推荐答案

简单情况:

可能的快速解决方案:从RowProxy的kwargs构造对象，因为它们类似于对象.

Simple case:

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

给出:

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

我们也许可以做到:

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

rowproxy.items()返回key-value对中的tuples； dict(...)将tuples转换为实际的key-value对；并且User(...)的model属性名称使用kwargs.

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.

但是如果您有一个model，其中attribute names之一与SQL table column name不太一样怎么办?例如.像这样:

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)

当我们尝试将rowproxy打包到User类中时，我们可能会收到以下错误:TypeError: 'id' is an invalid keyword argument for User(因为它期望使用user_id代替).

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).

现在它变脏了:我们应该在mapper周围，以了解如何从table属性转换为model属性，反之亦然:

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}

在这里，a.key是model attribute(和kwarg)，而a.class_attribute.name是table attribute.这给了我们类似的东西:

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"
}

好吧，我们实际上想提供从rowproxy中获得的值，除了允许类似对象的访问之外，还允许类似dict的访问:

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}

现在我们可以做:

user = User(**kwargs)

勘误表:

• 您可能想在调用update().returning()之后立即session.commit()，以防止由于更改而导致的长时间延迟(与将更改永久存储在数据库中时相比).以后无需session.add(user)-您已经updated()并且只需要commit()该交易
• object是Python中的关键字，因此请不要踩踏它.这样做可能会导致一些非常奇怪的行为；这就是为什么我将其重命名为rowproxy.

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.

这篇关于连接SQLAlchemy ORM与来自sql核心表达式的对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！