We will need some way to store the data used by our application. Let’s use Postgres because it’s free for non-commercial use and open source. I also enjoy being able to use pgAdmin for a graphical look at my database if I need it. I also like elephants.
We will use the standard
postgres database and password for now since this is just a development database. Obviously, we will have to harden this before deploying to production.
SQLAlchemy has to know where to find our database. We can pass its location with the configuration flags. We haven’t set any configuration flags yet, so we need to create a new file called config.py that we can use for future configuration options.
Inside the file, we will set up the database URI to read from an environment variable so it can’t be pushed to GitHub accidentally (especially important for API/secret keys). Okay, here’s what the file looks like:
import os class DevConfig(): DEBUG = False CSRF_ENABLED = True SECRET_KEY = os.environ['SECRET_KEY'] SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URI'] class TestConfig(): pass class ProdConfig(): pass
I set up some other configuration classes that are empty for the moment, but we will use them to switch between configuration settings on different deployment environments.
We need to set the environment variable, do that (on Windows) with:
set DATABASE_URI = "postgresql://[user:pass]@localhost/postgres
We need to pass an instance of the Flask app to SQLAlchemy as a parameter. We need to edit our
__init__.py file in the package
app to include a few extra lines relevant to SQLAlchemy.
from flast.ext.sqlalchemy import SQLAlchemy import os from config import DevConfig app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = DevConfig.SQLALCHEMY_DATABASE_URI db = SQLAlchemy(app)
The changes are simple. We import the SQLAlchemy and os packages, read in the database URI and instantiate a
db object that forms our connection to Postgres. From now on, we can use SQLAlchemy almost exclusively to interact with the database.
We have an empty database, but that isn’t very useful. Let’s define some database schema for users and lost and found items. I’ll create a file called
models.py that will hold our SQLAlchemy schema definitions.
from app import db Class User(db.Model): id = db.Column(db.Integer, primary_key = True) username = db.Column(db.String(80), unique = True, nullable = False) email = db.Column(db.String(120), unique = True, nullable = False) Class Item(db.Model): id = db.Column(db.Integer, primary_key = True) title = db.Column(db.String(120), nullable = False) location = db.Column(db.String(120), nullable = False) lostBy = db.Column(db.Integer, db.ForeignKey('User.id'), nullable = False) active = db.Column(db.Boolean, default = True, nullable = False) date = db.Column(db.DateTime, nullable = False) Class Lost(Item): pass Class Found(Item): pass
I’m not trying to write a full-fledged SQLAlchemy tutorial so suffice it to say that the
models.py file creates a table for users, items, and some sub-classes that might prove useful later on.
Before creating the initial database, I want to set up Flask-Migrate. This will automatically update our database and changes in Postgres when we change the
models.py file. Very useful. We need to add some code to
__init__.py that creates a Flask-Migrations object when the flask application starts.
from flask import Flask from flask_sqlalchemy import SQLAlchemy from flask_migrate import Migrate, MigrateCommand import os from config import DevConfig app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = DevConfig.SQLALCHEMY_DATABASE_URI db = SQLAlchemy(app) migrate = Migrate(app, db) from app import routes
We create an instance of Flask-Migrate’s migrate class and pass it instances of the Flask application and database. We can create migrations by running:
flask db <migration_name>
Let’s do that now to create an initial database.
flask db init > Creating directory C:\Users\Sean Ziegler\Coding\GatorLF\migrations … done > Creating directory C:\Users\Sean Ziegler\Coding\GatorLF\migrations\versions … done > Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\alembic.ini … done > Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\env.py … done > Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\README … done > Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\script.py.mako … done
And let’s run our first migration (I also needed to install psycopg2 and regenerate the
flask db migrate
If this works, you’ll end up seeing a bunch of terminal commands fire off and have a new migrations file in the migrations folder. Now, you’ll always have a record of the changes to your database schema!