mysql query to sqlalchemy version

Question

Myself getting familiar with sqlalchemy.I have a mysql query as below:

SELECT COUNT( * ) AS total, SUM( IF(sub =  'N', 1, 0 ) ) AS NotSubscribed, SUM( IF( subscription =  'A', 1, 0 ) ) AS 12Month, SUM( IF( subscription =  'HY', 1, 0 ) ) AS 6Month, SUM( IF( subscription =  'M', 1, 0 ) ) AS 1Month
FROM accounts
WHERE userID IS NOT NULL

I am trying to write a sqlalchemy version of it.

  users = Table('users',metadata,autoload=True)
  userInfo = users.select([func.count(users.c.userID).label('Total'),
                              func.sum(users.c.sub == 'N').label('NotSubscribed'),
                              func.sum(users.c.sub == 'A').label('12Month'),
                              func.sum(users.c.sub =='HY').label('6Month'),
                              func.sum(users.c.sub == 'M').label('1Month')]).where(users.c.userID != None).execute()

I am ending up with following error:

sqlalchemy.exc.ArgumentError: SQL expression object or string expected

Can someone help me where I am going wrong. If not let me know if there are any good tutorials about sqlalchemy apart from the documentation.

Thanks

What is the SUM( IF(..., 1, 0)) supposed to do here? Total counts for the various subscription types? — Martijn Pieters
– Martijn Pieters, Commented Nov 28, 2013 at 10:30
hi, bascially i am trying to get the total users against the different subscribed users.Am i clear — troy
– troy, Commented Nov 28, 2013 at 10:32
Why not just use SELECT subscription, COUNT(userID) FROM accounts WHERE userID IS NOT NULL GROUPBY subscription, and make total the sum of the resulting rows? That query will be much lighter on the query engine too. — Martijn Pieters
– Martijn Pieters, Commented Nov 28, 2013 at 10:32
In any case, func.sum(users.c.subscription == 'N') would not translate to the IF() statement here. You'd have to add a func.if() too. — Martijn Pieters
– Martijn Pieters, Commented Nov 28, 2013 at 10:33
thanks for that. Just trying to get familiar with sqlalchemy, havent thought of optimising it.cheers — troy
– troy, Commented Nov 28, 2013 at 10:34

van · Accepted Answer · 2013-11-28 20:28:51Z

The sum is the problem in your code. To make it work, you will need to combine sum and case. Below shows 2 ways of using case (pick one you like better):

qry = select([
        func.count(users.c.userID).label("Total"),
        func.sum(case(value=users.c.sub, whens={'N': 1}, else_=0)).label("NotSubscribed"),
        func.sum(case(value=users.c.sub, whens={'A': 1}, else_=0)).label("12Month"),
        func.sum(case([(users.c.sub == 'HY', 1)], else_=0)).label("6Month"),
        func.sum(case([(users.c.sub == 'M', 1)], else_=0)).label("1Month"),
    ],
).where(users.c.userID != None)

res = engine.execute(qry)
print res.keys()     # column names
print res.fetchone() # values

However, I would indeed consider the recommendation of Martijn, the corresponding SA expression for which would be:

qry = select([users.c.sub, func.count(users.c.userID)]).where(users.c.userID != None).group_by(users.c.sub)

res = engine.execute(qry)
rows = res.fetchall()
rows.append(('Total', sum(_r[1] for _r in rows))) # add Total
for row in rows:
    print row

Collectives™ on Stack Overflow

mysql query to sqlalchemy version

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related