如何将查询结果映射到sqlalchemy中的自定义对象? [英] How to map the result from a query to a custom object in sqlalchemy?

问题描述

我正在寻找一种告诉sqlalchemy将某些表上的复杂查询映射到自定义类MyResult而不是默认RowProxy类的方法.这是一个简单的工作示例

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');
'''

和以下代码:

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

现在，我正在寻找一种方法来告诉sqlalchemy执行从结果行到MyResult的映射.

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)

但是我感觉这不是在sqlalchemy中处理它的方法.

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

推荐答案

从您的示例可以看出，Foo.id = 0返回的Foo超过1，这将导致主键的值重复，反过来，这只会导致返回结果集的子集.在这种情况下，您可能还应该将primary_key扩展到其他Bar列(包括Bar.id或使用Bar.name(如果唯一)).

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

然后，您可以使用 from_statement (如使用文字SQL )来实现:

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

但是，必须映射模型MyResult.您可以将其映射到某个虚拟(不存在)的表或视图.列的label也是很重要的，因为它们必须与您对类的列定义完全匹配(无论如何，构造器将不使用)

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

这篇关于如何将查询结果映射到sqlalchemy中的自定义对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！