【ORM】【SQLAlchemy】Tutorials

The most up-to-date and complete tutorials available for getting started with SQLAlchemy are the Core and ORM tutorials included with SQLAlchemy’s own documentation. There’s a few other good resources available as well:

1.1 Introduction to SQLAlchemy - presented at many Pycon and other conferences

Author: Mike Bayer

www.youtube.com/embed/woKYyhLCcnU

This is Mike Bayer’s own “getting started” tutorial that presents a fast paced but beginner-focused tutorial on what SQLAlchemy is all about. While the tutorial has evolved over the years and at this point is fairly fixed. The “Prerequisite Material” is highly recommended and includes a PDF introductory guide as well as a self-installing, runnable code demo that’s used in the tutorial.

1.2 Python SQLAlchemy Tutorial - on the Python Central website

Author: Xiaonuo Gantan

A series of beginner-focused SQLAlchemy tutorials covering a wide range of basic topics. While a lot of the information here is derived from the main documentation, the pace is slower and there are also details culled from other sources, including performance tips, comparison to other ORMs, and design philosophies. A very good effort by author Xiaonuo Gantan.

1.3 SQLAlchemy Tutorial - on the Zetcode tutorial website

Author: Jan Bodnar

This is a “nutshell” style tutorial that quickly introduces the major areas of SQLAlchemy using simple examples, covering raw SQL, schema concepts, SQL Expression language, and the ORM including querying and relationships.

SQLAlchemy Tutorial

1.4 SQLAlchemy + URL Dispatch Wiki Tutorial - Part of the Pylons Documentation

Author: Chris McDonough

This is the official tutorial for SQLAlchemy integration within the Pyramid Web Framework. The basic steps to integrate SQLAlchemy within a traditional web application are laid out here and are mostly in conformance with those guidelines referenced by SQLAlchemy’s documentation.

SQLAlchemy + URL Dispatch Wiki Tutorial

2 (Reprint) Python SQLAlchemy Tutorial

Author: Xiaonuo Gantan

2.1 Index - N/A

https://www.pythoncentral.io/series/python-sqlalchemy-database-tutorial/

2.2 Introductory Tutorial

This article is part 1 of 11 in the series Python SQLAlchemy Tutorial

Last Updated: Thursday 12th December 2013

2.2.1 Python’s SQLAlchemy and Object-Relational Mapping

A common task when programming any web service is the construction of a solid database backend. In the past, programmers would write raw SQL statements, pass them to the database engine and parse the returned results as a normal array of records. Nowadays, programmers can write Object-relational mapping (ORM) programs to remove the necessity of writing tedious and error-prone raw SQL statements that are inflexible and hard-to-maintain.

ORM is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an OO language such as Python contains types that are non-scalar, namely that those types cannot be expressed as primitive types such as integers and strings. For example, a Person object may have a list of Address objects and a list of PhoneNumber objects associated with it. In turn, an Address object may have a PostCode object, a StreetName object and a StreetNumber object associated with it. Although simple objects such as PostCodes and StreetNames can be expressed as strings, a complex object such as a Address and a Person cannot be expressed using only strings or integers. In addition, these complex objects may also include instance or class methods that cannot be expressed using a type at all.

In order to deal with the complexity of managing objects, people developed a new class of systems called ORM. Our previous example can be expressed as an ORM system with a Person class, a Address class and a PhoneNumber class, where each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system.

2.2.2 The Old Way of Writing Database Code in Python

We’re going to use the library sqlite3 to create a simple database with two tables Person and Address in the following design:
【ORM】【SQLAlchemy】Tutorials

Note: If you want to checkout how to use SQLite for Python, you might want to have a look at the SQLite in Python series.

In this design, we have two tables person and address and address.person_id is a foreign key to the person table. Now we write the corresponding database initialization code in a file sqlite_ex.py.

import sqlite3
conn = sqlite3.connect('example.db')
 
c = conn.cursor()
c.execute('''
          CREATE TABLE person
          (id INTEGER PRIMARY KEY ASC, name varchar(250) NOT NULL)
          ''')
c.execute('''
          CREATE TABLE address
          (id INTEGER PRIMARY KEY ASC, street_name varchar(250), street_number varchar(250),
           post_code varchar(250) NOT NULL, person_id INTEGER NOT NULL,
           FOREIGN KEY(person_id) REFERENCES person(id))
          ''')
 
c.execute('''
          INSERT INTO person VALUES(1, 'pythoncentral')
          ''')
c.execute('''
          INSERT INTO address VALUES(1, 'python road', '1', '00000', 1)
          ''')
 
conn.commit()
conn.close()

Notice that we have inserted one record into each table. Run the following command in your shell.

$ python sqlite_ex.py

Now we can query the database example.db to fetch the records. Write the following code in a file sqlite_q.py.

import sqlite3
conn = sqlite3.connect('example.db')
 
c = conn.cursor()
c.execute('SELECT * FROM person')
print c.fetchall()
c.execute('SELECT * FROM address')
print c.fetchall()
conn.close()

And run the following statement in your shell.

$ python sqlite_q.py
[(1, u'pythoncentral')]
[(1, u'python road', u'1', u'00000', 1)]

In the previous example, we used an sqlite3 connection to commit the changes to the database and a sqlite3 cursor to execute raw SQL statements to CRUD (create, read, update and delete) data in the database. Although the raw SQL certainly gets the job done, it is not easy to maintain these statements. In the next section, we’re going to use SQLAlchemy’s declarative to map the Person and Address tables into Python classes.

2.2.3 Python’s SQLAlchemy and Declarative

There are three most important components in writing SQLAlchemy code:

A Table that represents a table in a database.
A mapper that maps a Python class to a table in a database.
A class object that defines how a database record maps to a normal Python object.

Instead of having to write code for Table, mapper and the class object at different places, SQLAlchemy’s declarative allows a Table, a mapper and a class object to be defined at once in one class definition.

