[Flask] Database Queries

Create object

me = User(username='fgimian', name='Fotis')
me.save()

Selecting by Pk / ID

SELECT * FROM user where id = 5

translates to:

the_user = User.query.get(5)

To update a record for a model:

the_user = User.query.get(5)
the_user.update(username='newusername')

to delete a record for a model:

the_user = User.query.get(5)
the_user.delete()

Selecting all rows in a table

SELECT * FROM user

translates to:

users = User.query.all()

Basic filtering

SELECT * FROM user WHERE age = 18

translates to:

users = User.query.filter_by(age = 18).all()

You may also filter using other standard <, >, != operators:

SELECT * FROM user WHERE age < 18 AND age >= 5

translates to:

users = User.query.filter((User.age < 18) & (User.age >= 5)).all()

Obtaining a single record

SELECT * FROM user WHERE username = 'fgimian' LIMIT 1

translates to:

me = User.query.filter_by(username = 'fgimian').first()

The first() function will return None if no records were found.
In the case that multiple records are found matching your filter, only the first row will be returned.

To ensure that only a single result is present

SELECT *
FROM user
WHERE username = 'fgimian'

translates to:

me = User.query.filter_by(username = 'fgimian').one()

The one function will return a single row, however it will raise a sqlalchemy.orm.exc.NoResultFound exception if no records were found or a *sqlalchemy.orm.exc.MultipleResultsFound** exception if multiple records were found.

Filtering using an in statement:

SELECT *
FROM user
WHERE username IN ('fgimian', 'lonelycat')

translates to:

users = User.query.filter(User.username.in_(['fgimian', 'lonelycat'])).all()

Selecting particular columns only

SELECT username, email
FROM user

translates to:

users = User.query.with_entities(User.username, User.email).all()

Note
Using the with_entitities function returns a list of tuples instead of a list of objects as per the other queries.

Logical operators for use with filtering:

SELECT *
FROM user
WHERE (username = 'fgimian'
       OR username = 'lonelycat')
AND id < 3
users = User.query.filter(
    (User.username == 'fgimian') | (User.username == 'lonelycat') &
    (User.id < 3)
).all()

Applying functions to columns when selecting

SELECT upper(username)
FROM user
users = User.query.with_entities(db.func.upper(User.username)).all()

Ordering results

SELECT *
FROM user
ORDER BY age [DESC]

translates to:

users = User.query.order_by(User.age)
users_desc = User.query.order_by(User.age.desc())

Grouping by

SELECT age, count(*)
FROM user
GROUP BY age
HAVING count(*) > 5

translates to:

users = User.query.with_entities(
    User.age, db.func.count()
).group_by(User.age).having(db.func.count() > 5).all()

Aliasing columns

SELECT age, count(*) AS counter
FROM user
GROUP BY age
HAVING counter > 5
translates to:

users = User.query.with_entities(
    User.age, db.func.count().label('counter')
).group_by(User.age).having('counter > 5').all()

Sub-queries, ranking and extracting date components

SELECT name, used, date
FROM (SELECT name, value, date,
             rank() OVER (PARTITION BY name, EXTRACT(YEAR FROM date)
                          ORDER BY date DESC) AS ranking
      FROM bandwidth_usage) AS bwr
WHERE ranking = 1
translates to:

used_bw_subquery = BandwidthUsage.query.add_columns(
    db.func.rank().over(
        partition_by=[BandwidthUsage.name,
                      db.extract('year', BandwidthUsage.date)],
        order_by=BandwidthUsage.date.desc()
    ).label('ranking')
).subquery('bwr')

used_bw = BandwidthUsage.query.select_entity_from(used_bw_subquery).filter(
    bw_subquery.c.ranking == 1
)

https://flaskage.readthedocs.io/en/latest/database_queries.html