SQL to SQLAlchemy conversions

05 May 2016

Some examples on how to convert raw SQL to SQLAlchemy query:

SELECT

SELECT COUNT(*)

> from sqlalchemy import text, func
> db.session.query(func.count()).all()

Add labels

SELECT COUNT(*) as requiests

> rows = db.session.query(func.count().label('requests')).all()
> row = rows[0]
> row.requests
12

SUM function

SELECT SUM(status) as rides

> rows = db.session.query(func.sum(Book.status).label('books')).all()
> row = rows[0]
> row.books
12

COUNT and DISTINCT

SELECT COUNT(DISTINCT(author_id)) as authors

from sqlalchemy import distinct
> rows = db.session.query(
    func.count(distinct(Book.author_id)).label('authors')
).all()

Query a date range (BETWEEN)

> now = datetime.datetime.utcnow()
> db.session.query(Book).filter(
    Book.created_at.between(now - datetime.timedelta(hours=5), now)
).all()

Conditional SUM

SELECT SUM(((status IN (4, 7))::int)) as books ...

> from sqlalchemy.sql.expression import case
> db.session.query(
    func.sum(case([(Book.status.in_((4, 7)), 1)], else_=0)).label('books'),
).filter(
    # ...
).all()

IN clause

> Book.query.filter(
    Book.status.in_((BOOK_CONFIRMED, BOOK_FINISHED)),
).count()

For NOT IN just add ~ symbol:

> Book.query.filter(
    ~Book.status.in_((BOOK_CONFIRMED, BOOK_FINISHED)),
).count()

Labels and AS clause

In this example we’ll make a fingle table out of books and authors tables, labeling books.title and authors.last_name columns as name:

SELECT books.title AS name 
FROM books UNION ALL SELECT authors.last_name AS name 
FROM authors

And that’s how out SQL converts to SQLAlchemy:

db.session.query(
    label('name', Book.title)
).union_all(db.session.query(
    label('name', Author.last_name)
)).all()

Now we can also filter our result table by ǹame column:

db.session.query(
    label('name', Book.title)
).union_all(db.session.query(
    label('name', Author.last_name)
)).filter_by(name='some string')

LIKE and ILIKE

Book.query.filter(Book.title.like('%tale%')).all()
Book.query.filter(Book.title.ilike('%tale%')).all()

GROUP BY

Group books my month name:

SELECT to_char(created_at, 'Mon'), count(created_at)
FROM books 
GROUP BY to_char(created_at, 'Mon') 
ORDER BY to_char(created_at, 'Mon') 

| Month    | Count |
|----------|-------|
| January  | 12    |
| March    | 29    |
| November | 8     |

books = db.session.query(
    func.to_char(Book.created_at, 'MM'),
    func.count(Book.created_at)
).group_by(
    func.to_char(Book.created_at, 'MM')
).order_by(func.to_char(Book.created_at, 'MM')).all()