How to execute “left outer join” in SqlAlchemy

by 
q = session.query(Table1.field1, Table1.field2)\
    .outerjoin(Table2)\ # use in case you have relationship defined
    # .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined
    .filter(Table2.tbl2_id == None)

should do it, assuming that field1 and field2 are from Table1, and that you define a relationship:

class Table2(Base):
    # ...
    table1 = relationship(Table1, backref="table2s")

More Related Contents:

  1. How to execute raw SQL in Flask-SQLAlchemy app
  2. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  3. sqlalchemy: how to join several tables by one query?
  4. Python, SQLAlchemy pass parameters in connection.execute
  5. Complex foreign key constraint in SQLAlchemy
  6. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  7. SQLAlchemy: how to filter date field?
  8. Getting random row through SQLAlchemy
  9. Select NULL Values in SQLAlchemy
  10. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  11. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  12. VALUES clause in SQLAlchemy
  13. flask sqlalchemy query with keyword as variable
  14. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  15. Pandas DENSE RANK
  16. Flask-SQLalchemy update a row’s information
  17. Efficiently updating database using SQLAlchemy ORM
  18. pyodbc – How to perform a select statement using a variable for a parameter
  19. SQLAlchemy ORDER BY DESCENDING?
  20. SQLAlchemy ON DUPLICATE KEY UPDATE
  21. stored procedures with sqlAlchemy
  22. SqlAlchemy – Filtering by Relationship Attribute
  23. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  24. Dynamic SQL Queries with Python and mySQL
  25. Getting first row from sqlalchemy
  26. How to get number of affected rows in sqlalchemy?
  27. convert selected datetime to date in sqlalchemy
  28. Is possible to mapping view with class using mapper in SqlAlchemy?
  29. How to delete a record in Django models?
  30. SQLAlchemy ordering by count on a many to many relationship

Leave a Comment