How to map the result from a query to a custom object in sqlalchemy?

Question

I'm searching a way to tell sqlalchemy to map a complex query over some tabes to a custom class MyResult instead of the default RowProxy class. Here's a simple working example

'''
create table foo(id integer, title text);
create table bar(id integer, foo_id integer, name text);
insert into foo values(0, 'null');
insert into foo values(1, 'eins');
insert into bar values(0,0, 'nullnull');
insert into bar values(1,0, 'einsnull');
insert into bar values(2,1, 'zweieins');
'''

and the following code:

from sqlalchemy import *
from itertools import imap

db = create_engine('sqlite:///test.db')
metadata = MetaData(db)

class MyResult(object):
    def __init__(self, id, title, name):
        self.id = id
        self.title = title
        self.name = name

foo = Table('foo', metadata, autoload=True)
bar = Table('bar', metadata, autoload=True)

result = select([foo.c.id, foo.c.title, bar.c.name], foo.c.id == bar.c.foo_id).execute().fetchall()

Now I'm looking for a way to tell sqlalchemy to perform a mapping from the result rows to MyResult.

row = result[0]
print type(row)
#<class 'sqlalchemy.engine.base.RowProxy'>
print row.items()
#[(u'id', 0), (u'title', u'null'), (u'name', u'einsnull')]

I know I can do the mapping by hand with something like

my_result = imap(lambda x: MyResult(**x), result)

but I have the feeling that this is not the way to handle it in sqlalchemy.

Answer 1

As can be seen from your sample, there will be more than 1 Foo returned for Foo.id = 0, which will result in the duplicate value for the primary key, which will in turn only result in a subset of your result-set being returned. In this case you probably should extend the primary_key also to other Bar columns (either include Bar.id or use Bar.name if it is unique).

Then you can use the from_statement (as documented in Using Literal SQL) to achieve this:

sql_qry = select([foo.c.id.label("id"), 
                  foo.c.title.label("title"), 
                  bar.c.name.label("name")], 
                 foo.c.id == bar.c.foo_id)
my_result_qry = session.query(MyResult).from_statement(sql_qry)
for x in my_result_qry.all():
    print x

However, the model MyResult has to be mapped. You can map it to some dummy (non-existant) table or view. Also the labels for columns are important as they must exactly match your column definitions of the class (the constructor will not be used anyways).