Survival Guide for Flask-SQLAlchemy Queries
With vanilla SQL, SQLAlchemy, Flask-SQLAlchemy, and many more tools at your disposal, it’s easy to lose track of what syntax you need when and where you need it. Together we’ll look at Flask-SQLAlchemy specifically and how to write queries for a many-to-many relationship. Let’s start by doing a quick inventory of the building blocks available to us.
What we’re working with
These are options for asking a more specific question of your data.
- join( ) : Connect two tables via a foreign key. For Flask SQL Alchemy, you’ll likely reference the relationship field on your chosen model.
- group_by( ) : Arrange the data into buckets based on a shared column value. You’ll typically use this with an aggregate function.
- order_by( ) : Return the data in order of a particular column value. Specify .desc( ) for descending order.
- with_entities ( ) : Return additional information with your query other than what’s provided in the original table.
- filter( ) : Only return data that matches a condition of your choice.
What we get back
These are options to select which rows you want back from the database, or what happens if they’re not found.
- first( ) : Returns the first result that matches the query.
- first_or_404( ) : Returns a 404 error if there are no results. You won’t have a lot of control over the error itself, but this is handy for when you’re building views for an API.
- all( ) : Returns every result that matches the query.
These are some cursory definitions to get us started. For more information, check out the SQLAlchemy and Flask-SQLAlchemy docs.
Many-to-many tables
We’ll walk through some examples using a many-to-many relationship between posts and users. They are connected through post_likes — a user can have liked many posts, and a post can have been liked by many users.
Presume we also have models built in Python for User , Post , and PostLike.
Sort posts in descending order
To start our Flask SQL alchemy queries, we’ll refer to the name of the model, followed by .query , then any of the options to narrow our search mentioned above.
Post.query.order_by(Post.created_at.desc()).all()Here we pass Post.created_at to order_by so that our posts will be returned in order of their created date, which directly corresponds to the created_at column in the posts table and the matching field on the model. Adding .desc() will sort the posts so our most recent is our first result.
This is a good start for when we are building a GET request and want to get back all of our rows for a particular table. When we have a stronger opinion on how we would like to user our data, we can get more specific.
Sort posts in descending order by number of likes
If we want to order our posts by number of likes, which we are not storing already in our table, we’ll need to combine a join and a group_by .
A join with our posts and post_likes tables will bridge the gap so that we can calculate the total number of likes per post.
def sort_posts_by_likes():
query = Post.query \
# post_likes is a relationship field on the Post model
# this joins the posts and post_likes tables
.join(Post.post_likes) \
# section the data by common post ids
.group_by(Post.id) \
# count the number of post_likes for that section
# return in descending order by number of likes
.order_by(db.func.count(Post.post_likes).desc()) \
# return information about the post from the table,
# plus the like_count labeled as such
.with_entities(Post.id, Post.content, Post.user_id, \
db.func.count(Post.post_likes).label("like_count")) \
.all()
# specifies how to format each row returned from our query
posts_by_like = [{
"id": post.id,
"content": post.content,
"user_id": post.user_id,
"like_count": post.like_count
} for post in query]
return posts_by_like
## example return with the "like_count" additional entity
[
{
"content": "excellent post",
"id": 91,
"like_count": 500,
"user_id": 2
},
{
"content": "good post",
"id": 8,
"like_count": 150,
"user_id": 3
},
{
"content": "ok post",
"id": 37,
"like_count": 25,
"user_id": 1
}
]Once we join our tables, we can group them in order to perform a calculation on each group. Since we group our posts on their unique id, we can then use db.func.count to tally the total number of posts per group, or per each individual post. We then arrange the posts in descending order by this like count, so the most popular posts are returned first.
The with_entities() option allows us to list exactly what about each row we would like to return. We can pull directly from columns like we did with id, content , and user_id , but the real utility comes from defining our own “column”. If we specify our db.func.count calculation as another value, we can label it like_count and then use it much more accessibly in other areas of our application.
Return users who liked a certain person’s posts
Our prior examples have each returned posts, but what if we want to return information about another table? In this example, we’ll look at returning a specific person’s “fan club,” or the usernames for anyone that has liked their posts. We’ll need to join multiple tables in order to accomplish this.
def get_likers_for_posts_by(userid):
query = User.query \
# post_likes is a relationship field on the User model
# this joins the users and post_likes tables
.join(User.post_likes) \
# post is a relationship field on the PostLike model
# this joins the post_likes and posts tables
.join(PostLike.post) \
# only include posts written by the relevant user
.filter(Post.user_id == userid) \
.all()
# only return the username for each result
# set comprehension in case users have liked multiple posts by this person
unique_users = { user["username"] for user in users_schema.dump(query) }
return list(unique_users)
## [ "username1", "username2", "username3" ]Since we want to return information about users , we start our query on the User model. We join post_likes and posts so we can get to the column on which we want to filter — user_id , or the author of the post. If that’s the id we received when we invoked, then we return the username for this row in users.
Note: Don’t worry too much about the comprehension for unique users. You’ll need to de-serialize what you retrieve via query but you may do this in a variety of ways. The takeaway is that you can iterate through your results to specify exactly what you get back in the event that you don’t want the typical amount of user information.
As a reference, this is what the same query would look like in vanilla SQL.
SELECT DISTINCT users.username FROM users
JOIN post_likes ON users.id = post_likes.user_id
JOIN posts ON post_likes.post_id = posts.id
GROUP BY posts.id
HAVING posts.user_id = ?;I hope this helps to get you started crafting your own queries — see you later for the sequel?
