Flask SQLAlchemy simple queries
Posted on
by Kevin FoongIn 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 UserDetails
which 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, UserDetails.id==UploadPhotos.user_details_id)
.join(User, 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.
Tags: flask flask-sqlalchemy database