The following declarative definitions specify the same tables defined in sqlite_ex.py:

import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
 
Base = declarative_base()
 
class Person(Base):
    __tablename__ = 'person'
    # Here we define columns for the table person
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
 
class Address(Base):
    __tablename__ = 'address'
    # Here we define columns for the table address.
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    street_name = Column(String(250))
    street_number = Column(String(250))
    post_code = Column(String(250), nullable=False)
    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship(Person)
 
# Create an engine that stores data in the local directory's
# sqlalchemy_example.db file.
engine = create_engine('sqlite:///sqlalchemy_example.db')
 
# Create all tables in the engine. This is equivalent to "Create Table"
# statements in raw SQL.
Base.metadata.create_all(engine)

Save the previous code into a file sqlalchemy_declarative.py and run the following command in your shell:

$ python sqlalchemy_declarative.py

Now a new sqlite3 db file called “sqlalchemy_example.db” should be created in your current directory. Since the sqlalchemy db is empty right now, let’s write some code to insert records into the database:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
 
from sqlalchemy_declarative import Address, Base, Person
 
engine = create_engine('sqlite:///sqlalchemy_example.db')
# Bind the engine to the metadata of the Base class so that the
# declaratives can be accessed through a DBSession instance
Base.metadata.bind = engine
 
DBSession = sessionmaker(bind=engine)
# A DBSession() instance establishes all conversations with the database
# and represents a "staging zone" for all the objects loaded into the
# database session object. Any change made against the objects in the
# session won't be persisted into the database until you call
# session.commit(). If you're not happy about the changes, you can
# revert all of them back to the last commit by calling
# session.rollback()
session = DBSession()
 
# Insert a Person in the person table
new_person = Person(name='new person')
session.add(new_person)
session.commit()
 
# Insert an Address in the address table
new_address = Address(post_code='00000', person=new_person)
session.add(new_address)
session.commit()

Save the previous code into a local file sqlalchemy_insert.py and run the command python sqlalchemy_insert.py in your shell. Now we have one Person object and one Address object stored in the database. Let’s query the database using the classes defined in sqlalchemy_declarative.py:

>>> from sqlalchemy_declarative import Person, Base, Address
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///sqlalchemy_example.db')
>>> Base.metadata.bind = engine
>>> from sqlalchemy.orm import sessionmaker
>>> DBSession = sessionmaker()
>>> DBSession.bind = engine
>>> session = DBSession()
>>> # Make a query to find all Persons in the database
>>> session.query(Person).all()
[<sqlalchemy_declarative.Person object at 0x2ee3a10>]
>>>
>>> # Return the first Person from all Persons in the database
>>> person = session.query(Person).first()
>>> person.name
u'new person'
>>>
>>> # Find all Address whose person field is pointing to the person object
>>> session.query(Address).filter(Address.person == person).all()
[<sqlalchemy_declarative.Address object at 0x2ee3cd0>]
>>>
>>> # Retrieve one Address whose person field is point to the person object
>>> session.query(Address).filter(Address.person == person).one()
<sqlalchemy_declarative.Address object at 0x2ee3cd0>
>>> address = session.query(Address).filter(Address.person == person).one()
>>> address.post_code
u'00000'

2.2.4 Summary of Python’s SQLAlchemy

In this article, we learned how to write database code using SQLAlchemy’s declaratives. Compared to writing the traditional raw SQL statements using sqlite3, SQLAlchemy’s code is more object-oriented and easier to read and maintain. In addition, we can easily create, read, update and delete SQLAlchemy objects like they’re normal Python objects.

You might be wondering that if SQLAlchemy’s just a thin layer of abstraction above the raw SQL statements, then it’s not very impressive and you might prefer to writing raw SQL statements instead. In the following articles of this series, we’re going to investigate various aspects of SQLAlchemy and compare it against raw SQL statements when they’re both used to implement the same functionalities. I believe at the end of this series, you will be convinced that SQLAlchemy is superior to writing raw SQL statements.

2.3 How to Install SQLAlchemy

$ pip install sqlalchemy

2.4 Comparison to other ORMs - N/A

2.5 Overview of Expression Language and ORM Queries

This article is part 4 of 11 in the series Python SQLAlchemy Tutorial

Last Updated: Wednesday 12th March 2014

2.5.1 Overview

In the previous article, we made a comparison between SQLAlchemy and other Python ORMs. In this article, we are going to take a deeper look at SQLAlchemy’s ORM and Expression Language and use an example to showcase their empowering API and easy-to-understand Python structures.

Not only does the SQLAlchemy ORM provide a way to map database concepts into the Python space, it also provides a convenient Pythonic querying API. To find something in an SQLAlchemy database using ORM is pleasant, since everything is straightforward and the query results are returned as Python objects, as well as the query parameters.

The SQLAlchemy Expression Language provides a system for the programmers to write “SQL statements” using Python constructs. These constructs are modeled to resemble those of the underlying database as closely as possible, while hiding the difference between various database backends from the user. Although these constructs aim to represent equivalent concepts between backends with consistent structures, they do not conceal useful backend-specific features. Therefore, the Expression Language provides a way for the programmers to write backend-neutral expressions, while allowing the programmers to take advantage of specific backend features if they really want to.

The Expression Language complements the Object Relational Mapper. Whereas the ORM presents an abstracted pattern of usage mapping database concepts into Python’s space, where models are used to map tables and relationships are used to map many-to-many through an association table and one-to-one through a foreign key, the Expression Language is used to directly represent more primitive constructs in a database without opinion.

2.5.2 An example with departments and employees

Let’s use an example to illustrate how to use the expression language in a database with two tables department and employee. A department has many employees while an employee belongs to at most one department. Therefore, the database could be designed as follows:

