Flask Mega-Tutorial, Part 4: Database (edition 2018)

blog.miguelgrinberg.com

Miguel grinberg

<<< previous next >>>

This article is a translation of the fourth part of the new edition of the Miguel Greenberg textbook. The old translation has long lost its relevance.

This is the fourth edition of the Flask Mega-Tutorial series, in which I will tell you how to work with databases.

For reference, below is a list of articles in this series.

Note 1: If you are looking for old versions of this course, this is here .

Note 2: If suddenly you would like to speak in support of my (Miguel) work on this blog, or simply do not have the patience to wait for a week of the article, I (Miguel Greenberg) offer a full version of this guide a packed e-book or video. For more information, visit learn.miguelgrinberg.com .

The topic of this chapter is extremely important. For most applications, it is necessary to maintain persistent data that can be efficiently extracted, and this is exactly what databases are created for.

GitHub links for this chapter: Browse , Zip , Diff .

Databases in Flask

Since I am sure that you have already heard, Flask does not support databases initially. This is one of the many areas in which Flask is intentionally not self-sufficient, which is great because you have the freedom to choose the database that best suits your application, instead of being forced to adapt to one.

Python has a large selection for databases, many of which integrate with the Flask application. Databases can be divided into two large groups: those that correspond to the relational model, and those that do not. The latter group is often called NoSQL, which indicates that they do not implement the popular relational query language SQL . Although there are excellent database products in both groups, I believe that relational databases are better suited for applications that have structured data, such as user lists, blog posts, etc., while NoSQL databases have tends to be better for data that has a less defined structure. This application, like most others, can be implemented using any type of database, but for the reasons stated above, I am going to work with a relational database.

In chapter 3, I showed you the first Flask extension. In this chapter, I'm going to use two more. The first is Flask-SQLAlchemy , an extension that provides a Flask-friendly wrapper to the popular SQLAlchemy package, which is an Object Relational Mapper or ORM. ORMs allow applications to manage a database using high-level objects such as classes, objects, and methods, rather than tables and SQL. The ORM task is to translate high-level operations into database commands.

The most pleasant thing about SQLAlchemy is that this ORM is not for one, but for many relational databases. SQLAlchemy maintains a long list of database engines, including the popular MySQL , PostgreSQL and SQLite . This is very strong, because you can do your development with a simple SQLite database that does not require a server, and then when the time comes to deploy the application to the production server, you can choose a more reliable MySQL or PostgreSQL server without changing your application.

To install Flask-SQLAlchemy in a virtual environment, make sure that you activate it first, and then run:

(venv) $ pip install flask-sqlalchemy 

Database migration

Most of the database tutorials that I've seen cover creating and using a database, but do not adequately solve the problem of creating updates to an existing database as the application needs to be modified or enlarged. This is difficult because the relational databases are centered around structured data, so when the structure changes, the data that is already in the database must be transferred to the modified structure.

The second extension that I am going to present in this chapter is Flask-Migrate , which is actually created by yours truly. This extension is a Flask wrapper for Alembic , the basis for migrating the SQLAlchemy database. Working with database migrations adds a bit of work at the beginning, but this is a small price to pay for a reliable way to make changes to your database in the future.

The installation process for Flask-Migrate is similar to other extensions you have seen:

 (venv) $ pip install flask-migrate 

Flask-SQLAlchemy configuration

During development, I'm going to use a SQLite database. SQLite databases are the most convenient choice for developing small applications, sometimes not even very small ones, since each database is stored in one file on disk and there is no need to run a database server like MySQL and PostgreSQL.

We have two new configuration items to add to the configuration file:

config.py

 import os basedir = os.path.abspath(os.path.dirname(__file__)) class Config(object): # ... SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \ 'sqlite:///' + os.path.join(basedir, 'app.db') SQLALCHEMY_TRACK_MODIFICATIONS = False 

The Flask-SQLAlchemy extension takes the location of the application database from the SQLALCHEMY_DATABASE_URI configuration SQLALCHEMY_DATABASE_URI . As you remember from Chapter 3 , it is generally recommended to set the configuration from environment variables and provide a fallback value when the environment does not define a variable. In this case, I take the database URL from the DATABASE_URL environment variable, and if it is not defined, I set up a database called app.db located in the main application directory, which is stored in the basedir variable.

The SQLALCHEMY_TRACK_MODIFICATIONS configuration SQLALCHEMY_TRACK_MODIFICATIONS set to False to disable the Flask-SQLAlchemy function, which I do not need, which should signal the application every time a change is made to the database.

