Python Web Development with Flask — SQLAlchemy ORM

Flask is a simple web framework written in Python.

In this article, we’ll look at how to develop simple Python web apps with Flask.

Using Flask with SQLAlchemy

We can use Flask with the SQLAlchemy ORM.

First, we install the sqlalchemy package by running:

pip install sqlalchemy

Next, we create the database file with the SQLite browser app.

We create a people table with the id integer primary key column and the name text column.

Then we have the file as database.db in the root of the Flask project folder.

Then, we can write to use it:

from flask import Flask, g
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.orm import mapper

engine = create_engine('sqlite:///database.db', convert_unicode=True)
metadata = MetaData(bind=engine)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

metadata.create_all(bind=engine)
app = Flask(__name__)

@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

class Person(object):
    query = db_session.query_property()

    def __init__(self, name=None, id=None):
        self.name = name
        self.id = id

    def __repr__(self):
        return '<Person %r>' % (self.name)

people = Table('people', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), unique=True),
              )
mapper(Person, people)

@app.route('/')
def index():
    person = people.select(people.c.id == 1).execute().first()
    return person.name

We connect to the database with the create_engine call.

Then we create the Metadata object so tjhat we can create models that uses the database connection.

Then we call scoped_session to create the database session.

autocommit set to false disables auto commit.

autoflush sets to false disabled auto flush.

bind set to engine binds the database session to our connection.

Then we call metadata.create_all to bind the engine to our app so we can create Table objects that uses the connection for queries.

Then we have the shutdown_sesion function that calls db_session.remove to disconnect the database when the app stops running.

Next, we create the Person class to add the query into our app.

This lets us use the class to query the database.

The __init__ method sets the instance properties with the names from the database columns.

__repr__ returns the string representation of the entry.

Then we use the Table construictor to create the people table object with the id and name columns.

They should match what we have in the database.

Then we call mapper to map the Person class to the people table.

Finally, in the index route function, we make a query in the table and then return the first result with first .

Then we return the value of the name column of the result.

To insert data, we change index to:

@app.route('/')
def index():
    con = engine.connect()
    con.execute(people.insert(), name='bob')
    person = people.select(people.c.id == 1).execute().first()
    return person.name

Everything above this function is the same as what we have before.

We connect to the database with engine.connect() .

Then we call con.execute to run the connect, people.insert() indicates we’re inserting an entry.

name indicates that we’re inserting to the name column.

Conclusion

We can use the SQLAlchemy ORM with Flask easily.