>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> from sqlalchemy.orm import relationship, backref
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>>
>>> Base = declarative_base()
>>>
>>>
>>> class Department(Base):
...     __tablename__ = 'department'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...
>>>
>>> class Employee(Base):
...     __tablename__ = 'employee'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     department_id = Column(Integer, ForeignKey('department.id'))
...     department = relationship(Department, backref=backref('employees', uselist=True))
...
>>>
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)

In this example, we created a in-memory sqlite database with two tables ‘department’ and ‘employee’. The column ‘employee.department_id’ is a foreign key to the column ‘department.id’ and the relationship ‘department.employees’ include all the employees in that department. To test our setup, we can simply insert several example records and query them using SQLAlchemy’s ORM:

>>> john = Employee(name='john')
>>> it_department = Department(name='IT')
>>> john.department = it_department
>>> s = session()
>>> s.add(john)
>>> s.add(it_department)
>>> s.commit()
>>> it = s.query(Department).filter(Department.name == 'IT').one()
>>> it.employees
[]
>>> it.employees[0].name
u'john'

As you can see, we inserted one employee, john, into the IT department.

Now let’s perform the same kind of query using the expression language:

>>> from sqlalchemy import select
>>> find_it = select([Department.id]).where(Department.name == 'IT')
>>> rs = s.execute(find_it)
>>> rs
 
>>> rs.fetchone()
(1,)
>>> rs.fetchone()  # Only one result is returned from the query, so getting one more returns None.
>>> rs.fetchone()  # Since the previous fetchone() returned None, fetching more would lead to a result-closed exception
Traceback (most recent call last):
  File "", line 1, in 
  File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 790, in fetchone
    self.cursor, self.context)
  File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1027, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 781, in fetchone
    row = self._fetchone_impl()
  File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 700, in _fetchone_impl
    self._non_result()
  File "/Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 724, in _non_result
    raise exc.ResourceClosedError("This result object is closed.")
sqlalchemy.exc.ResourceClosedError: This result object is closed.
>>> find_john = select([Employee.id]).where(Employee.department_id == 1)
>>> rs = s.execute(find_john)
 
>>> rs.fetchone()  # Employee John's ID
(1,)
>>> rs.fetchone()

Since the Expression Language provides lower-level Python structures that mimic a backend-neutral SQL, it feels almost identical to writing actual SQL but in a Pythonic way.

2.5.3 Many-to-many between the departments and the employees

In our previous example, it’s simple that one employee belongs to at most one department. What if an employee could belong to multiple departments? Isn’t one foreign key not enough to represent this kind of relationship?

Yes, one foreign key is not enough. To model a many-to-many relationship between department and employee, we create a new association table with two foreign keys, one to ‘department.id’ and another to ‘employee.id’.

>>> from sqlalchemy import Column, String, Integer, ForeignKey
>>> from sqlalchemy.orm import relationship, backref
>>> from sqlalchemy.ext.declarative import declarative_base
>>>
>>>
>>> Base = declarative_base()
>>>
>>>
>>> class Department(Base):
...     __tablename__ = 'department'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     employees = relationship('Employee', secondary='department_employee')
...
>>>
>>> class Employee(Base):
...     __tablename__ = 'employee'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     departments = relationship('Department', secondary='department_employee')
...
>>>
>>> class DepartmentEmployee(Base):
...     __tablename__ = 'department_employee'
...     department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
...     employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
...
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>> s = session()
>>> john = Employee(name='john')
>>> s.add(john)
>>> it_department = Department(name='IT')
>>> it_department.employees.append(john)
>>> s.add(it_department)
>>> s.commit()

In the previous example, we created an association table with two foreign keys. This association table ‘department_employee’ links ‘department’ and ‘employee’ and the relationships Department.employees and Employee.departments are to-many mapping between the tables. Notice the “magic-trick” to make this happen is the argument “secondary” we passed into the relationship() function in the Department and Employee model classes.

We can test our setup using the following queries:

>>> john = s.query(Employee).filter(Employee.name == 'john').one()
>>> john.departments
[]
>>> john.departments[0].name
u'IT'
>>> it = s.query(Department).filter(Department.name == 'IT').one()
>>> it.employees
[]
>>> it.employees[0].name
u'john'

Now let’s insert one more employee and another department into the database:

>>> marry = Employee(name='marry')
>>> financial_department = Department(name='financial')
>>> financial_department.employees.append(marry)
>>> s.add(marry)
>>> s.add(financial_department)
>>> s.commit()

To find all the employees in the IT department, we can write it in ORM:

>>> s.query(Employee).filter(Employee.departments.any(Department.name == 'IT')).one().name
u'john'

Or the Expression Language:

>>> find_employees = select([DepartmentEmployee.employee_id]).select_from(Department.__table__.join(DepartmentEmployee)).where(Department.name == 'IT')
>>> rs = s.execute(find_employees)
>>> rs.fetchone()
(1,)
>>> rs.fetchone()

Now let’s assign employee marry into the IT department so that she will be part of two departments

>>> s.refresh(marry)
>>> s.refresh(it)
>>> it.employees
[]
>>> it.employees.append(marry)
>>> s.commit()
>>> it.employees
[, ]

To find marry, i.e., all the employees who belong to at least two departments, we use group_by and having in an ORM query:

>>> from sqlalchemy import func
>>> s.query(Employee).join(Employee.departments).group_by(Employee.id).having(func.count(Department.id) > 1).one().name

Similar to the ORM query, we can also use group_by and having in a Expression Language query:

>>> find_marry = select([Employee.id]).select_from(Employee.__table__.join(DepartmentEmployee)).group_by(Employee.id).having(func.count(DepartmentEmployee.department_id) > 1)
>>> s.execute(find_marry)
 
>>> rs = _
>>> rs.fetchall()
[(2,)]

Of course, always remember to close the database session when you’re done.

>>> s.close()

2.5.4 Summary and Tips

