Returning multiple table models on join query instead of keyed tuple

[John Kida]

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>

This would help alot when i goto serialize my objects into JSON,... i am trying to find an automatic way to serialize my queries into JSON but keep the model/table data. ie.

{"User": { "id":23, "name":"John", "last_login":null}, "Message": {"id":123, "subject":null, "body":"what ever", "date": null, creator_id: null}, ..more rows }

If there is no way to actually return the model when specifying columns, im guess there is a way to know what table that value came from, and construct it that way?

Any ideas?

[Michael Bayer]

On Oct 17, 2013, at 12:51 PM, John Kida <jdk...@gmail.com> 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).

[John Kida]

The Bundle option sounds perfect, there is really no need for the other attrs to be None, i was just trying to explain the idea. Is there a nightly avaliable of 0.9 that I can grab that has a partially working version of Bundle?

[Michael Bayer]

its totally working, just grab 0.9 from github.

On Oct 17, 2013, at 2:35 PM, John Kida <jdk...@gmail.com> wrote:

The Bundle option sounds perfect, there is really no need for the other attrs to be None, i was just trying to explain the idea. Is there a nightly avaliable of 0.9 that I can grab that has a partially working version of Bundle?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.