SQLAlchemy:选择查询中对象的哪些列 [英] SQLAlchemy: selecting which columns of an object in a query

问题描述

是否可以控制在 SQLAlchemy 的查询方法中查询哪些列，同时仍然返回您正在查询的对象的实例(尽管部分填充)?

Is it possible to control which columns are queried in the query method of SQLAlchemy, while still returning instances of the object you are querying (albeit partially populated)?

或者SQLAlchemy是否需要执行SELECT *来映射到一个对象?

Or is it necessary for SQLAlchemy to perform a SELECT * to map to an object?

(我确实知道可以查询单个列，但它不会将结果映射到对象，只会映射到命名元组的组件).

(I do know that querying individual columns is available, but it does not map the result to an object, only to a component of a named tuple).

例如，如果 User 对象具有属性 userid、name、password 和 bio，但您希望查询仅填写它返回的对象的 userid 和 name:

For example, if the User object has the attributes userid, name, password, and bio, but you want the query to only fill in userid and name for the objects it returns:

# hypothetical syntax, of course:
for u in session.query(User.columns[userid, name]).all():
    print u

会打印:

<User(1, 'bob', None, None)> 
<User(2, 'joe', None, None)>
...

这可能吗?如果是这样，如何?

Is this possible; if so, how?

推荐答案

您可以查询单个列，如果您只是传递给模板或东西:

you can query for individual columns, which returns named tuples that do in fact act pretty much like your mapped object if you're just passing off to a template or something:

http://www.sqlalchemy.org/docs/orm/tutorial.html#querying

或者您可以通过配置或使用选项在映射类上建立各种列作为延迟":

or you can establish various columns on the mapped class as "deferred", either configurationally or using options:

http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-column-loading

trac 中有一张旧票用于称为defer_everything_but()"，如果有人想提供测试并且没有理由不能添加功能，这里有一个快速版本:

there's an old ticket in trac for something called "defer_everything_but()", if someone felt like providing tests and such there's no reason that couldn't be a feature add, here's a quick version:

from sqlalchemy.orm import class_mapper, defer
def defer_everything_but(entity, cols):
    m = class_mapper(entity)
    return [defer(k) for k in 
            set(p.key for p 
                in m.iterate_properties 
                if hasattr(p, 'columns')).difference(cols)]

s = Session()
print s.query(A).options(*defer_everything_but(A, ["q", "p"]))

defer() 应该真正接受倍数，为此添加了票号 #2250(如评论中所述，这是在 0.9 中作为 load_only())

defer() should really accept multiples, added ticket #2250 for that (edit: as noted in the comment this is in 0.9 as load_only())

这篇关于SQLAlchemy:选择查询中对象的哪些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！