I’m having some trouble translating a subquery into sqlalchemy. I have two tables that both have a store_id column that is a foreign key (but it isn’t a direct many-to-many relationship) and I need to return the id, store_id and name from table 1 along with the number of records from table 2 that also have the same store_id. I know the SQL that I would use to return those records I’m just now sure how to do it using sqlalchemy.

SELECT
table_1.id
table_1.store_id,
table_1.name,
(
    SELECT
        count(table_2.id)
    FROM
        table_2 
    WHERE
        table_1.store_id = table_2.store_id
) AS store_count FROM table_1;