So, we created a route! Awesome! But it doesn't do much quite yet. In almost all our APIs, we have a database. Let's create one of our own!
As you might have seen when you started your docker, a flaskapistarterkit_db_1 container was created.
This container contains a database ! You can connect to it by running :
make db/connect
We use postgres for our databases. Here are some commands you could use:
\dt: describe tables\d mytable: describe a table\x: an expanded display\pset pager off: turn the pagination off\o file: save the results of your queries in a file (but then you don't see anything of what you're doing)
That being said, let's make our python server interact with our database.
As you can see, in the project source, there are two folders we did not use yet: models and repositories.
The models will be our database representation in python. If you've coded in Symfony before, it is like the Entity folder.
Let's create a UserModel class:
Create a new user.py file in the models folder. Our user will have a first name, a last name and an age:
"""
Define the User model
"""
from . import db
from .abc import BaseModel
class User(db.Model, BaseModel):
""" The User model """
__tablename__ = 'user'
first_name = db.Column(db.String(300), primary_key=True)
last_name = db.Column(db.String(300), primary_key=True)
# The age of our user
age = db.Column(db.Integer, nullable=True)
def __init__(self, first_name, last_name, age=None):
""" Create a new User """
self.first_name = first_name
self.last_name = last_name
self.age = ageHere, we defined a new class containing four properties: a __tablename__, a first_name, a last_name and an age.
The __tablename__ will be used, as you could have guessed, to define the corresponding talbe name in the database.
The other properties are business properties, add the one you want.
If those properties are a bit tricky to understand, explain it in a comment just above.
We also created one method:
__init__: the constructor of the class
The primary_key=True defines the database property to use as a primary key.
Then nullable=True defines that the property in the databse is nullable.
Add in the __init__.py of the user folder the line from .user import User.
So, we now have an model. How can we create a new table in the database, corresponding to this model?
For that, we use alembic, a version control for the SQLAlchemy vendor.
It creates migrations file (placed in the migrations folder), that will be executed to modify your database.
You can see that the migrations/versions folder is not empty, it already contains a migration.
Run in a terminal:
make db/upgrade
It will setup your database by running the first migration.
Then, to create the migration for the User model run:
make db/migrate
As you can see, a new version was created.
Adapt it (you might have to edit it as sudo in linux) so it looks like this:
""" Create the User table
Revision ID: 51e2c29ad95
Revises: 4f2e2c180af
Create Date: 2016-10-02 16:00:01.042947
"""
# revision identifiers, used by Alembic.
revision = '51e2c29ad95'
down_revision = '4f2e2c180af'
from alembic import op
import sqlalchemy as sa
def upgrade():
op.create_table(
'user',
sa.Column('first_name', sa.String(length=300), nullable=False),
sa.Column('last_name', sa.String(length=300), nullable=False),
sa.Column('age', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('first_name', 'last_name')
)
def downgrade():
op.drop_table('user')Each migration has a upgrade method, that will set your changes to the database, and a downgrade methode, that will unset them. As you can see, we adapted the automaticaly generated file. We:
- deleted the comments generated by alembic
- adapted the file docstring to describe what our migration does (it will be displayed when we run the migration, so it's really usefull to put a meaningfull comment)
Now, we can update tell alembic to update our database:
make db/upgrade
Now, if you connect to the database, you can see that we have a new user table!
A good practice is to upgrade, downgrade and upgrade again to make sure your migration file is correct.
To downgrade, simply run:
make db/downgrade
Now, to interact with our database, let's create a repository.
Create a user.py in the repositories folder, containing:
""" Defines the User repository """
from models import User
class UserRepository:
""" The repository for the user model """
@staticmethod
def get():
""" Query every user """
return User.query.all()Add in the __init__.py of the repositories folder the line
from .user import UserRepositorySQLAlchemy lets us use models to interact directly with the database.
As you can see, we created a new query on the user table with the line User.query.all() that will retrieve all users.
Now let's make our route /users return all our users. In the user.py of the resource folder, import the UserRepository:
from repositories import UserRepositoryThen, in the get method:
def get(self):
""" Return a list of key information about users """
user_repository = UserRepository()
users = user_repository.get()
return usersIf you go to the user route, you will find an empty array. It's normal, there isn't any user in the database yet. Let's create some. Connect to the database and run:
INSERT INTO "user" (first_name, last_name, age) VALUES ('John', 'Doe', 34), ('Jane', 'Doe', 36);
Then call your route again. Oh no! A 500! What happened?
Let's see. If we're in debug mode, the answer will be a page with the error traceback.
Otherwise, all the logs are located in the server.log file (in some APIs, the file is named error.log)
Run:
tail -f server.log
We can read: TypeError: User({'last_name': 'Doe', 'age': 34, 'first_name': 'John'}) is not JSON serializable.
Flask doesn't know how to convert our User into something json serializable.
However, flask knows how to convert dictionaries into JSON.
Let's convert our users into dictionaries. Remember our to_dict method in the User model? We will use that:
Rework your get method so it looks like:
def get(self):
""" Return a list of key information about users """
user_repository = UserRepository()
users = user_repository.get()
return [user.json for user in users]Here, we construct a list of dictionary converted users and return this list. Restart the docker and refresh the page: here are our users!
Now, we have to make sure nothing will ever break our outstanding feature. For that, let's write a test.
In the test/api folder, create a new test_user.py file.
Fill it with:
import unittest
import json
from server import server
from models.abc import db
from models import User
class TestUser(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.client = server.test_client()
def setUp(self):
db.create_all()
User('John', 'Doe', 25).save()
def tearDown(self):
db.session.remove()
db.drop_all()
def test_get(self):
response = self.client.get(
'/application/user',
content_type='application/json'
)
self.assertEqual(response.status_code, 200)
response_json = json.loads(response.data.decode('utf-8'))
self.assertEqual(response_json, [
{
'age': 25,
'first_name': 'John',
'last_name': 'Doe'
},
])What is important here:
- Your test class must inherit from unittest.TestCase
- setUpClass is called once, on the creation of your class
- tearDownClass is called once, on the destruction of your class
- setUp and tearDown are called before and after each test
- your test methods need to begin with
test(eg:test_get) in order to be run - your test files need to begin with
test(eg:test_user) in order to be run
So here, we call our route /user with the method get, we check that we have a 200 HTTP code, and we check that the response is as expected.
Run:
make test
The tests pass! If they do not, verify that you copied the code correctly.
You can see we added a User in our test database in the setUp method. But wait? Here we call the method save() on a User, which we did not add in our User model!
Indeed, this method is present in the BaseModel class in models/abc.py, which is extended by our User model.
You can see this abc (AbstractBaseClass) contains several methods, in particular the methods save and delete, that are pretty self explanatory.
Now, try to change the test by adding a second user in the test database in the setUp method.
Now, you have all the tools you need to create all the routes you want ! Try to create a post method on the /user route, to create a user in the database!
Do not forget to write the corresponding test!
Once you're done, you should be ready to start your own project, we hope you found the tutorial usefull! Feel free to open a PR if you feel anything is missing.