Cansada de ser feliz

Bienvenidos a mi flujo de conciencia

SQL to SQLAlchemy conversions

| Comments

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

SELECT

1
SELECT COUNT(*)
1
2
> from sqlalchemy import text, func
> db.session.query(func.count()).all()

Add labels

1
SELECT COUNT(*) as requiests
1
2
3
4
> rows = db.session.query(func.count().label('requests')).all()
> row = rows[0]
> row.requests
12

SUM function

1
SELECT SUM(status) as rides
1
2
3
4
> rows = db.session.query(func.sum(Book.status).label('books')).all()
> row = rows[0]
> row.books
12

COUNT and DISTINCT

1
SELECT COUNT(DISTINCT(author_id)) as authors
1
2
3
4
from sqlalchemy import distinct
> rows = db.session.query(
    func.count(distinct(Book.author_id)).label('authors')
).all()

Query a date range (BETWEEN)

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

Conditional SUM

1
SELECT SUM(((status IN (4, 7))::int)) as books ...
1
2
3
4
5
6
> 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

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

For NOT IN just add ~ symbol:

1
2
3
> 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:

1
2
3
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:

1
2
3
4
5
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:

1
2
3
4
5
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

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

GROUP BY

Group books my month name:

1
2
3
4
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')
1
2
3
4
5
| Month    | Count |
|----------|-------|
| January  | 12    |
| March    | 29    |
| November | 8     |
1
2
3
4
5
6
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()

Comments