Flask Database Handling – How To Use Flask With A Database

In this Flask database tutorial, learn to use Flask with various databases, such as Flask MySQL, Flask MongoDB, SQLite, etc.

We will extend the concepts covered in our first Flask Python tutorial. We start by using Flask with MongoDB, a document-based NoSQL database, where NoSQL stands for Not Only SQL.

First, we cover the concept of connecting with the database, and then we let you know how to not get locked with one database. If required, we can modify only the configurations to change the database backend.

=> Check Out The Perfect Flask Training Guide Here

Flask Database Tutorial

In this tutorial, readers can draw a comparison between the discussed databases. Furthermore, we talk about Flask-MongoEngine, Flask-SQLAlchemy, and Flask MongoAlchemy. These two ORMs i.e. Object Relation Mapper, are quite popular.

ORMs under the hood transparently translate the objects (database models) into database commands or SQL statements.

The benefits of using an ORM are listed below:

1. Developers can work with objects instead of tables and SQL.
2. Use Migrations to keep a track of database updates.
3. It reduces development costs and time.
4. It overcomes database-specific SQL differences.

When using ORMs, the programmers do not have to write complex SQL queries and commands to perform base SQL commands.

Connect To The Database

Open the configuration file and notice the below-mentioned values. Flask-Appbuilder takes the database details on the connection string from the mentioned values.

# The MongoEngine connection string.
MONGODB_SETTINGS = {
        'DB': 'mydb',
        'connect': False,
}

All low-level features of database management by the ORM have been wrapped under Flask Click commands, which we can see using flask fab –help on the command line.

Flask MongoDB

In this section, we will learn how to use ORMs rather than using raw SQL scripts to work with databases in Flask.

MongoDB is a non-relational document-based database. We have already configured it with our current flask tutorial example application.

Use the below-given commands to manage the MongoDB server on the local machine.

sudo systemctl start mongod # to start MongoDB
sudo systemctl stop mongod # to stop MongoDB
sudo systemctl status mongod # to check status MongoDB

We have discussed two famous ORM’s that you can use with MongoDB and Flask.

Using a database designer, we created two tables called Album and Song and defined a one-to-many relationship between Album and Song. Given below is the image depicting the same.

Flask MongoEngine

Now let’s create our first MongoEngine DB model.

Create or edit the file models.py under the app directory and add the following code.

from mongoengine import Document
from mongoengine import DateTimeField, StringField, ReferenceField, ListField, IntField
class Album(Document):
    name = StringField(unique=True, required=True, max_lenth=100)   

 def __str__(self):
        return self.name

class Song(Document):
    title = StringField(max_lenth=200, required=True, unique=True)
    rating = IntField(default=0,max_lenth=1) # 1 to 9
    album = ReferenceField(Album)

    def __str__(self):
        return self.title

We have created two MongoEngine models called Album and Song. These models correspond to the respective documents in MongoDB.

Album has one field of type string with some constraints.

• Album name is unique.
• Album name cannot be blank.
• Album name can be of maximum hundred characters.

Similarly, the document Song has a title, a rating field, and a reference field that points to another document, Album. Let’s save this file and create data using these two models. Go to the project’s root directory and use the flask shell command to access the flask application in Python shell.

Once you enter the shell, use the below statements to access the MongoEngine models and create sample data as shown below.

>>> from app.models import Album, Song
>>> album1 = Album(name="Album1")
>>> album1.save()
>>> song1 = Song(title="Song1", rating=9, album=album1)
>>> song1.save()

Now let us access the database using Mongo client and see whether data is saved as a result of the statements given above. In the above code, we first import Album and Song, then create their objects with the required values of the parameters.

Here parameters are the field names as defined in the models, and we mention our data as values to those parameters. Once the object creation is successful, we call the save method on the respective objects to save the documents in the database.

Use the mongo command to access MongoDB. Once you connect to the Server using the mongo client, use the below commands.

#1) Check the list of databases

> show dbs
#----- output ---- 
admin 0.000GB
config 0.000GB
local 0.000GB
mydb 0.001GB
#----- output ----

#2) Use our database called mydb

> use mydb
#----- output ---- 
switched to db mydb
#----- output ----

#3) Check the collections a.k.a tables in RDBMS

> show collections
#----- output ---- 
album
permission
permission_view
role
song
user
view_menu
#----- output ----

#4) List any one document in the album collection.

> db.album.findOne()
#----- output ---- 
{ "_id" : ObjectId("5eddb43b8b1e179bef87d41d"), "name" : "Album1" }
#----- output ----

#5) List any one document in the song collection.

> db.song.findOne()
#----- output ---- 
{
"_id" : ObjectId("5eddb74b0177c8f096d880ec"),
"title" : "Song1",
"rating" : 9,
"album" : ObjectId("5eddb43b8b1e179bef87d41d")
}
#----- output ----

If you have worked with Django, then you will realize that MongoEngine works very similar to Django’s inbuilt ORM. In the last output, when we queried a song, notice how the reference of another document is for the album field.

Now let’s create another album and perform an update on the existing song document.

>>> from app.models import Album, Song
>>> album2 = Album(name='Album2')
>>> album2.save()
>>> songs_q = Song.objects(title='Song1') # query the database
>>> songs_q.count()
1
>>> song1 = songs_q[0]
>>> song1.album = album2 # update the album field
>>> song1.save()

We import both the models i.e. Album and Song. Then create a new document called album2. Query Song collection in the database and get the song using its title. Then we access the object using an array index of the query result, update using an assignment operator, and save the updated document.

Now let’s use Mongo client again to check the stored collections.

> db.album.find().pretty()
## output
{ "_id" : ObjectId("5eddb43b8b1e179bef87d41d"), "name" : "Album1" }
{ "_id" : ObjectId("5eddbaab9fd7d3ec78b2fd8f"), "name" : "Album2" }

> db.song.find().pretty()
## output
{
        "_id" : ObjectId("5eddb74b0177c8f096d880ec"),
        "title" : "Song1",
        "rating" : 9,
        "album" : ObjectId("5eddbaab9fd7d3ec78b2fd8f")
}

In the output of the second query in the above snippet, notice the updated album field of the Song1 document.

Now let’s delete the documents in both Album and Song collection. Use the below code to remove the records. If you are still in the flask shell, then use the below-mentioned commands to delete a document and verify the deletion.

>>> song1.delete()
>>> songs_q = Song.objects(title='Song1')
>>> songs_q.count()
0
>>>

We use the delete method on song1 to delete the document from the song collection. We could perform all basic CRUD operations using a flask shell. Furthermore, we can use the ModelView class of flask_appbuilder to display the database models as views.

Create model-based views, as shown in the code below.

from app.models import Album, Song
from flask_appbuilder import ModelView
from flask_appbuilder.models.mongoengine.interface import
 MongoEngineInterface

class SongsView(ModelView):
    datamodel = MongoEngineInterface(Song)

class AlbumView(ModelView):
    datamodel = MongoEngineInterface(Album)

We first import the database models, along with ModelView and MongoEngineInterface. Then we subclass the ModelView and assign particular MongoEngineInterface instances to the data model attribute of our views.

Now let’s register SongsView and AlbumView with the menu as shown below under the same category.

appbuilder.add_view(AlbumView, "Album View", category="Model Views")
appbuilder.add_view(SongsView, "Song View", category="Model Views")

To access those views on the application, navigate to http://localhost:8080/, log into the app using the admin credentials, and perform the below-mentioned steps to understand the default database model-based views.

Step 1: Click on the Model View menu

Step 2: Click on Album View submenu.

Step 3: Click on the plus icon to create a document or a record.

Step 4: Enter the name of the Album and save it.

Similar to the steps above, you can perform all CRUD operations using these views. Therefore, let us create a song using Song View submenu as shown in the below image. Notice, how a reference field of a related database model is displayed in the dropdown. Try creating some more Albums and Songs.

You can further explore the same concepts using MongoAlchemy; Another easy to use, and similar ORM built for easy MongoDB database access and manipulation using Python.

Please check the documentation of MongoAlchemy here. However, we recommend building a basic understanding of Flask-SQLAlchemy by going through the below section first.

Flask Sqlite Or Flask MySQL

In this section, We repurpose the same application for SQLAlchemy as the backend engine. Therefore, please commit all your changes so far and create a separate Git branch tutorial-3-sqla. Flask can use SQLite and MySQL as a backend database. We recommend that you use SQLAlchemy as ORM with these relational databases.

Let us start with the changes that we need to make after you check out a new branch.

Config

Open config.py in the project’s root directory and remove the connection string of MongoDB. Update the config.py with the connection string for Flask SQLite or Flask MySQL.

# The SQLAlchemy connection string.
SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.join(basedir, "app.db")
# SQLALCHEMY_DATABASE_URI = 'mysql://myapp@localhost/myapp'

App __init__.py

Now open app/__init__.py file and remote MongoEngine related imports, and import SQLA as shown below.

#from flask_appbuilder.security.mongoengine.manager import SecurityManager
from flask_appbuilder import AppBuilder, SQLA
#from flask_mongoengine import MongoEngine

# other lines of code

#db = MongoEngine(app)
db = SQLA(app)

#appbuilder = AppBuilder(app, security_manager_class=SecurityManager)
appbuilder = AppBuilder(app, db.session)

Flask Models

Update models.py with the following code and remove the code related to MongoEngine.

from flask_appbuilder import Model
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

Flask Views

Update views.py with the following code.

from flask_appbuilder import ModelView
from app.models import Album, Song

class AlbumView(ModelView):
    datamodel = SQLAInterface(Album)

class SongsView(ModelView):
    datamodel = SQLAInterface(Song)

appbuilder.add_view(AlbumView, "Album View", category="Model Views")
appbuilder.add_view(SongsView, "Song View", category="Model Views")

Note that we have used the same ModelView class however changed MongoEngineInterface with SQLAInterface.

To create the tables and their corresponding relationships, we execute the below-mentioned command to create database objects.

flask fab create-db

Remember that we have changed the database backend. Therefore, use the flask fab create-admin command to create the admin user. Now start the development server as earlier; using python run.py. Navigate to http://localhost:8080.

Here at this point, our application will work as it worked in the case of MongoDB. Test it out with all CRUD operations, as we did in the previous sections.

Moreover, we have shown both the corresponding tables for the database models while using SQLite DB Browser.

Flask MySQL

To use MySQL as database backend, when we use Flask-SQLAlchemy, we need only to update one configuration concerning the database in the config.py.

SQLALCHEMY_DATABASE_URI = 'mysql://myapp@localhost/myapp'

As per the given connection string, the name of the database is myapp. The user to connect with the database is myapp@localhost. However, both of these are prerequisites, and we should create using the details given below.

Therefore, use the queries given below to create a user and a database to make Flask MySQL database work in conjunction with each other. Execute these queries in MySQL client.

CREATE USER 'myapp'@'localhost' IDENTIFIED BY '';

GRANT ALL PRIVILEGES ON *.* TO 'myapp'@'localhost';

FLUSH PRIVILEGES;

CREATE DATABASE myapp;

We also need to install Python3 mysqlclient. Install development headers and libraries as given in the below commands.

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
pip install mysqlclient

Now because we have changed the database backend, we need to create tables corresponding to the database models. We also need to create a Flask admin user as all the registered menu items are protected and can be accessed only by an existing user in the application.

These commands given below hide the lower-level SQL statements for dropping and creating the tables.

flask fab create-db
flask fab create-admin

On successful completion of all the steps above, we can again navigate to http://localhost:8080 . Now access the application as we obtained in the case of Flask SQLite.

Flask Migrate

During the early development of an application, there are many changes to the schema of a database. Developers that adds quite an overhead to the development time need to make these changes frequently. In such similar scenarios, the Flask-Migrate plugin is quite helpful.

Let’s install Flask-Migrate.

pip install flask-migrate

On successful installation, a db subcommand is added. Check the command-line utilities that are added to this db subcommand by using the below-mentioned code.

flask db --help

First, we need to create a migrate object, as shown below in app/__init__.py.

from flask import Flask
from flask_migrate import Migrate
app = Flask(__name__)
migrate = Migrate(app, db)

Let us try some commands with the database that we have in our application.

Initialize a separate migration repository.

flask db init

Similar to the above commands, there are commands to create migrations and apply them using the upgrade command. We will use these migration commands as a part of the workflow in our subsequent tutorials whenever required.

Frequently Asked Questions

You might come across some of the questions related to the use of databases with Flask.

Q #1) What database does Flask use?

Answer: Flask supports all databases that are supported by SQLAlchemy, which is a database toolkit for Python, and is an ORM (Object Relation Mapper). We can install Flask-SQLAlchemy from PyPI to work with SQLAlchemy.

Flask-Alchemy is a Flask plugin and requires minimal configuration other than its installation. Some of the prevalent databases that developers use with Flask-SQLAlchemy are SQLite, PostgreSQL, MySQL, etc.

Flask also has plugins such as Flask-MongoEngine, Flask-MongoAlchemy, Flask-CouchDB, etc. to work with NoSQL document-based databases such as MongoDB, and CouchDB.

Q #2) How do I create a database in Flask?

Answer: The creation of a Database in Flask is generally dependent on the pattern followed by the corresponding Flask plugin. Almost all plugins create databases based on the database connection settings defined in the Flask configuration in the project.

However, you can write your own method to create a database in Flask when not using a plugin.

We have given a trivial example of creating an SQLite example below. This example uses g Object to keep the reference of the Database connection.

import sqlite3
from flask import g # g and current_app object
current_app.config['DATABASE'] = 'MYDB' # Name of the database

def get_db():
    '''A method to get the database connection'''
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row 

   return g.db
def close_db(e=None):
    '''A method to close the database connection'''
    db = g.pop('db', None)    
if db is not None:
        db.close()

Q #3) How do you display data from a database in Flask?

Answer: In Flask, developers use various Object Relational Mappers, also called ORM. These ORMs generally have API’s to access the database using the query attribute to read the data from a defined database model. Results of queries that are stored in Python’s data structures are displayed with the help of Flask Templates.

However, while testing the database models, the results can also be printed on the Console in Flask Shell.

One such example of querying the data using the queries API in Flask-SQLAlchemy is given below.

>>> user1 = User.query.filter_by(username='testuser').first()
>>> user1.id
2
>>> user1.email
u'user1@example.org'

Conclusion

In this tutorial, we covered concepts related to connecting to different databases using the same project layout. We moved away from the paradigm of writing raw SQL queries inside the code.

An approach to writing tables in the form of models makes us more agile. We also covered the concepts of storing database information as migrations. Migrations further add more flexibility to our development workflow.

So far, we have worked on an archetype that is automatically generated by the Flask app builder. In our next tutorials in this series, we take one more step and discuss the other Flask boilerplates and the concepts of working with Flask blueprints.

=> Check ALL Flask Tutorials Here