Generate sql with subquery as a column in select statement using SQLAlchemy

by

If you need this often, and/or the count is an integral part of your Tab1 model, you should use a hybrid property such as described in the other answer. If on the other hand you need this just for a single query, then you could just create the scalar subquery using Query.label(), or Query.as_scalar():

count_stmt = session.query(func.count(1)).\
    filter(Tab2.tab1_id == Tab1.id).\
    group_by(Tab2.col1).\
    label('cnt')

session.query(Tab1, count_stmt).filter(...).limit(100)

The subquery will automatically correlate what it can from the enclosing query.

More Related Contents:

  1. How to write DataFrame to postgres table
  2. CS50: LIKE operator, variable substitution with % expansion
  3. SQLAlchemy create_all() does not create tables
  4. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  5. How to create an SQL View with SQLAlchemy?
  6. Updates to JSON field don’t persist to DB
  7. Complex foreign key constraint in SQLAlchemy
  8. Connection problems with SQLAlchemy and multiple processes
  9. How to upsert pandas DataFrame to PostgreSQL table?
  10. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  11. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  12. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  13. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  14. Creating partial unique index with sqlalchemy on Postgres
  15. SQLAlchemy func.count on boolean column
  16. How to set connection timeout in SQLAlchemy
  17. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  18. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  19. jsonify a SQLAlchemy result set in Flask [duplicate]
  20. Using OR in SQLAlchemy
  21. Python Postgres psycopg2 ThreadedConnectionPool exhausted
  22. When do I need to use sqlalchemy back_populates?
  23. Psycopg2 image not found
  24. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  25. pandas to_sql all columns as nvarchar
  26. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  27. Deploying Django to Heroku (Psycopg2 Error)
  28. add column to SQLAlchemy Table
  29. Psycopg2, Postgresql, Python: Fastest way to bulk-insert
  30. How Postgresql COPY TO STDIN With CSV do on conflic do update?

Leave a Comment