In this article, we used an example database with two main tables and one association table to demonstrate how to write queries in SQLAlchemy’s ORM and Expression Language. As a carefully designed API, writing queries is as easy as writing normal Python code. Since the Expression Language provides a lower-level API than the ORM, writing a query in the Expression Language feels more like writing one in a DBAPI such as psycopg2 and Python-MySQL. However, the lower-level API provided the Expression Language is more flexible than the ORM and its queries can be mapped into selectableSQL views in Python, which is very helpful as our queries become more and more complex. In the future articles, we are going to further explore how to utilize the Expression Language to make writing complex queries a joy instead of a pain.

2.6 Commonly Asked Questions

This article is part 5 of 11 in the series Python SQLAlchemy Tutorial

2018/Oct/12 14:45

2.6.1 Common Questions

Before we dive deeper into SQLAlchemy, let’s answer a possible list of questions regarding the ORM:

Can you prevent SQLAlchemy from automatically creating a schema? Instead, can you bind SQLAlchemy models to an existing schema?
Is there a performance overhead when using SQLAlchemy, compared to writing raw SQL? If so, how much?
If you don’t have enough permission to create tables in a database, does SQLAlchemy throw an exception?
How is schema modified? Is it done automatically or do you write code to do it?
Is there a support for triggers?

In this article, we are going to answer all the questions. Some of the questions will be covered in detail while others will be answered in summary and covered in another article.

2.6.1 SQLAlchemy Schema Reflection / Introspection

Instead of creating a schema automatically from the SQLAlchemy, as what’s shown in the previous articles using Base.metadata.create_all(engine), we can also instruct a Table object to load information about itself from the corresponding database schema object already existing within the database.

Let’s create an example sqlite3 database with one table person that stores one record:

import sqlite3
 
conn = sqlite3.connect("example.db")
c = conn.cursor()
c.execute('''
          CREATE TABLE person
          (name text, email text)
          ''')
c.execute("INSERT INTO person VALUES ('john', '[email protected]')")
c.close()

Now we can reflect the structure of table person using the arguments autoload and autoload_with in the Table constructor.

