SQLAlchemy: selecting which columns of an object in a query

Question

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

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

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

would print:

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

Is this possible; if so, how?

Solution 1

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

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() should really accept multiples, added ticket #2250 for that (edit: as noted in the comment this is in 0.9 as load_only())

Solution 2

A simple solution that worked for me was:

users = session.query(User.userid, User.name)
for user in users:
    print user

would print:

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

Solution 3

Latest doc for load_only is here

http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#load-only-cols

If you're looking at a way to control that at model definition level, use deferred

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

Comments

[kolypto]

In sa>=0.9.0, see load_only()

[MarredCheese]

In your code, user is not a User instance (what the question was about). It's a named tuple. In reality, it would just print (1, 'bob')\n(2, 'joe').

[TheRealChx101]

No weird loops whatsoever. Best answer

[wonkybadonk]

For load_only() I'm probably being silly, but is there a way to pass it a list of orm columns? Right now if I only pass a single ormmodel.col1 explicitly, load_only() accepts it and life is wonderful. But if I instead pass a list of column entities [ormmodel.col1, ormmodel.col2] it barfs in strategy_options.py on line 779 in _bind_loader() "mapper option expects " "string key or list of attributes". Do I need to get fancy in how I pass in the list? Or does each column need to be defined explicitly when load_only is called?