How to execute “left outer join” in SqlAlchemy

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

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. SQLAlchemy: how to filter date field?
  6. How to use variables in SQL statement in Python?
  7. Writing a connection string when password contains special characters
  8. Pandas read_sql with parameters
  9. Difference between filter and filter_by in SQLAlchemy
  10. Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC
  11. Using OR in SQLAlchemy
  12. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  13. Pandas DENSE RANK
  14. How to create a large pandas dataframe from an sql query without running out of memory?
  15. How to put parameterized sql query into variable and then execute in Python?
  16. Passing table name as a parameter in psycopg2
  17. Flask SQLAlchemy query, specify column names
  18. Query for list of attribute instead of tuples in SQLAlchemy
  19. SQLAlchemy: What’s the difference between flush() and commit()?
  20. How to give column name dynamically from string variable in sql alchemy filter?
  21. Debugging (displaying) SQL command sent to the db by SQLAlchemy
  22. How to disable SQLAlchemy caching?
  23. Case Insensitive Flask-SQLAlchemy Query
  24. How to upsert pandas DataFrame to Microsoft SQL Server table?
  25. SQLAlchemy ORDER BY DESCENDING?
  26. SQL Alchemy ORM returning a single column, how to avoid common post processing
  27. How to log all sql queries in Django?
  28. Flask-SQLalchemy update a row’s information
  29. pyodbc – How to perform a select statement using a variable for a parameter
  30. Get inserted key before commit session
  31. Serve image stored in SQLAlchemy LargeBinary column
  32. How do I execute inserts and updates in an Alembic upgrade script?
  33. How to create a new database using SQLAlchemy?
  34. Generate sql with subquery as a column in select statement using SQLAlchemy
  35. How to do an upsert with SqlAlchemy?
  36. SQLAlchemy ManyToMany secondary table with additional fields
  37. LEFT JOIN Django ORM
  38. How do I select literal values in an sqlalchemy query?
  39. Python Pandas write to sql with NaN values
  40. Python SQL query string formatting
  41. How can I profile a SQLAlchemy powered application?
  42. Python call sql-server stored procedure with table valued parameter
  43. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  44. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  45. SqlAlchemy – Filtering by Relationship Attribute
  46. group by year, month, day in a sqlalchemy
  47. How to perform OR condition in django queryset?
  48. Getting first row from sqlalchemy
  49. How to build many-to-many relations using SQLAlchemy: a good example
  50. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?

Leave a Comment