Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Flask 101: Adding a Database

DZone's Guide to

Flask 101: Adding a Database

Learn how to add a database to a music data website using Flask, a micro-web-framework, and SQLAlchemy's ready-made extension for Flask.

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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, Date, 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 __init__(self, name):
        """"""
        self.name = name
 
    def __repr__(self):
        return "<Artist: {}>".format(self.name)
 
 
class Album(Base):
    """"""
    __tablename__ = "albums"
 
    id = Column(Integer, primary_key=True)
    title = Column(String)
    release_date = Column(Date)
    publisher = Column(String)
    media_type = Column(String)
 
    artist_id = Column(Integer, ForeignKey("artists.id"))
    artist = relationship("Artist", backref=backref(
        "albums", order_by=id))
 
    def __init__(self, title, release_date, publisher, media_type):
        """"""
        self.title = title
        self.release_date = release_date
        self.publisher = publisher
        self.media_type = media_type
 
 
# 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 database object that 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 __init__(self, name):
        """"""
        self.name = name
 
    def __repr__(self):
        return "<Artist: {}>".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.Date)
    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)
 
    def __init__(self, title, release_date, publisher, media_type):
        """"""
        self.title = title
        self.release_date = release_date
        self.publisher = publisher
        self.media_type = media_type

You will note that Flask-SQLAlchemy doesn't require all the imports that just plain SQLAlchemy required. All we need is the database object that 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 that a mymusic.db file has been created in your musicdb folder.

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 forms to add information to our database and we will learn how to display our data, too!

Download Code

Download a tarball of the code from this article.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
database ,tutorial ,flask ,sqlalchemy ,orm ,micro web framework

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}