Flask 101: Adding a Database

Last time we learned how to get Flask set up. In this article we will learn how to add a database to our music data website. As you might recall, Flask is a micro-web-framework. That means it doesn’t come with an Object Relational Mapper (ORM) like Django does. If you want to add database interactivity, then you need to add it yourself or install an extension. I personally like SQLAlchemy, so I thought it was nice that there is a ready-made extension for adding SQLAlchemy to Flask called Flask-SQLAlchemy.

To install Flask-SQLAlchemy, you just need to use pip. Make sure that you are in your activated virtual environment that we created in the first part of this series before you run the following or you’ll end up installing the extension to your base Python instead of your virtual environment:

pip install flask-sqlalchemy

Now that we have the Flask-SQLAlchemy installed along with its dependencies, we can get started creating a database!

Creating a Database

Creating a database with SQLAlchemy is actually pretty easy. SQLAlchemy supports a couple of different ways of working with a database. My favorite is using its declarative syntax that allows you to create classes that model the database itself. So I will use that for this example. We will be using SQLite as our backend too, however we could easily change that backend to something else, such as MySQL or Postgres if we wanted to.

To start out, we will look at how you create the database file using just normal SQLAlchemy. Then we will create a separate script that uses the slightly different Flask-SQLAlchemy syntax. Put the following code into a file called db_creator.py

# db_creator.py

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

engine = create_engine('sqlite:///mymusic.db', echo=True)
Base = declarative_base()


class Artist(Base):
    __tablename__ = "artists"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return "{}".format(self.name)


class Album(Base):
    """"""
    __tablename__ = "albums"

    id = Column(Integer, primary_key=True)
    title = Column(String)
    release_date = Column(String)
    publisher = Column(String)
    media_type = Column(String)

    artist_id = Column(Integer, ForeignKey("artists.id"))
    artist = relationship("Artist", backref=backref(
        "albums", order_by=id))

# create tables
Base.metadata.create_all(engine)

The first part of this code should look pretty familiar to anyone using Python as all we are doing here is importing the bits and pieces we need from SQLAlchemy to make the rest of the code work. Then we create SQLAlchemy’s engine object, which basically connects Python to the database of choice. In this case, we are connecting to SQLite and creating a file instead of creating the database in memory. We also create a “base class” that we can use to create declarative class definitions that actually define our database tables.

The next two classes define the tables we care about, namely Artist and Album. You will note that we name the table via the __tablename__ class attribute. We also create the table’s columns and set their data types to whatever we need. The Album class is a bit more complex since we set up a ForeignKey relationship with the Artist table. You can read more about how this works in my old SQLAlchemy tutorial or if you want the in-depth details, then check out the well written documentation.

When you run the code above, you should get something like this in your terminal:

2017-12-08 18:36:43,290 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-12-08 18:36:43,291 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,292 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-12-08 18:36:43,292 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,294 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("artists")
2017-12-08 18:36:43,294 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,295 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("albums")
2017-12-08 18:36:43,295 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,296 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE artists (
    id INTEGER NOT NULL, 
    name VARCHAR, 
    PRIMARY KEY (id)
)


2017-12-08 18:36:43,296 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,315 INFO sqlalchemy.engine.base.Engine COMMIT
2017-12-08 18:36:43,316 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE albums (
    id INTEGER NOT NULL, 
    title VARCHAR, 
    release_date DATE, 
    publisher VARCHAR, 
    media_type VARCHAR, 
    artist_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(artist_id) REFERENCES artists (id)
)


2017-12-08 18:36:43,316 INFO sqlalchemy.engine.base.Engine ()
2017-12-08 18:36:43,327 INFO sqlalchemy.engine.base.Engine COMMIT

Now let’s make all this work in Flask!

Using Flask-SQLAlchemy

The first thing we need to do when we go to use Flask-SQLAlchemy is to create a simple application script. We will call it app.py. Put the following code into this file and save it to the musicdb folder.

# app.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mymusic.db'
app.secret_key = "flask rocks!"

db = SQLAlchemy(app)

Here we create our Flask app object and tell it where the SQLAlchemy database file should live. We also set up a simple secret key and create a db object which allows us to integrate SQLAlchemy into Flask. Next we need to create a models.py file and save it into the musicdb folder. Once you have that made, add the following code to it:

# models.py 

from app import db


class Artist(db.Model):
    __tablename__ = "artists"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    def __repr__(self):
        return "".format(self.name)


class Album(db.Model):
    """"""
    __tablename__ = "albums"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    release_date = db.Column(db.String)
    publisher = db.Column(db.String)
    media_type = db.Column(db.String)

    artist_id = db.Column(db.Integer, db.ForeignKey("artists.id"))
    artist = db.relationship("Artist", backref=db.backref(
        "albums", order_by=id), lazy=True)

You will note that Flask-SQLAlchemy doesn’t require all the imports that just plain SQLAlchemy required. All we need is the db object we created in our app script. Then we just pre-pend “db” to all the classes we used in the original SQLAlchemy code. You will also note that instead of creating a Base class, it is already pre-defined as db.Model.

Finally we need to create a way to initialize the database. You could put this in several different places, but I ended up creating a file I dubbed db_setup.py and added the following contents:

# db_setup.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///mymusic.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    import models
    Base.metadata.create_all(bind=engine)

This code will initialize the database with the tables you created in your models script. To make the initialization happen, let’s edit out test.py script from the previous article:

# test.py

from app import app
from db_setup import init_db

init_db()


@app.route('/')
def test():
    return "Welcome to Flask!"

if __name__ == '__main__':
    app.run()

Here we just imported our app object and the init_db function. Then we called the init_db function immediately. To run this code, all you need to do is run the following command in your terminal from within the musicdb folder:

FLASK_APP=test.py flask run

When you run this, you won’t see the SQLAlchemy output that we saw earlier. Instead you will just see some information printed out stating that your Flask application is running. You will also find a mymusic.db file has been created in your musicdb folder.

Note that the init_db() call doesn’t always seem to work, so you may need to run the db_creator script I wrote in the previous article if your SQLite database file isn’t generated correctly.

Wrapping Up

At this point, you now have a web application with an empty database. You can’t add anything to the database with your web application or view anything in the database. Yes, you just created something really cool, but it’s also completely useless for your users. In the next article we will learn how to add a search form to search for data in our empty database! Yes, there is a method to my madness, but you’ll have to keep reading the series to figure it out.