The database will be presented in the application as database instance . The database migration mechanism will also have an instance. These are the objects that need to be created after the application in the file app/__ init__.py :

 from flask import Flask from config import Config from flask_sqlalchemy import SQLAlchemy from flask_migrate import Migrate app = Flask(__name__) app.config.from_object(Config) db = SQLAlchemy(app) migrate = Migrate(app, db) from app import routes, models 

I made three changes to the init script. First, I added a db object that represents the database. Then I added another object that represents the migration mechanism. I hope you will see a sample of work with Flask extensions. Most extensions are initialized as these two. Finally, I import a new module called models below. This module will define the database structure.

Database models

The data that will be stored in the database will be represented by a set of classes, commonly called database models. The ORM level in SQLAlchemy will perform the translations necessary to match the objects created from these classes into rows in the corresponding database tables.

Start by creating a model that represents the users. Using the WWW SQL Designer tool, I made the following diagram to represent the data we want to use in the users table:

The id field is usually used in all models and is used as the primary key. Each user in the database will be assigned a unique identifier value stored in this field. Primary keys are in most cases automatically assigned by the database, so I just need to specify the id field marked as the primary key.

The username , email and password_hash fields are defined as strings (or VARCHAR in database jargon), and their maximum length is specified so that the database can optimize space utilization. Although the username and email fields do not require explanation, the password_hash fields are noteworthy. I want to make sure that the application I create uses the best security recommendations, and for this reason I will not store user passwords in the database. The problem with storing passwords is that if a database ever becomes compromised, attackers will have access to passwords, and this can be destructive to users. Instead of writing passwords directly, I'm going to write password hashes , which greatly improve security. This will be the topic of another chapter, so don't worry about it now.

So, now that I know what I need for the table of my users, I can translate it into code in the new app / models.py module :

 from app import db class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) password_hash = db.Column(db.String(128)) def __repr__(self): return '<User {}>'.format(self.username) 

The User class created above inherits from db.Model , the base class for all models from Flask-SQLAlchemy. This class defines several fields as class variables. Fields are created as instances of the db.Column class, which takes a field type as an argument, plus other optional arguments that, for example, allow me to specify which fields are unique and indexed, which is important for efficient database search,

The __repr__ method tells Python how to print objects of this class, which will be useful for debugging. You can see the __repr __() method in action in the Python interpreter session below:

 >>> from app.models import User >>> u = User(username='susan', email='susan@example.com') >>> u <User susan> 

Creating a repository migration

The model class created in the previous section defines the initial structure (or schema ) of the database for this application. But as the application continues to grow, you will need to change the structure, which will most likely add new entities, but sometimes can also change or delete elements. Alembic (the migration infrastructure used by Flask-Migrate) will make these schema changes in such a way that it is not necessary to recreate the database from scratch.

To accomplish this seemingly complex task, Alembic maintains a migration repository, which is the directory in which its migration scripts are stored. Each time a change is made to the database schema, a migration script is added to the repository with the details of the change. To apply migrations to the database, these migration scripts are executed in the order in which they were created.

Flask-Migrate issues its commands via the flask command. You have already seen the flask run , which is a subordinate command that is native to Flask. Flask-Migrate is added to the flask db subcommand to manage everything related to database migration. So let's create a migration repository for microblog by running flask db init :

 (venv) $ flask db init Creating directory /home/miguel/microblog/migrations ... done Creating directory /home/miguel/microblog/migrations/versions ... done Generating /home/miguel/microblog/migrations/alembic.ini ... done Generating /home/miguel/microblog/migrations/env.py ... done Generating /home/miguel/microblog/migrations/README ... done Generating /home/miguel/microblog/migrations/script.py.mako ... done Please edit configuration/connection/logging settings in '/home/miguel/microblog/migrations/alembic.ini' before proceeding. 

Remember that the flask command relies on the FLASK_APP environment variable to know where the Flask application is located. For this application, you want to set FLASK_APP=microblog.py , as described in chapter 1 .

After running this command, you will find a new migrations directory, which has several files and a subdirectory of versions . All of these files should now be considered as part of your project and need to be added to the version control system.

First database migration

With the migration repository, it is time to create the first database migration, which will include the Users table mapped to the user database model. There are two ways to create a database migration: manually or automatically. To automatically create a migration, Alembic compares the database schema defined by the database models with the actual database schema currently used in the database. It then fills the migration script with the changes necessary for the database schema to fit the application models. In this case, since there is no previous database, the automatic transfer will add the entire User model to the transfer script. flask db migrate DB migration subcommand generates these automatic migrations:

 (venv) $ flask db migrate -m "users table" INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.autogenerate.compare] Detected added table 'user' INFO [alembic.autogenerate.compare] Detected added index 'ix_user_email' on '['email']' INFO [alembic.autogenerate.compare] Detected added index 'ix_user_username' on '['username']' Generating /home/miguel/microblog/migrations/versions/e517276bb1c2_users_table.py ... done 

