In SQLAlchemy, it may like this: SELECT users.name, (SELECT count(addresses.id) AS count_1 FROM addresses WHERE users.id = addresses.user_id) AS anon_1 FROM users stmt = select([func.count(addresses.c.id)]).where(users.c.id == addresses.c.user_id).as_scalar() conn.execute(...