Creating databases in Flask

by Alex
Creating databases in Flask
In this lesson we will talk about interacting with the database. There are two competing database systems today:

  1. Relational databases.
  2. Non-relational or NoSQL databases.

Relational databases have traditionally been used in web applications. Many major players in the web programming market still use them. Facebook, for example. Relational databases store data in tables and columns and use a foreign key to create links between multiple tables. Relational databases also support transactions. This means that you can execute a set of SQL statements that must be atomic. By atomic we mean all operators that are executed on an all-or-nothing basis. NoSQL databases have grown in popularity in recent years. Such databases do not store data in tables and columns, but instead use structures such as document stores, key and value stores, graphs, and so on. Most NoSQL databases do not support transactions, but offer faster performance.

Relational databases are much older than NoSQL. They have proven to be reliable and secure in many industries. Consequently, the remainder of the lesson will focus on describing how to use relational databases in Flask. This is not to say that NoSQL is not used. There are cases where NoSQL databases even make more sense, but now we will talk only about relational databases.

SQLAlchemy and Flask-SQLAchemy

SQLAlchemy is a framework for working with relational databases in Python in practice. It was created by Mike Bayer in 2005. SQLAlchemy supports the following databases: MySQL, PostgreSQL, Oracle, MS-SQL, SQLite and others. SQLAchemy comes with a powerful ORM (object-relational mapping technology), which allows you to work with different databases using object-oriented code instead of raw SQL (structured query language). Of course, this doesn’t require using only ORM. At any time, you can use the capabilities of SQL. Flask-SQLAlchemy is an extension that integrates SQLAlchemy into the Flask framework. It also offers additional methods that make working with SQLAlchemy a little easier. You can install Flask-SQLAlchemy along with the add-ons using the following command:

(env) [email protected]:~/flask_app$ pip install flask-sqlalchemy


To use Flask-SQLAlchemy, you need to import the SQLAlchemy class from the flask_sqlalchemy package and create an instance of the SQLAlchemy object by passing an application instance to it. Let’s open the main2.py file to change the code as follows:

#...
from forms import ContactForm
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.debug = True
app.config['SECRET_KEY'] = 'a really really really long secret key'

manager = Manager(app)
db = SQLAlchemy(app)

class Faker(Command):
#...

The db instance of the SQLAlchemy object provides access to the SQLAlchemy functions. Next, you need to tell SQLAlchemy the location of the database as a URI. The format of the database URI is as follows:

dialect+driver://username:[email protected]:port/database

dialect refers to the name of the database, such as mysql, mssql, postgresql, and so on. driver refers to the DBAPI it uses to connect to the database. By default, SQLAlchemy only works with SQLite without any additional drivers. To work with other databases, you need to install a specific DBAPI-compatible database driver. What is DBAPI? DBAPI is just a standard that defines a Python API for accessing databases from different vendors. The following table contains some DBAPI-compatible databases and drivers for them:

Database DBAPI driver
MySQL PyMysql
PostgreSQL Psycopg 2
MS-SQL pyodbc
Oracle cx_Oracle

Username and password are specified only when necessary. If specified, they will be used for database authorization. host – the location of the database server. port – port of the database server. database – database name. Here are some examples of database URLs for the most popular types:

# Database URL for MySQL using the PyMysql driver
'mysql+pymysql://root:[email protected]/my_db'

# Database URL for PostgreSQL using psycopg2
'postgresql+psycopg2://root:[email protected]/my_db'

# Database URL for MS-SQL using the pyodbc driver
'mssql+pyodbc://root:[email protected]/my_db'

# Database URL for Oracle using the cx_Oracle driver
'oracle+cx_oracle://root:[email protected]/my_db'

The database URL format for SQLite is slightly different. Because SQLite is a file-based database and does not require a username and password, the database URL only includes the path to the database file.

# For Unix / Mac we use 4 slashes
sqlite:////absolute/path/to/my_db.db

# For Windows we use 3 slashes
sqlite:///c:/absolute/path/to/mysql.db

Flask-SQLAlchemy uses the SQLALCHEMY_DATABASE_URI configuration key to define the database URI. Let’s open main2.py to add SQLALCHEMY_DATABASE_URI:

#...
app = Flask(__name__)
app.debug = True
app.config['SECRET_KEY'] = 'a really really really long secret key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:[email protected]/flask_app_db'

manager = Manager(app)
db = SQLAlchemy(app)
#...

This course will use a MySQL database. So before moving on to the next section, you need to make sure MySQL is running on your computer.

Creating models

A model is a class in Python that represents a database table. Its attributes are mapped to table columns. The model class inherits from db.Mobel and defines columns as instances of the db.Column class. Let’s open main2.py to add the following class before the updating_session() view:

#...
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

#...

class Post(db.Model):
    __tablename__ = 'posts'
   id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(255), nullable=False)
    slug = db.Column(db.String(255), nullable=False)
    content = db.Column(db.Text(), nullable=False)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)
    updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)

   def __repr__(self):
	return "<{}:{}>".format(self.id, self.title[:10])