>>> from sqlalchemy import create_engine, MetaData, Table
>>>
>>> engine = create_engine('sqlite:///example.db')
>>> meta = MetaData(bind=engine)
>>> person = Table("person", meta, autoload=True, autoload_with=engine)
>>> person
Table('person', MetaData(bind=Engine(sqlite:///example.db)), Column(u'name', TEXT(), table=), Column(u'email', TEXT(), table=), schema=None)
>>> [c.name for c in person.columns]
[u'name', u'email']

We can also reflect all tables in the database using the MetaData.reflect method.

>>> meta = MetaData()
>>> meta.reflect(bind=engine)
>>> person = meta.tables['person']
>>> person
Table(u'person', MetaData(bind=None), Column(u'name', TEXT(), table=), Column(u'email', TEXT(), table=), schema=None)

Albeit very powerful, reflection does have its limitations. It’s important to remember reflection constructs Table metadata using only information available in the relational database. Naturally, such a process cannot restore aspects of a schema that are not actually stored in the database. The aspects that are not available include but not limited to:

Client side defaults, Python functions or SQL expressions defined using the default keyword of the Columnconstructor.
Column information, defined in the Column.info dictionary.
The value of the .quote setting for Column or Table.
The association of a particular Sequence with a given Column.

Recent improvements in SQLAlchemy allow structures like views, indexes and foreign key options to be reflected. Structures like CHECK constraints, table comments and triggers are not reflected.

2.6.2 Performance Overhead of SQLAlchemy

Since SQLAlchemy uses the unit of work pattern when synchronizing changes, i.e., session.commit(), to the database, it does more than just “inserts” data as in a raw SQL statement. It tracks changes made to a session’s object and maintain an identity map for all the objects. It also performs a fair bit amount of bookkeeping and maintains the integrity of any CRUD operations. Overall, unit of work automates the task of persisting a complex object graph into a relational database without writing explicit procedural persistence code. Of course, such an advanced automation has a price.

Since SQLAlchemy’s ORM is not designed to deal with bulk insertions, we can write an example to test its efficiency against raw SQL. Besides the ORM and raw SQL implementation of a bulk insertion test case, we also implement a version that uses SQLAlchemy’s Core system. Since SQLAlchemy’s Core is a thin layer of abstraction above the raw SQL, we expect it to achieve comparable level of performance to raw SQL.

import time
import sqlite3
 
 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
 
 
Base = declarative_base()
session = scoped_session(sessionmaker())
 
 
class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
 
 
def init_db(dbname='sqlite:///example.db'):
    engine = create_engine(dbname, echo=False)
    session.remove()
    session.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    return engine
 
 
def test_sqlalchemy_orm(number_of_records=100000):
    init_db()
    start = time.time()
    for i in range(number_of_records):
        user = User()
        user.name = 'NAME ' + str(i)
        session.add(user)
    session.commit()
    end = time.time()
    print "SQLAlchemy ORM: Insert {0} records in {1} seconds".format(
        str(number_of_records), str(end - start)
    )
 
 
def test_sqlalchemy_core(number_of_records=100000):
    engine = init_db()
    start = time.time()
    engine.execute(
        User.__table__.insert(),
        [{"name": "NAME " + str(i)} for i in range(number_of_records)]
    )
    end = time.time()
    print "SQLAlchemy Core: Insert {0} records in {1} seconds".format(
        str(number_of_records), str(end - start)
    )
 
def init_sqlite3(dbname="sqlite3.db"):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS user")
    cursor.execute("CREATE TABLE user (id INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY(id))")
    conn.commit()
    return conn
 
def test_sqlite3(number_of_records=100000):
    conn = init_sqlite3()
    cursor = conn.cursor()
    start = time.time()
    for i in range(number_of_records):
        cursor.execute("INSERT INTO user (name) VALUES (?)", ("NAME " + str(i),))
    conn.commit()
    end = time.time()
    print "sqlite3: Insert {0} records in {1} seconds".format(
        str(number_of_records), str(end - start)
    )
 
 
if __name__ == "__main__":
    test_sqlite3()
    test_sqlalchemy_core()
    test_sqlalchemy_orm()

In the previous code, we compare the performance of bulk inserting 100000 user records into a sqlite3 database using raw SQL, SQLAlchemy’s Core and SQLAlchemy’s ORM. If you run the code, you will get an output similar to the following:

$ python orm_performance_overhead.py
sqlite3: Insert 100000 records in 0.226176977158 seconds
SQLAlchemy Core: Insert 100000 records in 0.371157169342 seconds
SQLAlchemy ORM: Insert 100000 records in 10.1760079861 seconds

Notice that the Core and raw SQL achieved comparable insertion speed while the ORM is much slower than the other two. Although it looks like the ORM incurs a large performance overhead, keep in mind that the overhead becomes significant only when there is a large amount of data to be inserted. Since most web applications run small CRUD operations in one request-response cycle, it’s preferred to using the ORM instead of the Core due to the extra convenience and better maintainability.

2.6.3 SQLAlchemy and database permissions

So far, our examples have been working well with sqlite3 databases, which do not have fine-grained access control such as user and permission management. What if we want to use SQLAlchemy with MySQL or PostgreSQL? What happens when the user connected to the database does not have enough permission to create tables, indexes, etc.? Will SQLAlchemy throw a database access exception?

Let’s use an example to test the behaviour of SQLAlchemy’s ORM when there is not enough permissions given to a user. First, we create a testing database “test_sqlalchemy” and a testing user “sqlalchemy”.

$ psql
postgres=# create database test_sqlalchemy;
CREATE DATABASE
postgres=# create user sqlalchemy with password 'sqlalchemy';
CREATE ROLE
postgres=# grant all on database test_sqlalchemy to sqlalchemy;
GRANT

For now, the testing user “sqlalchemy” has all access privileges towards the testing database “test_sqlalchemy”. Therefore, we expect the database initialization call to succeed and insert one record into the database “test_sqlalchemy”.

import time
import sqlite3
 
 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
 
 
Base = declarative_base()
session = scoped_session(sessionmaker())
 
 
class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
 
 
def init_db(dbname):
    engine = create_engine(dbname, echo=False)
    session.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.create_all(engine)
    return engine
 
 
if __name__ == "__main__":
    init_db("postgresql://sqlalchemy:[email protected]/test_sqlalchemy")
    u = User(name="other_user")
    session.add(u)
    session.commit()
    session.close()

After executing the script, you can check that there is a new User record in the “user” table.

$ psql test_sqlalchemy
psql (9.3.3)
Type "help" for help.
 
test_sqlalchemy=# select * from "user";
 id |    name
----+------------
  1 | other_user

Now suppose we take away the insertion permission from the testing user “sqlalchemy”. Then we should expect that running the same code will fail with an exception.

# inside a psql shell
test_sqlalchemy=# revoke INSERT on "user" from sqlalchemy;
REVOKE
 
# inside a bash shell
$ python permission_example.py
Traceback (most recent call last):
  File "permission_example.py", line 32, in 
    session.commit()
  File "/home/vagrant/pythoncentral/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/vagrant/pythoncentral/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 765, in commit
    self.transaction.commit()
......
  File "/home/vagrant/pythoncentral/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) permission denied for relation user
 'INSERT INTO "user" (name) VALUES (%(name)s) RETURNING "user".id' {'name': 'other_user'}

As you can see, an exception was thrown indicating that we do not have the permission to insert records into the relation user.

2.6.4 SQLAlchemy’s Schema Migration

There are at least two libraries available for performing SQLAlchemy migrations: migrate documentation link and alembicdocumentation link.

Since alembic was written by the author of SQLAlchemy and actively developed, we recommend you to use it instead of migrate. Not only does alembic allow you to manually write migration scripts, it also provides a way to auto-generate the scripts. We will further explore how to use alembic in another article.

2.6.5 SQLAlchemy’s Support for Triggers

SQL triggers can be created using custom DDL constructs and hooked to SQLAlchemy’s events. Although it’s not a direct support for triggers, it’s easy to implement and plug into any system. We will take a look at custom DDL and events in another article.

2.6.7 Tips and Summary

In this article, we answered a couple common questions regarding SQLAlchemy from a SQL database admin’s point of view. Although SQLAlchemy defaults to create a database schema for you, it also allows you to reflect on an existing schema and generates Table objects for you. There’s a performance overhead when using SQLAlchemy’s ORM, but it’s mostly obvious when performing bulk insertions, while most web applications perform relatively small CRUD operations. If your database user does not have enough permissions to perform certain actions on a table, SQLAlchemy will throw an exception that shows exactly why you cannot perform the actions. There are two migration libraries for SQLAlchemy and alembic is highly recommended. Although triggers are not directly supported, you can easily write them in raw SQL and hook them up using custom DDL and SQLAlchemy events.

2.7 ORM Examples

This article is part 6 of 11 in the series Python SQLAlchemy Tutorial

2018/Oct/12 14:45

2.7.1 ORM Recap

In one of the previous articles, we briefly went through an example database with two tables department and employeewhere one department can have multiple employees and one employee can belong to arbitrary number of departments. We used several code snippets to demonstrate the power of SQLAlchemy’s expression language and show how to write ORM queries.

In this article, we are going to take a look at SQLAlchemy’s ORM in more detail and find out how we can use it more effectively to solve real-world problems.

2.7.2 Department and Employee

We are going to keep using the previous article’s department-employee as the example database in this article. We are also going to add more columns to each table to make our example more interesting to play with.

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Use default=func.now() to set the default hiring time
    # of an Employee to be the current time when an
    # Employee record was created
    hired_on = Column(DateTime, default=func.now())
    department_id = Column(Integer, ForeignKey('department.id'))
    # Use cascade='delete,all' to propagate the deletion of a Department onto its Employees
    department = relationship(
        Department,
        backref=backref('employees',
                         uselist=True,
                         cascade='delete,all'))
 
 
from sqlalchemy import create_engine
engine = create_engine('sqlite:///orm_in_detail.sqlite')
 
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

Notice we made two changes to the employee table: 1. we inserted a new column ‘hired_on’ which is a DateTime column that stores when the employee was hired and, 2. we inserted a keyword argument ‘cascade’ with a value ‘delete,all’ to the backref of the relationship Employee.department. The cascade allows SQLAlchemy to automatically delete a department’s employees when the department itself is deleted.

Now let’s write a couple lines of code to play with our new table definitions.

>>> d = Department(name="IT")
>>> emp1 = Employee(name="John", department=d)
>>> s = session()
>>> s.add(d)
>>> s.add(emp1)
>>> s.commit() 
>>> s.delete(d)  # Deleting the department also deletes all of its employees.
>>> s.commit()
>>> s.query(Employee).all()
[]

Let’s create another employee to test our new DateTime column ‘hired_on’:

>>> emp2 = Employee(name="Marry")                                                                                                                   
>>> emp2.hired_on
>>> s.add(emp2)
>>> emp2.hired_on
>>> s.commit()
>>> emp2.hired_on
datetime.datetime(2014, 3, 24, 2, 3, 46)

Did you notice something odd about this short snippet? Since Employee.hired_on is defined to have a default value of func.now(), how come emp2.hired_on is None after it has been created?

The answer lies in how func.now() was handled by SQLAlchemy. func generates SQL function expressions. func.now()literally translates into now() in SQL:

>>> print func.now()
now()
>>> from sqlalchemy import select
>>> rs = s.execute(select([func.now()]))
>>> rs.fetchone()
(datetime.datetime(2014, 3, 24, 2, 9, 12),)

As you see, executing the func.now() function through the SQLAlchemy database session object gives us the current datetime based on our machine’s time zone.

Before proceeding further, let’s delete all the records in the department table and the employee table so that we can start later from a clean database.

>>> for department in s.query(Department).all():
...     s.delete(department)
...
>>> s.commit()
>>> s.query(Department).count()
0
>>> s.query(Employee).count()
0

2.7.3 More ORM Queries

Let’s keep writing queries to become more familiar with the ORM API. First, we insert several employees into two departments “IT” and “Financial”.

IT = Department(name="IT")
Financial = Department(name="Financial")
john = Employee(name="John", department=IT)
marry = Employee(name="marry", department=Financial)
s.add(IT)
s.add(Financial)
s.add(john)
s.add(marry)
s.commit()
cathy = Employee(name="Cathy", department=Financial)
s.add(cathy)
s.commit()

Suppose we want to find all the employees whose name starts with “C”, we can use startswith() to achieve our goal:

>>>s.query(Employee).filter(Employee.name.startswith("C")).one().name                                                                              
u'Cathy'

Making the query harder, suppose we want to find all the employees whose name starts with “C” and who also work for the Financial department, we can use a join query:

>>> s.query(Employee).join(Employee.department).filter(Employee.name.startswith('C'), Department.name == 'Financial').all()[0].name
u'Cathy'

What if we want to search for employees who are hired before a certain datetime? We can use a normal datetime comparison operator in the filter clause.

>>> from datetime import datetime
# Find all employees who will be hired in the future
>>> s.query(Employee).filter(Employee.hired_on > func.now()).count()
0
# Find all employees who have been hired in the past
>>> s.query(Employee).filter(Employee.hired_on < func.now()).count()
3

2.7.4 Many-to-Many between Department and Employee

So far, a Department can have multiple Employees and one Employee belongs to at most one Department. Therefore, there’s a one-to-many relationship between Department and Employee. What if an Employee can belong to an arbitrary number of Departments? How do we handle many-to-many relationship?

In order to handle a many-to-many relationship between Department and Employee, we are going to create a new association table “department_employee_link” with foreign key columns to both Department and Employee. We also need to remove the backref definition from Department since we are going to insert a to-many relationship in Employee.

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(
        DateTime,
        default=func.now())
        departments = relationship(
        Department,
        secondary='department_employee_link'
    )
 
 
class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)

Notice that all the columns in DepartmentEmployeeLink, ‘department_id’ and ‘employee_id’, are combined together to form the primary key for the table department_employee_link and the relationship arguments in class Department and class Employee have an additional keyword argument “secondary” which points to the association table.

Once we have defined our models, we can use them in the following way:

>>> fp = 'orm_in_detail.sqlite'
>>> # Remove the existing orm_in_detail.sqlite file
>>> if os.path.exists(fp):
...     os.remove(fp)
...
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///orm_in_detail.sqlite')
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>> s = session()
>>> IT = Department(name="IT")
>>> Financial = Department(name="Financial")
>>> cathy = Employee(name="Cathy")
>>> marry = Employee(name="Marry")
>>> john = Employee(name="John")
>>> cathy.departments.append(Financial)
>>> Financial.employees.append(marry)
>>> john.departments.append(IT)
>>> s.add(IT)
>>> s.add(Financial)
>>> s.add(cathy)
>>> s.add(marry)
>>> s.add(john)
>>> s.commit()
>>> cathy.departments[0].name
u'Financial'
>>> marry.departments[0].name
u'Financial'
>>> john.departments[0].name
u'IT'
>>> IT.employees[0].name
u'John'

Notice that we use Employee.departments.append() to append one Department to the list of departments of an Employee.

To find a list of employees in the IT department no matter whether they belong to other departments or not, we can use the relationship.any() function.

>>> s.query(Employee).filter(Employee.departments.any(Department.name == 'IT')).all()[0].name
u'John'

On the other hand, to find a list of departments which have John as one of their employees, we can use the same function.

>>> s.query(Department).filter(Department.employees.any(Employee.name == 'John')).all()[0].name
u'IT'

2.7.5 Summary and Tips

In this article, we take a deeper look at SQLAlchemy’s ORM library and wrote more queries to explore the API. Notice that when you want to cascade deletion from the foreign key referred object to the referring object, you can specify cascade='all,delete' in the backref of the refering object’s foreign key definition (as what’s shown in the example relationship Employee.department).

2.8 Association Tables

This article is part 7 of 11 in the series Python SQLAlchemy Tutorial

Published: Sunday 20th April 2014

2.8.1 Association Tables

In our previous articles, we used an association table to model many-to-many relationships between tables, such as the relationship between Department and Employee. In this article, we are going to dive deeper into the association table concept and see how we can use it to further solve more complicated problems.

2.8.2 DepartmentEmployeeLink and Extra Data

In our previous article, we created the following SQLAlchemy models:

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )
 
 
class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)

