Home Code GatorLF Part 3: Postgres and SQLAlchemy

GatorLF Part 3: Postgres and SQLAlchemy

by Sean Ziegler

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 requirements.txt file).

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!

You may also like

1 comment

GatorLF Part 2: Flask and Bootstrap setup - Sean Ziegler April 29, 2020 - 7:12 PM

[…] a RaspberryPi Cluster with Ansible Vim Command Cheatsheet Object Tracking with OpenCV GatorLF Part 3: Postgres and SQLAlchemy GatorLF Part 2: Flask and Bootstrap setup GatorLF Part 1: Setting up a GitHub […]

Reply

Leave a comment

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept