karatejb: [Flask] ORM with Flask-SQLAlchemy and Flask-Migrate

Python Flask Flask-SQLAlchemy flask-Migrate

▌Introduction

Let’s get started on how to apply ORM framework: SQLAlchemy, to our Flask application by

	Package	Description
1	Flask-SQLAlchemy	SQLAlchemy extension for Flask
2	Flask-Migrate	An extension that handles SQLAlchemy database migrations for Flask applications using Alembic.
3.	Psycopg2	PostgreSQL database adapter for the Python

We will use PostgreSQL database for example.

▋Related articles

1. [Flask] Socket.IO integration - Flask-SocketIO

2. [Flask] Socket.IO integration - Vue-Socket.io

3. [Flask] Logging

4. [Flask] ORM with Flask-SQLAlchemy and Flask-Migrate

▌Environment

▋Python 3.6.5

▋Flask 1.0.2

▋Flask-Migrate 2.3.1

▋Flask-SQLAlchemy 2.3.2

▋psycopg2 2.7.7

▌Implement

▋Install

$ pip install psycopg2

$ pip install Flask-SQLAlchemy

$ pip install Flask-Migrate

▋SQLAlchemy on Flask

from flask import Flask

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__, template_folder='./templates')

app.config.from_object(config.TestConfig)

# Application config

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql+psycopg2://{user}:{pw}@{host}:{port}/{db}".format(

user="postgres", pw="1qaz2wsx", host="localhost", port="5432", db="postgres")
# Silence the deprecation warning

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

After the SQLAlchemy object is created, we can start implementing the POCO (Models).

class User(db.Model):

__tablename__ = "users"

id = db.Column(db.Integer, db.Sequence('users_id_seq'), primary_key=True)

name = db.Column(db.String(50), unique=False, nullable=False)

phone = db.Column(db.String(20), unique=False, nullable=True)

def __init__(self, name, phone, *args, **kwargs):

self.name = name

self.phone = phone

class Product(db.Model):

__tablename__ = "products"

id = db.Column(db.Integer, db.Sequence('products_id_seq'), primary_key=True)

title = db.Column(db.String(200), unique=False, nullable=False)

price = db.Column(db.Integer, unique=False, nullable=False, default=0)

def __init__(self, title, price):

self.title = title

self.price = price

class Order(db.Model):

__tablename__ = "orders"

id = db.Column(db.Integer, db.Sequence('orders_id_seq'), primary_key=True)

count = db.Column(db.Integer, unique=False, default=1)

user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

product_id = db.Column(db.Integer, db.ForeignKey('products.id'), nullable=False)

def __init__(self, count, user_id, product_id):

self.count = count

self.user_id = user_id

self.product_id = product_id

▋Apply Flask-Migrate

Since we have the SQLAlchemy object: db, and three models, User, Product, Order.

We can apply Flask-Migrate after them as following,

migrate = Migrate(app, db)

▋Create migrations

Lets create some models in the Flask application,

1. Initialize

$ flask db init

2. Create a migration

$ flask db migrate

The success messages may be like this,

alembic_version table will be created at this time

INFO [alembic.runtime.migration] Context impl PostgresqlImpl.

INFO [alembic.runtime.migration] Will assume transactional DDL.

INFO [alembic.autogenerate.compare] Detected added table 'products'

INFO [alembic.autogenerate.compare] Detected added table 'users'

INFO [alembic.autogenerate.compare] Detected added table 'orders'

Generating ~\Flask\venv\demo\app\migrations\versions\a3fc805f385b_.py ... done

And the “migrations” directory will be created,

3. Upgrade database

$ flask db upgrade

INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.

▋Result

▋CRUD

▋Update model

Now we will update the model to support Create/Update/Delete functions.

Take the model, User for example,

class User(db.Model):

__tablename__ = "users"

id = db.Column(db.Integer, db.Sequence('users_id_seq'), primary_key=True)

name = db.Column(db.String(50), unique=False, nullable=False)

phone = db.Column(db.String(20), unique=False, nullable=True)

def __init__(self, name, phone, *args, **kwargs):

self.name = name

self.phone = phone

def create(self):

db.session.add(self)

db.session.commit()

def delete(self):

db.session.delete(self)

db.session.commit()

def update(self):

db.session.commit()

Notice that:

def __init__(self, name, phone, *args, **kwargs):

allows us to create the User object by

entity = User(**json_obj)

▋APIs

Here are the CRUD sample APIs for Flask,

@app.route("/create-user", methods=["POST"])

def create_user():

json_obj = request.json

entity = User(**json_obj)

User.create(entity)

return "", 201

@app.route("/update-user", methods=["POST"])

def update_user():

json_obj = request.json

id = json_obj["id"]

entity = User.query.filter_by(id=id).first()

entity.name = json_obj["name"]

entity.phone = json_obj["phone"]

User.update(entity)

return "", 200

@app.route("/delete-user", methods=["DELETE"])

def delete_user():

json_obj = request.json

id = json_obj["id"]

entity = User.query.filter_by(id=id).first()

User.delete(entity)

return "", 200

▋Demo

The demo shows the actions in sequence,

Create user -> Update user -> Delete user

▋(Optional)Refactoring

Now lets refactor the codes by

1. Extract the config value from main program

2. Create a module for creating and initializing SQLAlchemy object

3. Move the models code to Models/ folder

Lets make the flask application structure like this,

▋Extract the config value from main program

Create a modules/config.py to store the values of Flask configuration.

# Flask config reference: http://flask.pocoo.org/docs/1.0/config/

# Flask-Sqlalchemy config reference: http://flask-sqlalchemy.pocoo.org/2.3/config/

POSTGRES_ENV_VARS_DEV = {

'user': 'postgres',

'pwd': '1qaz2wsx',

'host': 'localhost',

'port': '5432',

'db': 'postgres',

}

POSTGRES_ENV_VARS_PRD = {

# Set the new variables for production here

}

class BaseConfig(object):

# Flask

DEBUG = False

TESTING = False

SECRET_KEY = "12qwaszx"

JSONIFY_MIMETYPE = "application/json"

# Flask-Sqlalchemy

SQLALCHEMY_DATABASE_URI = "postgresql://%(user)s:%(pwd)s@%(host)s:%(port)s/%(db)s" % POSTGRES_ENV_VARS_DEV

SQLALCHEMY_TRACK_MODIFICATIONS = False

class DevConfig(BaseConfig):

DEBUG = True

class TestConfig(BaseConfig):

TESTING = True

class PrdConfig(BaseConfig):

# SQLALCHEMY_DATABASE_URI = "postgresql://%(user)s:%(password)s@%(host)s:%(port)s/%(db)s" % POSTGRES_ENV_VARS_PRD

pass

So that we can import the config in to Flask like this,

import modules.config as config

app = Flask(__name__, template_folder='./templates')

app.config.from_object(config.TestConfig)

▋Create a module for creating and initializing SQLAlchemy object

modules/sqlalchemy_module.py

from flask_sqlalchemy import SQLAlchemy

from flask_migrate import Migrate

db = SQLAlchemy()

def init_db(app):

db = SQLAlchemy(app)

return db

def migrate_db(app, db):

migrate = Migrate(app, db)

return migrate

▋Move the models code to Models/ folder

While we have the sqlalchemy_module, we can move the models to another directory but they must be imported after the SQLAlchemy object is created and initialized.

main.py

db = sqlalchemy_config.db = sqlalchemy_config.init_db()

from models import user, product, order

migrate = sqlalchemy_config.migrate_db(app, db)

And don’t forget to import the sqlalchemy_module into the model, for example,

from modules.sqlalchemy_module import db

class User(db.Model):

# ...skip

▋Sample code

Github

▌Reference

▋http://flask-sqlalchemy.pocoo.org