This creates a Post model with 5 class variables. Each variable of the class, except __tablename__, is an instance of the db.Column class. __tablename__ is a special class variable used to define the database table name. By default, SQLAlchemy does not follow the convention of creating plural names, so the table name here is the model name. If you want to rely on this behavior, you should use the __tablename__ variable to explicitly specify the table name. The first argument of the db.Column() constructor is the type of column being created. SQLAlchemy offers a large number of column types, and if they are not enough, you can create your own. The following table describes the basic column types in SQLAlchemy and their corresponding types in Python and SQL.

SQLAlchemy Python SQL
BigInteger int BIGINT
Boolean bool BOOLEAN or SMALLINT
Date datetime.date DATE
DateTime datetime.date DATETIME
Integer int INTEGER
Float float FLOAT or REAL
Numeric decimal.Decimal NUMERIC
Text str TEXT

You can also specify additional column constraints by passing them as keyword arguments to the db.Column constructor. The following table includes some commonly used constraints:

Restriction Description
nullable When the value is False, makes the column mandatory. The default value is True.
default Creates a default value for the column.
index Logical attribute. If True, creates an indexed column.
onupdate Creates a default value for the column when the record is updated.
primary_key Logical attribute. If True, marks the column with the primary key of the table.
unique Logical attribute. If True, each column must be unique.

Lines 16-17 defined the __repr__() method. It is not necessary, but if present, it creates a string representation of the object. You could notice that the default values for created_on and updated_on are the name of the method(datetime.utcnow) rather than its call(datetime.utcnow()). This is done because there is no need to call the datetime.utcnow() method when executing the code. Instead, it should be called when a record is added or updated.

Defining relationships (links)

In the last section, we created a Post model with a pair of fields. In practice, model classes exist by themselves. Most of the time, they are related to other models by various types of relationships: one-to-one, one-to-many, many-to-many. It is worth working further on the analogy of the blog. Typically, a blog post belongs to one category and has one or more tags. In other words, there is a one-to-one relationship between a category and a post, and a many-to-many relationship between a post and a tag. Open main2.py to add the Category and Tag models:

#...
def updating_session():
   #...
   return res

class Category(db.Model):
    __tablename__ = 'categories'
   id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    slug = db.Column(db.String(255), nullable=False)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)

   def __repr__(self):
	return "<{}:{}>".format(id, self.name)

class Posts(db.Model):
   # ...

class Tag(db.Model):
    __tablename__ = 'tags'
   id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    slug = db.Column(db.String(255), nullable=False)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)

   def __repr__(self):
	return "<{}:{}>".format(id, self.name)
#...

The one-to-many relationship

To create a one-to-many relationship, you need to place a foreign key in the child table. This is the most common type of relationship. To create a one-to-many relationship in SQLAlchemy, follow these steps:

  1. Create a new db.Column instance using the db.ForeignKey constraint in the child class.
  2. Define a new property using the db.relationship instruction in the parent class. This property will be used to access related objects.

Open main2.py to change the Post and Cathegory models:

#...
class Category(db.Model):
   # ...
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)
    posts = db.relationship('posts', backref='category')

class Post(db.Model):
   # ...
    updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)
    category_id = db.Column(db.Integer(), db.ForeignKey('categories.id'))
#...

Two new attributes have been added here for the Post model in Category: posts and category_id. db.ForeignKey() takes the name of the column whose foreign key is used. Here the value of categories.id is passed to the db.ForeignKey() exception. This means that the category_id attribute of posts can only take value from the id column of the categories table. Next, the Catagory model has a posts attribute defined by the db.relationship() instruction. db.relationship() is used to add a bidirectional relationship. In other words, it adds an attribute to the model class to access related objects. In simple words, it takes at least one positional argument, which is the name of the class on the other side of the relationship.

class Category(db.Model):
   # ...
    posts = db.relationship('posts')

For example, if there is a Category object (say, c), then all posts can be accessed using c.posts. But what if you want to get the data on the other side, that is, get the category from the post object? That’s what the backref is used for. So, the code is:

posts = db.relationship('Post', backref='category')

adds a category attribute to the Post object. This means that if there is a Post object (for example, p), then the category can be accessed with p.category. The category and posts attributes of Post and Category objects exist only for convenience. They are not actual columns in the table. It is worth noting that, unlike the attribute represented by the foreign key (which must be defined on the “many” side of the relationship), db.relationship() can be defined on either side.

One-to-one relationship

Creating a one-to-one relationship in SQLAlchemy is almost the same as a one-to-many relationship. The only difference is that the db.relationship() instruction is passed an additional argument uselist=False. For example:

class Employee(db.Model):
    __tablename__ = 'employees'
   id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    designation = db.Column(db.String(255), nullable=False)
    doj = db.Column(db.Date(), nullable=False)
    dl = db.relationship('DriverLicense', backref='employee', uselist=False)

