Flask SQLalchemy simple queries

Posted on 2020-06-13T11:37:25Z by Kevin Foong

In this post we will explore some simple SQLalchemy statements and compare them to their SQL equivalents. This post is just on using Flask SQLalchemy to query your database. If you want to know how to model your database please see my other post here. Let's get started!

Let's say we have two tables UserDetails and Country in a one to many relationship towards Country. That is one UserDetails can only have one Country. But one Country can have many UserDetails. Our models look like this.

UserDetails

class UserDetails(db.Model):
    __tablename__ = 'user_details'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    gender_id = db.Column(db.Integer, db.ForeignKey('gender.id'), nullable=False)
    dob = db.Column(db.String(10), nullable=False)
    country_id = db.Column(db.Integer, db.ForeignKey('country.id'), nullable=False)
    state = db.Column(db.String(30), nullable=False)
    city = db.Column(db.String(30), nullable=False)

    def __repr__(self):
        return '<UserDetails {}>'.format(self.first_name)

Country

class Country(db.Model):
    __tablename__ = 'country'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False)
    update_date = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    user_details = db.relationship('UserDetails', backref='country', lazy='dynamic')

    def __repr__(self):
        return '<Country {}>'.format(self.name)

Note that UserDetails has a country_id field which is a foreign key to the Country table. Also note that Country has a relationship type which is not actually a field in the database, but provides a "back reference" to UserDetailswhich will allow us to easily traverse the two tables when we perform queries as we will see later.

Now let's use Flask shell to interact with our database. Flask shell allows us to interact with our Flask app directly and is a good way to test our syntax. On the command prompt make sure that you have started your virtual environment then enter flask shell

We need to import our flask-sqlalchemy db instance and our two models as we will be interacting with them. Amend the below according to how you have structured your own project.

>> from app import db
>> from app.models import UserDetails, Country

Say we want to select using an inner join between the two tables such as below.

SELECT *
FROM user_details a
INNER JOIN country b ON (b.id = a.country_id)
WHERE a.id=1;

The SQLalchemy equivalent would be:

>> r = db.session.query(UserDetails).join(Country).filter(UserDetails.id==1).first()

.join here is an inner join. If we wanted an outer join we would use .outerjoin. We also see that .first() returns the first UserDetails object. If we wanted to return all objects we would use .all() which would return a list of UserDetails objects.

r in our case is a UserDetails object which you can then use to access any field in UserDetails such as.

>> r.first_name
>> r.dob

Now to access fields in the Country table all you need to do is to reference the "backref" value defined in Country which is country. The below statement will return the country associated with the user.

>> r.country.name

Other queries

Here is a collection of queries showing other scenarios.

1. Query one table

To query just one table you can also use the object directly rather than db.session. Given below example SQL.

SELECT *
FROM user_details
WHERE id=1;

SELECT *
FROM country;

It will equate to this in SQLalchemy.

>> UserDetails.query.filter_by(id=1).first()
>> Country.query.all()

2. Query specific field

This query selects a specific field and has an inner join on 2 tables with a where clause.

SELECT a.filename
FROM upload_photos a
INNER JOIN user_details b ON (b.id = a.user_details_id)
INNER JOIN user c ON (c.id = b.user_id)
WHERE c.id = 7;

A good approach is to start from top to bottom to construct your SQLalchemy equivalent which will look like this. 

db.session.query(UploadPhotos.filename)
        .join(UserDetails).filter(UserDetails.id == UploadPhotos.user_details_id)
        .join(User).filter(User.id == UserDetails.user_id)
        .filter(User.id == 7).all()

Note that this will return a list of tuples. If you wanted to convert the list of tuples into a list you could use Python's list comprehension like this.

[item for f in filenames for item in f]

3. Many to many queries

When you have a many to many relationship between two tables you will need a third table to join the other two tables. In the below example I have created a task_stock table which acts as the joining table. In SQLalchemy you need to define a db.relationship on either the Stock or Task table. It doesn't matter which one. This particular line with lazy='dynamic' is saying that both backrefs are loaded dynamically. I normally use this setting because it allows us to further return the results using .first() or .all()

task_stock = db.Table('task_stock',
    db.Column('task_id', db.Integer, db.ForeignKey(
      'task.id'), primary_key=True),
    db.Column('stock_id', db.Integer, db.ForeignKey(
      'stock.id'), primary_key=True)
    )

class Stock(db.Model):
    __tablename__ = 'stock'
    id = db.Column(db.Integer, primary_key=True)
    ...
        
class Task(db.Model):
    __tablename__ = 'task'
    id = db.Column(db.Integer, primary_key=True)
    ...
    stocks = db.relationship('Stock',
                            secondary=task_stock,
                            lazy='dynamic',
                            backref=db.backref('tasks', lazy='dynamic'))

We can then proceed to perform queries starting from either table such as:

Stock.query.filter_by(id=1).tasks.all()
Task.query.filter_by(id=1).stocks.all()

4. Multiple conditions

If you have multiple conditions in your query you can just chain multiple filters together.

db.session.query(Stock).filter(Stock.id==1).filter(Stock.name=="bulb").all()

Or you can use SQLAlchemy's and_ or or_

from sqlalchemy import and_, or_

db.session.query(Stock).filter(and_(Stock.id==1, Stock.name=="bulb")).all()
db.session.query(Stock).filter(or_(Stock.name=="torch", Stock.name=="bulb")).all()

5. Using between and fuzzy matching

In SQL you can use the LIKE operator for some fuzzy matching. You can also use the BETWEEN operator with fuzzy matching. Here is an example SQL.

SELECT *
FROM USERDETAILS
WHERE email_matched_notification = 1
AND first_name BETWEEN 'a%' AND 'c%'
ORDER BY first_name ASC;

And the SQLAlchemy equivalent.

from sqlalchemy import and_, asc

db.session.query(UserDetails)
    .filter(and_(UserDetails.email_matched_notification == True, UserDetails.first_name.between("a%", "c%")))
    .order_by(asc(UserDetails.first_name))
    .all()

I will continue to add further queries for different scenarios to this post as I go.