I'm still confused as to how SQLAlchemy works. As of now I have a query which looked like this
SELECT cast(a.product_id as bigint) id, cast(count(a.product_id) as bigint) itemsSold, cast(b.product_name as character varying)
from transaction_details a
left join product b
on a.product_id = b.product_id
group by a.product_id, b.product_name
order by itemsSold desc;
I'm not so sure how this is converted in Flask tho.
If you're new to SQLAlchemy, read both Object Relational Tutorial and SQL Expression Language Tutorial to get familiar with the way it works. Since you are using Flask-SQLAlchemy extension, keep in mind that a lot of names, which are imported in the examples of aforementioned tutorials, can be accessed via the instance of SQLAlchemy class (usually named db in Flask-SQLAlchemy examples). Your SQL query, when converted to SA, would look something like the following (untested):
# Assuming that A and B are mapped objects that point to tables a and b from
# your example.
q = db.session.query(
db.cast(A.product_id, db.BigInteger),
db.cast(db.count(A.product_id), db.BigInteger).label('itemsSold'),
db.cast(B.product_name, db.String)
# If relationship between A and B is configured properly, explicit join
# condition usually is not needed.
).outerjoin(B, A.product_id == B.product_id).\
group_by(A.product_id, B.product_name).\
order_by(db.desc('itemsSold'))
