18560

Querying related tables in sqlalchemy

Question:

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

Answer1:

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

Recommend

  • Can there be two relationships between two tables?
  • Importing images in SQL Server 2005
  • How can i delete a row on fetch by onclick in reactjs
  • Assigning function to value attribute in details view yii2 [duplicate]
  • Enforce constraints between tables
  • Changing DateField to DateTimeField in Django
  • Group rows by 7 days interval starting from a certain date
  • Git cherry pick those commits that contain a keyword (tracking id)
  • Second Last records
  • Woocommerce Subscriptions - Get Start date
  • Cannot add Foreign Key on tables in DashDB / DB2 on Bluemix
  • Symfony2 Form is always empty after submitting
  • Take data from different tables and display it in View Index in mvc4
  • Single Table Inheritance in Rails 4
  • Delete multiple lines - from “patternA” match, through second occurrence of “patternB”
  • Implement a writable serializer for multilevel nested relationships in django rest framework
  • Apple Reachability Notifications for Network OR Wi-Fi
  • Entity framework - Foreign key - data annotation
  • Django ORM Left Join With GROUP BY and SUM
  • Could not find the following Boost libraries: boost_asio
  • Connect to OLEDB data source from Java?
  • C#, “Object reference not set to an instance of an object.” error
  • What is wrong in my MVC implementation?
  • Can you render a page with multiple forms instead of one form for a jQuery, Rails 3 update?
  • SSRS 2008 - Sorting within a group
  • Mysql EAV match row as field or entity's property value
  • Django: ORDER BY DESC on FloatField puts null values top?
  • Is it mandatory to have a doGet or doPost method?
  • Express displaying mongodb documents in Jade
  • Where these are stored?
  • Application level floating views with navigation in Android
  • C# fibonacci function returning errors
  • Login not working in Firefox in Meteor
  • How do I access an unhandled exception in an MVC Error view?
  • Email verification using google app script and google forms
  • Email format validation in mvc3 view
  • Master page gives error
  • sending/ receiving email in Java
  • Matrix multiplication with MKL
  • AngularJs get employee from factory