查询SQLAlchemy中的相关表 [英] Querying related tables in sqlalchemy
问题描述
所以我有两个这样的表Employee和Details.
So I have two tables Employee and Details like this.
class Employee(Base):
__tablename__ = 'employees'
id = Column(Integer, Sequence('employee_id_seq'), primary_key=True)
name = Column(String(50), nullable=False)
............
class Detail(Base):
__tablename__ = 'details'
id = Column(Integer, Sequence('detail_id_seq'), primary_key=True)
start_date = Column(String(50), nullable=False)
email = Column(String(50))
employee_id = Column(Integer, ForeignKey('employee.id'))
employee = relationship("Employee", backref=backref('details', order_by=id))
............
现在我要做的是获取所有员工及其相应的详细信息,这是我尝试过的.
Now what I want to do is get all the employees and their corresponding details, here is what I tried.
for e, d in session.query(Employee, Detail).filter(Employee.id = Detail.employee_id).all():
print e.name, d.email
此问题是它将所有内容打印两次.我尝试使用.join(),并且还将结果打印两次.
The problem with this is that it prints everything twice. I tried using .join() and also prints the results twice.
我想实现的目标是
print Employee.name
print Employee.details.email
推荐答案
如果您只关心很少的列,则可以在查询中直接指定它们:
If you really care only about few columns, you can specify them in the query directly:
q = session.query(Employee.name, Detail.email).filter(Employee.id == Detail.employee_id).all()
for e, d in q:
print e, d
如果您确实要加载对象实例,那么我会做不同的事情:
If you do really want to load object instances, then I would do it differently:
# query all employees
q = (session.query(Employee)
# load Details in the same query
.outerjoin(Employee.details)
# let SA know that the relationship "Employee.details" is already loaded in this query so that when we access it, SA will not do another query in the database
.options(contains_eager(Employee.details))
).all()
# navigate the results simply as defined in the relationship configuration
for e in q:
print(e)
for d in e.details:
print(" ->", d)
关于您的duplicate
结果问题,我相信您的真实代码中会产生一些额外"错误……
As to your duplicate
result problem, I believe you have some "extra" in your real code which produces this error...
这篇关于查询SQLAlchemy中的相关表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!