On Oct 17, 2013, at 12:51 PM, John Kida <[email protected]> wrote: > I am trying find a nice automatic way to serialize my sql alchemy result sets > when doing joins. > > Lets say i have the following 2 models, User and Message: > class User(Base): > __tablename__ = '...' > id = Column(...) > name = Column(...) > last_login Column(...) > > and > > class Message(Base): > __tablename__ = ... > id = Column(...) > subject = Column(...) > body = Column(...) > date = Column(...) > creator_id = Column(...) > > Now, if I do a query like: > > Session.query(User.id, User.name, Message.id, Message.body).all() > > How can i get back a list of User and Message objects, with all the variables > of those objects being None types except for what i selected in the query. > ie. User<id = 23, name='John', last_login=None>, Message<id=123, > subject=None, body='what ever', date=None, creator_id=None>
one approach is you can use 0.9, which I should be starting to release in the
next month or two, and use load_only():
from sqlalchemy.orm import Load
Session.query(User, Message).options(Load(User).load_only("id",
"name"), Load(Message).load_only("id", "body"))
this will defer the other attributes - they won't be None, they'll just be
unloaded. If you access them, they will emit a SELECT to load remaining
columns (e.g. they are deferred). so this might not work for your JSON use
case, not sure. There's not a built in mechanism for hardwiring object
attributes to None in a query, that's an odd use case since that's not the
value of that attribute in the database (e.g. why None and not, say "7"? )
another, well it's also new in 0.9 too, sorry :) its called "Bundle", this
just allows you to nest the tuples returned by Query. You won't get a User or
Message object, you'll get nested tuples:
from sqlalchemy.orm import Bundle
for row in Session.query(Bundle("user", User.id, User.name),
Bundle("message", Message.id, Message.body)):
print row.user.id, row.user.name, row.message.id,
row.message.body
Bundle is likely better for JSON serializing, but the returned objects are just
keyed tuples. Again having the other attrs as None is not really built into
this unless you added all those attrs to the Bundle. You can make your own
subclasses of Bundle though, so in theory you could build up all kinds of
interesting things with them.
This stuff is all 0.9 though. In 0.8 you'd have to do something more hacky
(like wrap your Query object).
signature.asc
Description: Message signed with OpenPGP using GPGMail
