Create SQL query using SqlAlchemy select and join functions

Question

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

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

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

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

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

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

EPiddy · Accepted Answer · 2009-06-04 16:44:15Z

2

Give this a try...

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

edited Jun 4, 2009 at 16:44

answered Jun 4, 2009 at 15:01

EPiddy

4813 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ants Aasma Over a year ago

You forgot the .group_by(deal_tag.c.tagid). And this will not work on DBs that respect the SQL standard, you need to group by tags.c.Name and tags.c.id, because they are selected.

Nazmul Hasan · Accepted Answer · 2009-06-27 10:19:07Z

0

you can join table in the time of mapping table

in the orm.mapper()

for more information you can go thru the link

www.sqlalchemy.org/docs/

answered Jun 27, 2009 at 10:19

Nazmul Hasan

7,07013 gold badges38 silver badges37 bronze badges

Collectives™ on Stack Overflow

Create SQL query using SqlAlchemy select and join functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related