SQLAlchemy in Flask

Many people prefer SQLAlchemy for database access. In this case it’sencouraged to use a package instead of a module for your flask applicationand drop the models into a separate module (Larger Applications).While that is not necessary, it makes a lot of sense.

There are four very common ways to use SQLAlchemy. I will outline eachof them here:

Flask-SQLAlchemy Extension

Because SQLAlchemy is a common database abstraction layer and objectrelational mapper that requires a little bit of configuration effort,there is a Flask extension that handles that for you. This is recommendedif you want to get started quickly.

You can download Flask-SQLAlchemy from PyPI.

Declarative

The declarative extension in SQLAlchemy is the most recent method of usingSQLAlchemy. It allows you to define tables and models in one go, similarto how Django works. In addition to the following text I recommend theofficial documentation on the declarative extension.

Here’s the example database.py module for your application:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
 
def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import yourapplication.models
    Base.metadata.create_all(bind=engine)

To define your models, just subclass the Base class that was created bythe code above. If you are wondering why we don’t have to care aboutthreads here (like we did in the SQLite3 example above with theg object): that’s because SQLAlchemy does that for usalready with the scoped_session.

To use SQLAlchemy in a declarative way with your application, you justhave to put the following code into your application module. Flask willautomatically remove database sessions at the end of the request orwhen the application shuts down:

from yourapplication.database import db_session
 
@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

Here is an example model (put this into models.py, e.g.):

from sqlalchemy import Column, Integer, String
from yourapplication.database import Base
 
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    email = Column(String(120), unique=True)
 
    def __init__(self, name=None, email=None):
        self.name = name
        self.email = email
 
    def __repr__(self):
        return '<User %r>' % (self.name)

To create the database you can use the init_db function:

>>> from yourapplication.database import init_db
>>> init_db()

You can insert entries into the database like this:

>>> from yourapplication.database import db_session
>>> from yourapplication.models import User
>>> u = User('admin', '[email protected]')
>>> db_session.add(u)
>>> db_session.commit()

Querying is simple as well:

>>> User.query.all()
[<User u'admin'>]
>>> User.query.filter(User.name == 'admin').first()
<User u'admin'>

Manual Object Relational Mapping

Manual object relational mapping has a few upsides and a few downsidesversus the declarative approach from above. The main difference is thatyou define tables and classes separately and map them together. It’s moreflexible but a little more to type. In general it works like thedeclarative approach, so make sure to also split up your application intomultiple modules in a package.

Here is an example database.py module for your application:

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
 
engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
metadata = MetaData()
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
def init_db():
    metadata.create_all(bind=engine)

As in the declarative approach, you need to close the session aftereach request or application context shutdown. Put this into yourapplication module:

from yourapplication.database import db_session
 
@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

Here is an example table and model (put this into models.py):

from sqlalchemy import Table, Column, Integer, String
from sqlalchemy.orm import mapper
from yourapplication.database import metadata, db_session
 
class User(object):
    query = db_session.query_property()
 
    def __init__(self, name=None, email=None):
        self.name = name
        self.email = email
 
    def __repr__(self):
        return '<User %r>' % (self.name)
 
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50), unique=True),
    Column('email', String(120), unique=True)
)
mapper(User, users)

Querying and inserting works exactly the same as in the example above.

SQL Abstraction Layer

If you just want to use the database system (and SQL) abstraction layeryou basically only need the engine:

from sqlalchemy import create_engine, MetaData, Table
 
engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True)
metadata = MetaData(bind=engine)

Then you can either declare the tables in your code like in the examplesabove, or automatically load them:

from sqlalchemy import Table
 
users = Table('users', metadata, autoload=True)

To insert data you can use the insert method. We have to get aconnection first so that we can use a transaction:

>>> con = engine.connect()
>>> con.execute(users.insert(), name='admin', email='[email protected]')

SQLAlchemy will automatically commit for us.

To query your database, you use the engine directly or use a connection:

>>> users.select(users.c.id == 1).execute().first()
(1, u'admin', u'[email protected]')

These results are also dict-like tuples:

>>> r = users.select(users.c.id == 1).execute().first()
>>> r['name']
u'admin'

You can also pass strings of SQL statements to theexecute() method:

>>> engine.execute('select * from users where id = :1', [1]).first()
(1, u'admin', u'[email protected]')

For more information about SQLAlchemy, head over to thewebsite.