Flask and SQLAlchemy
Last updated
Last updated
After this lesson, you will be able to:
Create Models and migrate them to a Postgres database
Perform CRUD Operations with SQLAlchemy
Query it all via an API
Let's set up our app! We are going to need to
Set up a virtual environment
add our .gitignore (curious what to put in it? Checkout GitHub's suggested Python gitignore)
initialize and empty git repository
Activate the virtual environment
Upgrade our Pip in the venv
Install Flask, Flask-Sqlalchemy, and Psycopg2
Copy our installs into a requirements.txt file.
Create a models.py file for our models and an api.py for our flask server.
Not sure of the commands? Follow these:
When using SQLAlchemy in a Flask server, they need to be able to communicate with each other. This setup is going to happen in our models.py file. We are doing it in this file because models are often set up initially and then left alone, so the Flask app setup makes sense to be here.
If you have app setup that's more sizable, you can put it in a separate file and import what you need from it.
After importing Flask from flask and SQLAlchemy from flask_sqlalchemy, the flask app needs to be created, the database URI set to it, and SQLAlchemy given the newly created and configured app.
We have three config settings in here. 1. SQLALCHEMY_TRACK_MODIFICATIONS is a feature that is going to be removed on the next major release. This line isn't necessary, but if we don't have it, it'll throw us a warning which is annoying. Also, the python community is big on being explicit which means delaring this to be false even though is defaulted to that behavior is a shibboleth you can use to show how cool you are with the python community. 2. SQLALCHEMY_ECHO will print out all the SQL queries it is issuing. This defaults to False, but since I'm a nosey dev, I want to see everything my middleware does, so I set this to true. If you want to keep your console clean and your debugging harder, you can take that line out or embody the python lifestyle and explicity set it to False. 3. SQLALCHEMY_DATABASE_URI is pretty self explanitory. This URI tells our program everything it needs to know about our databse. This database URI will change when going into production, but for now, localhost is where our postgres database lives.
A Database URI is crafted like this:
[DB_TYPE]+[DB_CONNECTOR]://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DB_NAME]For macs, the database connector, username, password, and port are all assumed. Note that not all operating systems function that way.
This part looks and functions exactly like plain SQLAlchemy, the only difference is that, instead of importing all the Classes we need, they're all just called from the db variable we created.
This syntax should be pretty familiar!
Try it yourself!
We're going to have three models: 1. Users (already defined) 2. Posts
Header: String(150), unique, not null
author_id: Foreign Key (references user), on delete set null
body: String, not null
Tags
tag: String(50), unique, not null
Users has a 1:M relationship with Posts (One user has many posts, one post has one author) Posts has a N:M relationship with Tags (many posts have many tags, many tags are applied to many posts)
Use the SQLAlchemy you've already learned along with the User model we defined above to try to create those relations!
The Models—Final Version
The code below will look different from yours. How is it different? Why do you think those changes were applied?
Some things to note in these model definitions:
backref vs back_populates: When using backref, you only have to put the relationship on ONE class. When using back_populates, the relationship needs to be defined on BOTH class models. There is very little difference, but some prefer back_populates because it's more explicit and offers some IDE perks
lazy='subquery' in Post: When defining the relationship, we set lazy=subquery. This functionality is similar to lazy=joined/lazy=False in that it will load the relationship in the same query, but while joined/False loads it with a join statement, subquery will perform a second query to populate that field. The pro of subquery is that when whenever we ask for a Post, two queries will always be made, but when querying for multiple posts we will not get additional queries.
backref=backref('posts', lazy=True): If called backref='posts', the relationship on tags will be the same as on pages, which means that the relationship will default to lazy=subquery. However, we don't need ALL the posts by default when querying a tag, so we call the backref function with the name and any other relationship parameters we want to establish
First, we'll want to make sure that we have the database for it to connect to. You can do this either from the command line using createdb flasql or going into your psql shell and issuing the SQL command CREATE DATABASE flasql.
To create the tables, we are going to open an interactive python shell in our virtual environment. The first thing we'll need to do is import our db object from models.py, then we can run SQLAlchemy.create_all() method to create the tables:
Now if you check your database, you'll see FOUR tables! In this shell, we can seed our database with some folks and posts and tags. First, we'll add three users:
None of this will stick in the database until we commit the session. To do that, just type db.session.commit() into your python shell.
To make sure our Database works as we want, let's add two posts, one about cats, one about computers. Let's have Brandi write both. You can get creative or copy the ones I wrote below.
Lastly, we're going to apply the tags 'cats' and 'wisdom' to the first post, and 'computers' and 'wisdom' to the second one. We accompish this by saving all our posts into a list, then appending the tags to each item.
We can also add a post by the tag!
And, of course, we commit the session to save it all.
Once you've commited your session, check your database to make sure that the changes were applied. Once that's confirmed, exit the python shell by typing quit().
In your api.py, we're going to import our flask app from models at the top of the script. Following that, we'll import jsonify from flask.
Write a test route to the root route that returns some sort of message.
Before we can start our flask server, we need to export our environment and our app. Once we've done that, we can run flask run
Once you've confirmed that your flask server is running all okay, it's time to test the SQLAlchemy connection. When we hit the home route, instead of sending a message, let's send back the first user we find.
Check out that SWEET error!
Whaaaa?! When we printed it, it looks like an object, I access the keys like an object! What's the DEAL!? The reason error occurs is because a SQLAlchemy instance is more than just a dictionary, more than just the information from the database; it's chock full of methods and functionality that SQLAlchemy brings with its Base declarative. So how do we send the json back to the front-end?
We're going to add a method to our model classes! This will allow us to call .as_dict() on our SQLAlchemy objects and return dictionary version that CAN be serialized.
Back in models.py at the bottom of our User model, define a method called as_dict() that will take one parameter, self. There are a few ways to do this, the first is the brute force/hand-crafted way.
This is a totally reasonable and highly functional way of defining this. ESPECIALLY if you want to modify how the front end receives this (think back to taking out the password in auth when returning an object). We'll do that when we get into Post.
♟Bonus funky Iteration
There is a way to iterate over all the columns in the table, and create a dictionary from that. It's inline and pretty slick.
Let's break this down to see what it's doing.
The Inline loop—This is an instance of a loop essentially returning a value inside an object, creating a new key-value pair for each iteration. The structure is as such: [some code declaration] for [variable] in [list].
What is c?—So self.__table__.columns is clearly a list, because we're iterating through it, but what are each of the items? When calling type() on c tells us that c is an instance of a sqlalchemy.sql.schema.Column. It simply prints, for example, users.id, but there's more than just that string (think about our __repr__ function. Same principle). This sqlalchemy.sql.schema.Column has a name attribute which is a string. We can use this to create the key in the dictionary representation of User.
getattr(self, c.name)—This functions the same as self.id, but is particularly useful when your key is programatically generated and your object is not subscriptable (like our User object). This function takes two params, the first is the object to query, the second is the name of the key.
There it is! We can use this single line to create a dictionary representation of our User object, which can then be serialized for JSON useage!
Back in our api.py, instead of passing first_user, we'll pass first_user.as_dict().
If we put all CRUD logic for even one model in our api.py file, it's going to start to get unreadable. So we are going to make a series of scripts with CRUD and model specific functions to import into our main server.
In terminal, create a folder called crud. In that folder, create three files: user_crud.py, post_crud.py, and tag_crud.py. Nice and self explanitory!
Let's start with full CRUD for our user first. Open crud/user_crud.py and import two things at the top of the script: 1. from flask import jsonify, redirect 2. from models import db, User
Next, we define our first function! We will be writing 5 functions that correspond to our RESTful Routing chart:
Action
Method
Function Name
Path
index
GET
get_all_users()
/users
create*
POST
create_user()
/users
detail/show
GET
get_user(id)
/users/:id
update*
PUT
update_user(id, **kwargs)
/users/:id
delete*
DELETE
delete_user(id)
/users/:id
The three with asteriks will return a redirect
We will define our GET routes first.
🥇If At first you don't succeed, try except again
First function! We are going to be indexing all the users, so our function will be called get_all_users(). In Python, there is built in functionality that—in essence—says, "Run this block of code, and if there are any problems, throw an exception error". It structurally looks like an if...else... block, but the keywords are try and exception.
This block of code will try to run some functions with our imported gabe instance, but if anything goes wrong, it'll throw an exception which will be stored as the variable error, which we can then print to our console and debug.
More on try...except... from the Python docs.
This is good to know, but Flask has a very particular way that it deals with errors.
📇 The GET routes -
Index Users
Let's get all users!
Show User
Our get_user function needs an id passed to it, but once we have that, it's as simple as calling User.query.get(id).
This block of code queries the user by id, then runs a conditional to see if user is a truthy value. If it is, it returns the jsonified version of the dictionary representation of our user object (returned by our as_dict() method), else it raises an exeption with an error message.
To use these functions in our flask server, we need to import them.
Now we can return the results of these functions when /users and /users/:id respectively are hit.
Before looking at the code below, try to write these routes out yourself.
How do you specify query parameters?
How would you pass that parameter into a function?
Check your api.py below
So what happens when this doesn't work?
🚒 Exception Alert
If there is a problem with this, it will throw what's called an Exception. This normally looks like a styled page with flask because it assumes that you'll be doing all this via the browser. This is not super useful for us, as we're writing this
In our api.py, we're going to write a function that will run any time an exception is raised.
📝The POST Route -
When we write our functions, we have control over how the information gets passed into it. We can decide to take the entire request form/json and pass it into our create_user() function, then parse the data in our function, or we can parse the data outside of it and pass in what we want to the function. This tutorial is written as the following option.
Create
First up, is our create_user function. In order to create a user, we need three strings: name, email, bio.
Looks pretty similar to what we did in the python shell! The only difference is bio=bio or None. This is one way to account for optional fields. In this instance, we are expecting to recieve three parameters, even if one is just an empty string or none.
Often times when looking at python code, you'll run into parameters *args or **kwargs. These values are used to more easily handle a variable number of arguments that are passed into the function!
*args
Say you have a function that greets everyone in a classroom. You want to pass it multiple arguments, but you don't know how many arguments it will take, each class is different! Using *variable_name will make the function receive a tuple of arguments that can be accessed accordingly.
You can also use the * to pass in the parameters! This is especially useful if you are programmatically receiving a list.
**kwargs
Where *args gives you a tuple, **kwargs gives you a dictionary, arguments with keywords...keyword arguments...keyword args...keywargs...kwargs.
This is a particularly useful feature if the key is important as well! The best example of this is our database! Right now, our tables don't have a lot of columns, but let's look at a schema that is a little more involved:
spells
name: String(NOT NULL)
desc: String
higher_level: String
page: String
range: String(NOT NULL)
components: String(NOT NULL)
material: String
ritual: Boolean(NOT NULL)
duration: String(NOT NULL)
concentration: Boolean(NOT NULL)
casting_time: String(NOT NULL)
level: Integer(NOT NULL)
school_id: ForeignKey(NOT NULL)
In this example, there are twelve columns, four of which are not required. That means we could be getting 12-16 arguments, which is fairly meaty to write out a parameter for each! Enter **kwargs.
Even cooler is that we can pass them in using the double asteriks notation!
More on *args and **kwargs via this stack overflow answer
Let's apply this to our user creation where we don't konw how many values will be passed in.
Right now, our api.py has two get routes; one to /users and one to /users/<int:id>. If @app.route is called with only one parameter, then it will assume that only the GET action is allowed on that route. To add more methods, a second parameter will be added to @app.route() called methods. This will be equal to a list of action strings. In order to access which method the request is using, flask's request will also have to be imported. While we're importing, we'll also need to import our freshly crafted create_user function as well.
🛠The PUT Route -
Calling the update function in our api will look VERY similar to the how we called create_user. The only difference is that it will take an id which will be used to query the database for a specific user which we will update.
Update
The function itself will look a little more complex. The reason this is is because there is no update method attached to our User object. The update has to be explicitly assigned. If we don't know which values are actually present (for example, if bio was passed up in the form as an empty string, we don't want to overwrite our current bio with an empty string), we have to write them all out and add an or statement.
This works out just fine at the level that we are working at, but if we were using the spells schema, this method would get ugly fast.
The Kwarg Method
If we use the **kwarg method, we're going to have to iterate through the given dictionary at set the attribute of our user for each one. It's not too different from the non-kwarg method for our user model, but with more involved tables, this method will add a lot to code readability.
There are three main things that are unfamiliar in this function: 1. update_values.items()—dictionary.items() will return a list of tuples of two, where the first value is the key and the second is the value at that key. If I have a dictionary steven = {"nickname": "Stevie", "age": 44},steven.items() will return a list that looks like this: [('nickname', 'Stevie'), ('age', 44)]. 2. for key, value in update_values.items()—Because each item in the list the comes from .items() is a tuple of two, I know with impunity that we'll be getting two values per iteration. When using this syntax, the value of key is set to the value of tuple at index 0 while value is set to the value of tuple at index 1. Using our steven example from earlier, this loop will run twice because there are two tuples in the list resulting from steven.items(). In the first loop, key == 'nickname' and value == 'Stevie'. In the second iteration, key == 'age' while value == 44. 3. setattr(user, key, value)—With special objects like a SQLAlchemy model, they are non scriptable. That is why, when we used the quick inline return value in our models' as_dict() function, we had to call getattr. getattr takes two parameters, the object whose values you want to pull from, and the name of the key that holds that value. When we setattr, we still want those first two attributes, but we follow it up with a third, which is what we want to set that value to.
Based on how we define this function, we're going to pass it a minimum of one argument, and more if we need. First, we'll want to import our function from user_crud. Next, we'll want to make sure that our /users/<int:id> route is prepared to take PUT methods. After that, we'll add another conditional that states; if the request method is PUT, return the results of the update_user function with an id and a series of arguments as the parameters.
Mouthy pseudocode. Let's see it in action.
and VOILA! We have the power to delete users with a simple form PUT request.
🧨 The DELETE Route -
The Delete function is the easiest mutative function and looks quite a bit like the get_user function.
Destroy
All we need to destroy a user is something to search for them by, in our case, it will be the id.
That's it!
The steps need to add this to our API are the same as for the Update:
Import the function from crud.user_crud
Add 'DELETE' to the list of accepted actions on the relevant route
Add conditional in the route function that checks for request method 'DELETE'
return the results of destroy_user(id)
Still confused? Wanna check your work? Love looking at blocks of DRY code? Eyeballs just can't stop that reading?
You created a RESTful api using Flask and SQLAlchemy!
Not enough coding for you? Itching for more!? Add CRUD for the other two models! Some things to consider:
What information does the user want when they query for all posts? How does this information differ from when they're querying for a single post?
When adding a post, how will you handle adding tags? Will it be a separate route? Will the user be adding it when they add a post? If so is that an optional field? How will you check for existing tags vs new ones?
What if the user wants all posts associated with a user? What is the route for that? Where does that route live? What parameters do you need to make that happen and where will you get them from?
Try your hand at writing a get_or_create function that you can use to return an existing instance or create a new one if it isn't in the database already. What parameters would it need to take? How would you handle variation so it can be used on any model?
Try to read SQLalchemy docs! You're masochistic enough to get to this bullet point in the bonuses, so why not REALLY push your understanding by reading some of the densest docs an ORM can manage.
SQLAlchemy has a lot of functionaly that doesn't exist on the query. How would you impliment some of that in your API?
