Flask-SQLAlchemy

Introduction

To get the most out of Flask, it is a good idea to connect it to a database for long-term storage of important data.
Flask works well with SQLAlchemy and Alembic in their base forms as we saw in Python.

Flask Extensions

Flask is on its own, a very minimal framework. It provides what we need to deliver Python code to the web, but that's about it.
This allows you to choose the tools that you would like to use for database management, migrations, web forms, authentication, and much more.

Managing Databases with Flask-SQLAlchemy
Create a Directory app and cd into it , then create app.py and models.py file.

Run pipenv install && pipenv shell to install Flask, Flask-SQLAlchemy, and Flask-Migrate in your virtual environment.
Enter the following in app.py.

from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'

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

Just as with our other Flask applications, we start off with an application file that creates an instance of the Flask class with the module's name. We are also taking advantage of this opportunity to start configuring our database: in core python , we would have defined this in alembic.ini. Since we're using Flask-Migrate instead of pure Alembic, we define the application configuration variables in the application itself.

Now that we have a basic application ready to run, let's configure some models.

Models with Flask-SQLAlchemy
The structure of models in Flask-SQLAlchemy is identical to that of SQLAlchemy models with one exception: rather than importing individual fields from the SQLAlchemy module, we import a SQLAlchemy class from Flask-SQLAlchemy that contains all of the same fields as attributes. This behaves similarly to declarative_base in SQLAlchemy.

let's create some models!

# app/models.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Owner(db.Model):
    __tablename__ = 'owners'

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

    pets = db.relationship('Pet', backref='owner')

    def __repr__(self):
        return f'<Pet Owner {self.name}>'

class Pet(db.Model):
    __tablename__ = 'pets'

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

    owner_id = db.Column(db.Integer, db.ForeignKey('owners.id'))

    def __repr__(self):
        return f'<Pet {self.name}, {self.species}>'

As you can see, these are very similar to the models we set up in oop python. Rather than using a Base as a parent object for each of our models, we use the db object's Model class. Inside of the models, we retrieve important classes and methods through the db object. All classes that can be imported from vanilla SQLAlchemy can be accessed through the db object.

We can manually add these data models to the database, but there aren't many valid reasons to do that in your work. Instead, we will generate our database from our models using Flask-Migrate.

Flask-Migrate

Like Flask-SQLAlchemy with vanilla SQLAlchemy, Flask-Migrate is a wrapper for Alembic with minimal changes to allow it to integrate better with Flask applications. This can become a bit confusing, especially from the command line. Don't worry though we'll discuss those aspects in detail here!

Modify app.py to mirror the following:

#!/usr/bin/env python3
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from models import db



app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'

migrate = Migrate(app, db)

db.init_app(app)

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

We have imported the Migrate class here to set up our migrations using our Flask application instance and our SQLAlchemy instance. We also initialized our application for use within our database configuration with db.init_app(app)

NOTE: db.init_app(app) is an easy step to forget! (So don't!)

We're ready to get started with our migrations. The commands for Flask-Migrate are identical to those in Alembic, with the exception of using flask db in place of alembic in commands. Run the following from the app/ directory in the command line

Let's heed that warning and set SQLALCHEMY_TRACK_MODIFICATIONS to False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

We're getting a warning to change app/migrations/alembic.ini before we continue, but our app/app.py configuration already manages all of our unique application configuration variables. We can jump straight into migrating:

$ flask db revision --autogenerate -m'Create tables owners, pets'
 # => INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
# => INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
# =>   Generating /python-p4-flask-sqlalchemy/app/migrations/versions/a48f1fc37e07_create_tables_owners_pets.py ...  done

...and pushing those migrations to our database:

 $ flask db upgrade head
# => INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
# => INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
# => INFO  [alembic.runtime.migration] Running upgrade  -> 1c84830b3fc2, Create tables owners, pets

We've created a database with Flask-SQLAlchemy and Flask-Migrate! Open app/app.db (or app/instance/app.db, depending on your version of Flask-SQLAlchemy) and you should see the fruits of your work.

Conclusion

Flask extensions give us many opportunities to add functionality to our applications while writing minimal code for configurations. We saw how to create a database with Flask-SQLAlchemy and Flask-Migrate, and some among us may have even noticed that we wrote slightly less code than we did when using SQLAlchemy by itself!.