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
The problem with this is that it prints everything twice. I tried using .join() and also prints the results twice.
What I want to achieve is like
print Employee.name print Employee.details.emailAnswer1:
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)
As to your
duplicate result problem, I believe you have some "extra" in your real code which produces this error...