The command output gives you an idea that Alembic is included in the migration. The first two lines are informational and can usually be ignored. Then he says that he found the table 'user' and two indexes '['email']' and '[' username ']'. Then he tells you where he wrote the migration script. The code e517276bb1c2 is an automatically generated unique code for migration (it will be different for you). The comment given with the -m option is optional; it adds a short descriptive text to the hyphen.

The generated migration script is now part of your project and must be included in the version control system. You can view the script if you are interested to see what it looks like. You will find that it has two functions: upgrade() and downgrade() . The upgrade() function applies the migration, and the downgrade() function removes it. This allows Alembic to transfer the database to any point in the story, even to older versions, using a slide path.

The flask db migrate command does not make any changes to the database, it simply creates a migration script. To apply changes to the database, you must use the flask db upgrade command.

 (venv) $ flask db upgrade INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> e517276bb1c2, users table 

Since this application uses SQLite, the Upgrade command will detect that the database does not exist and will create it (you will notice that a file named app.db will be added after the completion of this command, that is, the SQLite database). When working with database servers such as MySQL and PostgreSQL, you must create a database on the database server before starting the update.

The process of updating the database and rolling back the changes Upgrade and Downgrade

At the moment, the application is in its infancy, but it does not hurt to discuss what will be in the database migration strategy in the future. Imagine that you have an application on your development machine, and also have a copy deployed to a production server that is online and in use.

Suppose that for the next version of your application you need to make changes to your models, for example, you need to add a new table. Without migration, you will need to figure out how to change the layout of your database, both on the local host and on your server, and this can be a big problem.

But with database migration support, after modifying the models in the application, you create a new migration script (flask db migrate), you will probably look at it to make sure the automatic creation did the right thing, and then apply the changes to the development database (flask db upgrade). You will add the migration script to the version control system and fix it.

When you are ready to release the new version of the application on your production server, all you need to do is grab the updated version of the application, which will include the new migration script and run flask db upgrade . Alembic will detect that the database has not been updated to the latest edition, and will perform all the new migration scenarios created since the previous release.

As I mentioned earlier, you also have the downgrade flask db command, which cancels the last migration. Although you will hardly need this option at the time of operation, you may find it very useful during development. You may have generated the migration script and applied it only to find that the changes you made are not exactly what you need. In this case, you can lower the database rating, delete the migration script, and then create a new one to replace it.

Database links

Relational databases are good at storing links between data items. Consider the case when a user writes a blog post. The user will have an entry in the user table, and the message will have an entry in the message table. The most effective way to write down who wrote this message is to link two records.

After the connection is established between the user and the post, there are two types of requests that we may need. The most trivial is when you have a post and you need to know which user wrote it. A slightly more complicated question is the opposite of this. If you have a user, then you may need to get all the entries written by him. Flask-SQLAlchemy will help us with both types of queries.

Expand our base for storing posts so that we can see the connections in action. To do this, we will return to our database design tool and create a table of records:

The Message table will have the required ID, message text and time stamp. But in addition to these expected fields, I add a user_id field that links the message to its author. You have seen that all users have a primary key id , which is unique. The way to associate a blog entry with the user who created it is to add a link to the user ID, and this is exactly what the user_id field user_id . This user_id field is called a foreign key. In the above database diagram, foreign keys are displayed as a link between the field and the id field of the table to which it refers. This kind of relationship is called one-to-many, because "one" user writes "many" messages.

A modified app / models.py is shown below:

 from datetime import datetime from app import db class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) password_hash = db.Column(db.String(128)) posts = db.relationship('Post', backref='author', lazy='dynamic') def __repr__(self): return '<User {}>'.format(self.username) class Post(db.Model): id = db.Column(db.Integer, primary_key=True) body = db.Column(db.String(140)) timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) def __repr__(self): return '<Post {}>'.format(self.body) 

The new Post class will submit blog posts written by users. The timestamp field will be indexed, which is useful if you want to receive messages in chronological order. I also added a default argument and passed the datetime.utcnow function. When you pass the default function, SQLAlchemy will set the value of the call to this function for the field (note that I did not include () after utcnow , so I pass this function myself, and not the result of calling it). In general, this will allow working with UTC dates and times in the server application. This ensures that you use uniform time stamps regardless of where the users are located. These timestamps will be converted to user local time when they are displayed.