Notice that the DepartmentEmployeeLink class contains two foreign key columns which are enough to model the many-to-many relationship between Department and Employee. Now let’s add one more column extra_data and two more relationships department and employee.

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )
 
 
class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    extra_data = Column(String(256))
    department = relationship(Department, backref=backref("employee_assoc"))
    employee = relationship(Employee, backref=backref("department_assoc"))

With one more extra column and two more relationships on the DepartmentEmployeeLink association model, we can store more information and be more liberal with how we want to use it. For example, suppose we have an employee John who works part-time in the IT department, we can insert the string ‘part-time’ into the column extra_data and create an DepartmentEmployeeLink object to represent this relationship.

>>> fp = 'orm_in_detail.sqlite'
>>> if os.path.exists(fp):
...     os.remove(fp)
...
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///association_tables.sqlite')
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker()
>>> session.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>>
>>> IT = Department(name="IT")
>>> John = Employee(name="John")
>>> John_working_part_time_at_IT = DepartmentEmployeeLink(department=IT, employee=John, extra_data='part-time')
>>> s = session()
>>> s.add(John_working_part_time_at_IT)
>>> s.commit()

Then, we can find John by querying the IT department or the DepartmentEmployeeLink model.

>>> IT.employees[0].name
u'John'
>>> de_link = s.query(DepartmentEmployeeLink).join(Department).filter(Department.name == 'IT').one()
>>> de_link.employee.name
u'John'
>>> de_link = s.query(DepartmentEmployeeLink).filter(DepartmentEmployeeLink.extra_data == 'part-time').one()
>>> de_link.employee.name
u'John'

