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.

• van: The example is not really complex, is the real world problem complex? Most importantly though: what is "real-life" relationship between these two tables: a) it is in fact a 1-1 relationship between tables, and two combined rows represent one "Object" instance (a-la inheritance); b) single Foo can be referenced by may Bars (1-n relationship). In this case your mapping would not work, as the id column (used as PK) might not be unique. x) are these view-only or would you like to be able to add new DB rows by creating and saving new MyResult instances?
• Peter Hoffmann: Real world example is a join over 4 Tables. 1-n relationship from snow flake schema. I'm aware of the id problem, but can solve it with either a joined unique id or some other way. Saving instances is not required.

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

Answer 2

By calling select directly you are leaving out the ORM features. You need to use mapper on your MyResult class. As you have it, MyResult is just an ordinary class.

Something like this:

Foo = mapper(MyResult, foo)
Bar = mapper(MyResult, bar) # NOTE: MyResult itself is unchanged by this
session = Session()
# query against the mapper class
result = session.query(Foo).filter(Foo.title == 'xyz').one()
print result.name

• wberry: You can also create a "declarative" base class and then make MyResult a subclass of it. Many people prefer the declarative style but it does have trade-offs. docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html