Menu
Let's say I have a table 'shares' with the following columns:
company price quantity Microsoft 100 10 Google 99 5 Google 99 20 Google 101 15 I'd like to run the equivalent of a SQL statement like this:
select price, sum(quantity) as num from shares where company='Google' group by price; The closest I've come is:
result = (dbsession.query(Shares.price, func.sum(Shares.quantity)) .filter(Shares.company == 'Google') .group_by(Shares.price) .all()) I'm having trouble with setting up the 'sum(quantity) as num' in sqlalchemy. It appears I need to use alias() but I can't figure out how by looking at the documentation.
483
In SQLAlchemy, any Table, select() construct, or other selectable object can be turned into an alias using the From Clause. alias() method, which produces an Alias construct. The alias() function in sqlalchemy.
The select() method of table object enables us to construct SELECT expression. The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. Here, we have to note that select object can also be obtained by select() function in sqlalchemy.
all() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present.
You actually want the label method.
result = dbsession.query(Shares.price, \ func.sum(Shares.quantity).label("Total sold")) \ .filter(Shares.company== 'Google') \ .group_by(Shares.price).all() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
