在sqlalchemy中查询相关表

Question

我有两个表，一个是员工表（Employee），另一个是详细信息表（Details），它们的结构如下。

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

现在我想做的是获取所有员工及其对应的详细信息，这是我尝试过的。

for e, d in session.query(Employee, Detail).filter(Employee.id = Detail.employee_id).all():
    print e.name, d.email

问题是，这样做会把所有内容打印两遍。我尝试使用 .join()，结果也是打印了两遍。

我想要的结果是这样的：

print Employee.name
print Employee.details.email

Answer 1

如果你只关心几个特定的列，可以直接在查询中指定这些列：

q = session.query(Employee.name, Detail.email).filter(Employee.id == Detail.employee_id).all()
for e, d in q:
    print e, d

如果你确实想加载对象实例，那我会用不同的方法：

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

关于你提到的重复结果问题，我觉得你的实际代码中可能有一些“多余”的部分导致了这个错误……