CODEWITHSUNDEEP
pythonormsqlalchemy
kes
kes

Reputation: 6167

SQLAlchemy: selecting which columns of an object in a query

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?

Upvotes: 34

Views: 71135

Answers (3)

Eli
Eli

Reputation: 4926

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

Upvotes: 32

Devi
Devi

Reputation: 5443

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

Upvotes: 9

zzzeek
zzzeek

Reputation: 75127

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

Upvotes: 24

Related Questions