Menu
PostgreSQL have aggregate expressions, e.g. count(*) FILTER (WHERE state = 'success'). How can I generate such expressions using SQLAlchemy?
717
SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.
SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.
If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.
One of which is that Flask-SQLAlchemy has its own API. This adds complexity by having its different methods for ORM queries and models separate from the SQLAlchemy API. Another disadvantage is that Flask-SQLAlchemy makes using the database outside of a Flask context difficult.
Suppose I have a model Machine with a boolean field active, and would like to filter the count by active = true
func.count(...).filter(...)
from models import db, Machine
from sqlalchemy.sql import func
query = db.session.query(
func.count(Machine.id).filter(Machine.active == True)
.label('active_machines')
)
We can look at the generated SQL query:
>>> print(query)
SELECT count(machine.id) FILTER (WHERE machine.active = true) AS active_machines
FROM machine
This should work the same for the other aggregate functions like func.avg, func.sum, etc
funcfilter(count(...), filter)
func.count(Machine.id).filter(Machine.active == True) is short hand for:
from sqlalchemy import funcfilter
funcfilter(func.count(Machine.id), Machine.active == True)
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