The user_id field was initialized as a foreign key for user.id , which means that it refers to the id value from the users table. In this link, user is the name of the database table that Flask-SQLAlchemy automatically sets as the name of the model class converted to lowercase. The User class has a new message field that is initialized by db.relationship . This is not the actual database field, but a high-level view of the relationship between users and posts, and for this reason it is not in the database diagram. For a one-to-many relationship, the db.relationship field is usually defined on the “one” side and is used as a convenient way to access “many”. So, for example, if I have a user stored in u , the expression u.posts will run a database query that returns all the entries written by this user. The first argument db.relationship specifies the class that represents the side of the "many" relationship. The backref argument specifies the name of the field that will be added to objects of the class "many", which points to the object "one". This will add a post.author expression that will return the author of the message. The lazy argument determines how the database query will be performed for the link, which I will discuss later. Do not worry if these details do not make sense to you, I will show examples at the end of this article.

Since I have updates for application models, I need to create a new database migration:

 (venv) $ flask db migrate -m "posts table" INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.autogenerate.compare] Detected added table 'post' INFO [alembic.autogenerate.compare] Detected added index 'ix_post_timestamp' on '['timestamp']' Generating /home/miguel/microblog/migrations/versions/780739b227a7_posts_table.py ... done 

And the migration should be applied to the database:

 (venv) $ flask db upgrade INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.runtime.migration] Running upgrade e517276bb1c2 -> 780739b227a7, posts table 

If the project is stored in a version control system, do not forget to add a new migration script to it.

Start time

I made you go through the long process of creating a database, but I have not shown you how everything works. Since the application does not yet have database logic, let's play around with the database in the Python interpreter to familiarize yourself with it. So go ahead and run Python. Before starting the interpreter, make sure that your virtual environment is activated.

On the Python command line, let's import the database instance and models:

 >>> from app import db >>> from app.models import User, Post 

Start by creating a new user:

 >>> u = User(username='john', email='john@example.com') >>> db.session.add(u) >>> db.session.commit() 

, db.session . , , db.session.commit() , . , db.session.rollback() , . , db.session.commit() . , .

:

 >>> u = User(username='susan', email='susan@example.com') >>> db.session.add(u) >>> db.session.commit() 

, :

 >>> users = User.query.all() >>> users [<User john>, <User susan>] >>> for u in users: ... print(u.id, u.username) ... 1 john 2 susan 

, . — , , all() . , id 1 2.

. , :

 >>> u = User.query.get(1) >>> u <User john> 

:

 >>> u = User.query.get(1) >>> p = Post(body='my first post!', author=u) >>> db.session.add(p) >>> db.session.commit() 

timestamp , , . user_id? , db.relationship , User , posts , . , , , . SQLAlchemy , .

, :

 >>> # get all posts written by a user >>> u = User.query.get(1) >>> u <User john> >>> posts = u.posts.all() >>> posts [<Post my first post!>] >>> # same, but with a user that has no posts >>> u = User.query.get(2) >>> u <User susan> >>> u.posts.all() [] >>> # print post author and body for all posts >>> posts = Post.query.all() >>> for p in posts: ... print(p.id, p.author.username, p.body) ... 1 john my first post! # get all users in reverse alphabetical order >>> User.query.order_by(User.username.desc()).all() [<User susan>, <User john>] 

Flask-SQLAlchemy- , , .

, , , :

 >>> users = User.query.all() >>> for u in users: ... db.session.delete(u) ... >>> posts = Post.query.all() >>> for p in posts: ... db.session.delete(p) ... >>> db.session.commit() 

shell context

, , Python? , , :

 >>> from app import db >>> from app.models import User, Post 

, Python, . flask shell — . shell — "", flask , . - Python . What does it mean? Consider the following example:

 (venv) $ python >>> app Traceback (most recent call last): File "<stdin>", line 1, in <module> NameError: name 'app' is not defined >>> (venv) $ flask shell >>> app <Flask 'app'> 

app , , flask shell . flask shell , , , «shell context», .

microblog.py , :

 from app import app, db from app.models import User, Post @app.shell_context_processor def make_shell_context(): return {'db': db, 'User': User, 'Post': Post} 

app.shell_context_processor . flask shell , , . , , , , , , .

, flask shell , , :

 (venv) $ flask shell >>> db <SQLAlchemy engine=sqlite:////Users/migu7781/Documents/dev/flask/microblog2/app.db> >>> User <class 'app.models.User'> >>> Post <class 'app.models.Post'> 

<<< previous next >>>