class DriverLicense(db.Model):
    __tablename__ = 'driverlicense'
   id = db.Column(db.Integer(), primary_key=True)
    license_number = db.Column(db.String(255), nullable=False)
    renewed_on = db.Column(db.Date(), nullable=False)
    expiry_date = db.Column(db.Date(), nullable=False)
    employee_id = db.Column(db.Integer(), db.ForeignKey('employees.id')) # Foreign key

Note: these classes assume that an employee cannot have more than one driver license. Therefore the relationship between employee and driver license is one-to-one. With the Employee object you can use e.dl to return a DriverLicense object. If you do not pass the db.relationship() instruction uselist=False, then a one-to-many relationship will be established between Employee and DriverLicense, and e.dl will return a list of DriverLicense objects, instead of one object. The uselist=False argument will not affect the employee attribute of the DriverLicense object. As usual it will return a single object.

Many-to-many relation

The many-to-many relationship requires an additional associative table. An example would be a blog. A blog post usually has one or more tags. Similarly, one tag may be associated with one or more posts. This is how the relationship between posts and tags is formed. It is not enough to add a foreign key referencing the id of posts, because a tag can have one or more posts.

The solution is to create a new association table by defining 2 foreign keys referencing the post.id and tag.id columns.Creating databases in FlaskAs you can see in the image, the many-to-many relationship between post and tag is created using two one-to-one relationships. The first is between the posts and post_tags tables and the second is between tags and post_tags. The following code demonstrates how to create a many-to-many relationship in SQLAlchemy. Open the main2.py file to add the following code.

# ...
class Category(db.Model):
   # ...
   def __repr__(self):
	return "<{}:{}>".format(id, self.name)
	
post_tags = db.Table('post_tags',
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'))
)

class Post(db.Model):
   # ...
    
class Tag(db.Model):
   # ...
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)
    posts = db.relationship('posts', secondary=post_tags, backref='tags')
#...

On lines 7-10, the association table is defined as a db.Table() object. The first argument of db. Table () is the name of the table, and the additional arguments are the columns represented by instances of db.Column(). The syntax for creating an association table may seem strange when compared to the process of creating a model class. This is because the association table is created with SQLAlchemy Core, another SQLAlchemy element. Next, you need to tell the model class about the association table to be used. The keyword argument secondary is responsible for this. On line 18, db.relationship() is called with the secondary argument, whose value is post_tags. Although the relationship has been defined in the Tag model, it can just as easily be defined in the Post model. If there is, for example, an object p of the Post class, then all of its tags can be accessed using p.tags. With an object of class Tag(t), posts can be accessed with t.posts. Now it’s time to create the database and tables.

Creating Tables

To complete all of the steps in the lesson, you need to make sure that MySQL is installed on your computer. It’s worth remembering that by default, SQLAlchemy only works with the SQLite database. To work with other databases, you need to install a DBAPI-compatible driver. To use MySQL, the PyMySql driver is suitable.

(env) [email protected]:~/flask_app$ pip install pymysql

After that, you need to log in to the MySQL server and create the flask_app_db database with the following command:

(env) [email protected]:~/flask_app$ mysql -u root -p
mysql>
mysql> CREATE DATABASE flask_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.26 sec)
mysql> q
Bye
(env) [email protected]:~/flask_app$

This command creates a flask_app_db database with full Unicode support. To create the necessary tables, you need to run the create_all() method of the SQLAlchemydb object. Next you need to run the Python shell and execute the following command:

(env) [email protected]:~/flask_app$python main2.py shell
>>>
>>> > from main2 import db
>>>
>>> db.create_all()
>>>

The create_all() method creates tables only if they are not in the database. Therefore, you can run it several times. Also, this method does not take into account model changes when creating tables. This means that if you run the create_all() method after changing its method when the table is already created, it will not change the table schema. To do this, you need to use the Alembic migration tool. How to migrate databases with Alembic will be explained in a separate tutorial, “Migrating a Database with Alembic”. To view the created tables, you need to log in to the MySQL server and run the following command:

mysql>
mysql> use flask_app_db
Database changed
mysql>
mysql> show tables;
+------------------------+
| Tables_in_flask_app_db |
+------------------------+
| categories |
| post_tags |
| posts |
| tags |
+------------------------+
4 rows in set (0.02 sec)

mysql>

Another way to see the tables is to use a database administration tool such as HeidiSQL. HeidiSQL is a cross-platform, open-source software for managing MySQL, MS-SQL, and PostgreSQL databases. It allows you to view and edit data, view the schema, change the table and do much more without a single line of SQL. Download HeidiSQL from here. By installing HeidiSQL on top of the flask_app_db database, you can get approximately the following list of tables:Creating databases in FlaskThe flask_app_db database has 4 tables. The tables named categories, posts, and tags are created directly from the models, and post_tags is an association table that represents the many-to-many relationship between the Post and Tag models. The SQLAlchemy class also defines a drop_all() method, which is used to remove all tables in the database. It is worth remembering that the drop_all() method does not take into account whether or not there is data in the table. It deletes all data, so you should use it wisely. All the tables are in place. It’s time to add some data to them.

Related Posts

LEAVE A COMMENT