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

或者您可以通过配置或使用选项在映射的类上将各种列设置为"deferred":

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

http://docs.sqlalchemy.org/zh_CN/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中为

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屋！