Finally, adding an IT employee using the relationship Department.employees still works, as shown in the previous article:

>>> Bill = Employee(name="Bill")
>>> IT.employees.append(Bill)
>>> s.add(Bill)
>>> s.commit()

2.8.3 Linking Relationships with Backref

One common keyword argument we have used so far in relationship definitions is backref. A backref is a common shortcut to place a second relationship() onto the destination table. For example, the following code puts a second relationship() “posts” onto the user table by specifying a backref on Post.owner:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
 
 
class Post(Base):
    __tablename__ = 'post'
    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey('user.id'))
    owner = relationship(User, backref=backref('posts', uselist=True))

This is equivalent to the following definition:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    posts = relationship("Post", back_populates="owner")
 
 
class Post(Base):
    __tablename__ = 'post'
    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey('user.id'))
    owner = relationship(User, back_populates="posts")

Now we have a one-to-many relationship between User and Post. We can interact with these two models in the following way:

>>> s = session()
>>> john = User(name="John")
>>> post1 = Post(owner=john)
>>> post2 = Post(owner=john)
>>> s.add(post1)
>>> s.add(post2)
>>> s.commit()
>>> s.refresh(john)
>>> john.posts
[, ]
>>> john.posts[0].owner
 
>>> john.posts[0].owner.name
u'John'

2.8.4 One-to-One

Creating a one-to-one relationship between models is very similar to creating many-to-one relationships. By modifying the uselist argument’s value to False in a backref(), we force the database models to be mapped to each other in a one-to-one relationship.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
 
 
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address = Column(String(256))
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User', backref=backref('address', uselist=False))

Then, we can use the models in the following way:

>>> s = session()
>>> john = User(name="John")
>>> home_of_john = Address(address="1234 Park Ave", user=john)
>>> s.add(home_of_john)
>>> s.commit()
>>> s.refresh(john)
>>> john.address.address
u'1234 Park Ave'
>>> john.address.user.name
u'John'
>>> s.close()

2.8.5 Relationship Update Cascades

In a relational database, referential integrity guarantees that when the primary key of a referenced object in a one-to-manyor many-to-many relationship changes, the refering objects’ foreign keys that reference the primary key will change as well. However, for databases that do not support referential integrity, such as SQLite or MySQL with their referential integrity option turned off, changing the primary key values of a referenced object does not trigger updates of the refering objects. In this case, we can use the passive_updates flag in relationship or backref to inform the database to execute extra SELECT and UPDATE statements that will update the values of the refering objects’ foreign keys.

In the following example, we construct a one-to-many relationship between User and Address and not specifying the passive_updates flag in the relationship. The database backend is SQLite.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
 
 
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address = Column(String(256))
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(
        'User', backref=backref('addresses', uselist=True)
    )

Then, when we change the primary key value of a User object, its Address objects’ user_id foreign key values will not change. Therefore, when you want to access an address's user object again, you will get an AttributeError.

>>> s = session()
>>> john = User(name='john')
>>> home_of_john = Address(address='home', user=john)
>>> office_of_john = Address(address='office', user=john)
>>> s.add(home_of_john)
>>> s.add(office_of_john)
>>> s.commit()
>>> s.refresh(john)
>>> john.id
1
>>> john.id = john.id + 1
>>> s.commit()
>>> s.refresh(home_of_john)
>>> s.refresh(office_of_john)
>>> home_of_john.user.name
Traceback (most recent call last):
  File "", line 1, in 
