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.

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? — van
– van, Commented Apr 17, 2012 at 9:55
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. — Peter Hoffmann
– Peter Hoffmann, Commented Apr 19, 2012 at 12:34

van · Accepted Answer · 2012-04-17 15:55:25Z

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

wberry · Accepted Answer · 2012-04-16 21:52:54Z

5

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

answered Apr 16, 2012 at 21:52

wberry

19.5k9 gold badges59 silver badges89 bronze badges

1 Comment

wberry Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related