我有一些代码看起来很糟糕:
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class OneThing(Base):
__tablename__ = 'one'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Text)
value = sa.Column(sa.Text)
class OtherThing(Base):
__tablename__ = 'other'
id = sa.Column(sa.Integer, primary_key=True)
some_deal = sa.Column(sa.Text)
other_deal = sa.Column(sa.Text)
Base.metadata.create_all(engine)
session = sa.orm.sessionmaker(bind=engine)()
one = OneThing()
one.id = 42
one.name = 'hi'
one.value = 'roscivs'
session.add(one)
other = OtherThing()
other.id = 42
other.some_deal = 'brown'
other.other_deal = 'and sticky'
session.add(other)
session.commit()
现在,我可以运行以下内容:
for row in session.query(OneThing).outerjoin(OtherThing, OneThing.id == OtherThing.id):
print(row)
它将打印一行。显然,我没有__repr__
,所以它只会打印出很难看的默认repr。但是,假设我想要other_deal
值,所以我放入print(row.other_deal)
。
我有以下错误:
Traceback (most recent call last):
File "test.py", line 43, in <module>
print(row.other_deal)
AttributeError: 'OneThing' object has no attribute 'other_deal'
确实您可以看到发射的SQL并不是我想要的:
SELECT one.id AS one_id, one.name AS one_name, one.value AS one_value
FROM one LEFT OUTER JOIN other ON one.id = other.id
it 是执行外部连接...但是它仅从one
表中返回列。我想要one
和 other
。我该如何得到?
我已经尝试了add_entity
,但这没有做我想要的。
有什么线索我如何从模型中获得列?(请注意,我无法在两个表之间添加ForeignKey
关系)
首先从其他一些事情开始,我们将回答您的实际问题。
first ,declarative_base
提供了一个关键字参数构造函数,所以代替了:
one = OneThing()
one.id = 42
one.name = 'hi'
one.value = 'roscivs'
您可以写:
one = OneThing(id=42, name='hi', value='roscivs')
第二:假设两个表之间存在数据库关系,则可以使用relationship
定义来告知sqlalchemy
模型之间的关系。在这里,我假设id
列到id
列有外键,这是一对一的关系:
class OtherThing(Base):
...
id = sa.Column(sa.Integer, sa.ForeignKey(OneThing.id), primary_key=True)
...
one_thing = sa.orm.relationship(OneThing, backref=sa.orm.backref('other_thing', uselist=False))
在这种情况下,您可以按照下面的方式编写当前查询,而sqlalchemy
将找出联接条件:
query = session.query(OneThing).outerjoin(OtherThing)
最后,,让我们在结果中获得相关数据。有多个选项,但是从您拥有的内容开始,您可以查询OneThing
(仅),然后导航上面定义的关系:
query = session.query(OneThing).outerjoin(OtherThing)
for one in query:
print(one)
print(one.other_thing.other_deal)
另一个选择是同时查询这两个模型,但请注意,所得行包含(OneThing, OtherThing)
的元组:
query = session.query(OneThing, OtherThing).outerjoin(OtherThing)
for one, oth in query:
print(one)
print(oth)
另一个选项是仅查询列的子集:
query = session.query(OneThing.name, OtherThing.other_deal).outerjoin(OtherThing)
for row in query:
print(row)
# ('hi', 'and sticky')