Python – Create SQL query using SqlAlchethe select and join functions


I have two tables "tags" and "deal_tag", and table definition follows,

Table('tags', metadata,
          Column('id', types.Integer(), Sequence('tag_uid_seq'),
          Column('name', types.String()),

Table('deal_tag', metadata,
       Column('dealid', types.Integer(), ForeignKey('')),
       Column('tagid', types.Integer(), ForeignKey

I want to select tag id, tag name and deal count (number of deals per
tag). Sample query is

SELECT tags.Name,,COUNT(deal_tag.dealid) FROM tags INNER JOIN
deal_tag ON = deal_tag.tagid GROUP BY deal_tag.tagid;

How do I create the above query using SqlAlchemy select & join functions?

Best Solution

Give this a try...

s = select([tags.c.Name,, func.count(deal_tag.dealid)], 
  == deal_tag.c.tagid).group_by(tags.c.Name,
Related Question