AttributeError: 'NoneType' object has no attribute 'name'
>>> s.close()

If we specify the passive_updates flag in the Address model, then we can change the primary key of john and expect SQLAlchemy to issue extra SELECT and UPDATE statements to keep home_of_john.user and office_of_john.user up-to-date.

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
 
 
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address = Column(String(256))
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(
        'User', backref=backref('addresses', uselist=True, passive_updates=False)
    )

>>> s = session()
>>> john = User(name='john')
>>> home_of_john = Address(address='home', user=john)
>>> office_of_john = Address(address='office', user=john)
>>> s.add(home_of_john)
>>> s.add(office_of_john)
>>> s.commit()
>>> s.refresh(john)
>>> john.id
1
>>> john.id = john.id + 1
>>> s.commit()
>>> s.refresh(home_of_john)
>>> s.refresh(office_of_john)
>>> home_of_john.user.name
u'john'
>>> s.close()

2.8.6 Summary

In this article, we dig a little deeper into SQLAlchemy’s association tables and the backref keyword argument. It’s often crucial to understand the mechanism behind these two concepts to fully master complex join queries, as what will be shown in future articles.

2.12 Migrate SQLAlchemy Databases with Alembic

This article is part 11 of 11 in the series Python SQLAlchemy Tutorial

Published: Wednesday 5th November 2014

2.12.1 Alembic

Alembic is a lightweight database migration tool for SQLAlchemy. It is created by the author of SQLAlchemy and it has become the de-facto standard tool to perform migrations on SQLAlchemy backed databases.

2.12.2 Database Migration in SQLAlchemy

A database migration usually changes the schema of a database, such as adding a column or a constraint, adding a table or updating a table. It’s often performed using raw SQL wrapped in a transaction so that it can be rolled back if something went wrong during the migration. In this article, we are going to use a sample database to demonstrate how to write Alembic migration scripts for a SQLAlchemy database.

To migrate a SQLAlchemy database, we add an Alembic migration script for the intended migration, perform the migration, update the model definition and then start using the database under the migrated schema. These steps sound like a lot, but they are quite straightforward to do, which is illustrated in the following section.

2.12.3 Sample Database Schema

Let’s create a SQLAlchemy database with a department and a employee table.

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
 
 
db_name = 'alembic_sample.sqlite'
if os.path.exists(db_name):
    os.remove(db_name)
 
from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)
 
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

After the database alembic_sample.sqlite has been created, we realize we forgot to add a many-to-many relationship between Employee and Department.

2.12.4 Migration

Instead of changing the schema directly and then recreate the database from scratch, we choose to migrate the database using alembic. In order to do that, we install alembic, initialize an alembic environment, write a migration script to add the link table, perform the migration, and then use an updated model definition to access the database again.

$ alembic init alembic
Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic ... done
Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.pyc ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/script.py.mako ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.py ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/README ... done
Please edit configuration/connection/logging settings in '/home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini' before proceeding.
 
$ vim alembic.ini # Change the line thats starts with "sqlalchemy.url" into "sqlalchemy.url = sqlite:///alembic_sample.sqlite"
 
$ alembic current
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
Current revision for sqlite:///alembic_sample.sqlite: None
 
$ alembic revision -m "add department_employee_link"
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions/1da977fd3e6e_add_department_employee_link.py ... done
 
$ alembic upgrade head
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade None -> 1da977fd3e6e, add department_employee_link
 
$ alembic current
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
Current revision for sqlite:///alembic_sample.sqlite: None -> 1da977fd3e6e (head), add department_employee_link

The migration script is as follows:

'''
add department_employee_link
 
Revision ID: 1da977fd3e6e
Revises: None
Create Date: 2014-10-23 22:38:42.894194
 
'''
 
# revision identifiers, used by Alembic.
revision = '1da977fd3e6e'
down_revision = None
 
from alembic import op
import sqlalchemy as sa
 
 
def upgrade():
    op.create_table(
        'department_employee_link',
        sa.Column(
            'department_id', sa.Integer,
            sa.ForeignKey('department.id'), primary_key=True
        ),
        sa.Column(
            'employee_id', sa.Integer,
            sa.ForeignKey('employee.id'), primary_key=True
        )
    )
 
 
def downgrade():
    op.drop_table(
        'department_employee_link'
    )

Now that the database alembic_sample.sqlite has been upgraded, we can use an updated piece of model code to access the upgraded database.

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )
 
 
class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
 
 
db_name = 'alembic_sample.sqlite'
 
from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)
 
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.bind = engine
s = session()
IT = Department(name='IT')
Financial = Department(name='Financial')
s.add(IT)
s.add(Financial)
cathy = Employee(name='Cathy')
marry = Employee(name='Marry')
john = Employee(name='John')
s.add(cathy)
s.add(marry)
s.add(john)
cathy.departments.append(Financial)
marry.departments.append(Financial)
john.departments.append(IT)
s.commit()
s.close()

Notice that we did not delete the database alembic_sample.sqlite but instead performed a migration to add a link table instead. After the migration, the relationship Department.employees and Employee.departments are working as expected.

2.12.5 Summary

Since Alembic is a lightweight database migration tool built specifically for SQLAlchemy, it allows you to re-use the same kind of database model APIs to perform simple migrations. However, it’s not an do-it-all-for-you tool. For very database specific migration, such as adding a trigger function in PostgreSQL, a raw DDL statement is still required.

RECIPES

SQLAlchemy is a recipe-focused library; while it includes a tremendous number of features, overall the emphasis is on that of creating a composable toolkit, so that users can build their own database interaction layers that suit them best. To help with this task, there’s a huge number of “recipes”, code examples geared towards specific use cases. These are currently split out among the SQLAlchemy reference documentation and the wiki:

SQLAlchemy ORM Examples - in the SQLAlchemy documentation
Theatrum Chemicum - large list of recipes old and new on the wiki