SQLAlchemy Imperative Relationships

SQLAlchemy is a well-known library for storing objects in a relational database. SQLAlchemy supports two modes, of which one (Imperative mode) is preferable but poorly documented, particularly for creating relationships between objects. This post discusses various types of relationships in SQLAlchemy Imperative mode and presents annotated code examples.

Introduction

SQLAlchemy is a robust object-relational mapping (ORM) package that enables objects and object graphs to be stored in, retrieved from, and queried by a relational database. SQLAlchemy can operate in two modes: a Declarative mode, in which objects are derived from a SQLAlchemy base class and use SQLAlchemy-specific field data types; and an Imperative mode in which the objects are ordinary Python objects including ordinary Python field data types.

Based on that distinction, it may be preferable to use the Imperative mode, as the code is cleaner and more familiar. However, both the SQLAlchemy official documentation and the available code examples are heavily geared toward the Declarative mode. In particular, examples of various SQLAlchemy table relationship types in the Imperative mode – including one-to-one, one-to-many, many-to-one, many-to-many, and association-table relationships – are practically nonexistent.

I put some trial-and-error effort into translating the Declarative-oriented documentation into Imperative-model examples of the various relationships. My results are as follows.

Note – this post is lengthy because it is intended to be a complete reference, with inline examples that can be directly compared.

SQLAlchemy Basics

First, SQLAlchemy requires a connection to a database. Loads of examples are available for how to connect to a local MySQL / PostgreSQL / Oracle / SQL Server / SQLite database. My particular use involves a Google Cloud SQL database, which supports a MySQL dialect.

Regardless of how you connect, you will end up with an engine object. Using that object, you will have to create a few other working objects. Here is some startup code:

import sqlalchemy as db
from sqlalchemy.orm import sessionmaker, registry, relationship
from sqlalchemy.engine import create_engine
from dataclasses import dataclass, field

session = sessionmaker(bind=engine)()
metadata = db.MetaData()
mapper_registry = registry()

Each table is modeled by defining a set of columns, and then defining a SQLAlchemy table to a class with corresponding fields. The beauty of the Imperative model is that we have much greater freedom to declare the classes. For example, I can use a Python dataclass:

from dataclasses import dataclass

@dataclass
class Parent:
  name: str
  age: int = None
  id: int = None

…and then model it in SQLAlchemy as follows:

parents_table = db.Table(
  'parents',                                                      # table name
  metadata,                                                       # required object
  db.Column('name', db.Text, nullable = False),                   # required string
  db.Column('age', db.Integer, nullable = True),                  # optional integer
  db.Column('id', db.Integer, primary_key = True)                 # primary key integer
)

If we create Parent objects and add them to the table, SQLAlchemy will automatically assign the id primary key field as an autoincrementing integer and update the .id field of the object.

We could also create another class with a foreign-key relationship to Parent:

@dataclass
class Child:
  name: str
  parent: int = None
  id: int = None

children_table = db.Table(
  'children',                                                     # table name
  metadata,                                                       # required object
  db.Column('name', db.Text, nullable = False),                   # required string
  db.Column('parent', db.Integer, db.ForeignKey('parents.id')),   # foreign key
  db.Column('id', db.Integer, primary_key = True)                 # primary key integer
)

Based on this schema, we could create a Parent object with an auto-assigned id, and then create a Child object with child.parent = parent.id. SQLAlchemy would store the parent ID in the parent field of the children table. Also, if we read a child record from the database, SQLAlchemy will load parent.id into the parent field of the materialized Child object.

The problem with this arrangement is that parent.id is not very useful to us – what we want is the original Parent object. This task requires writing additional code to load the related parent object from the Parent table, and, optionally, associate with the Child object. These additional steps can quickly scale out of control, especially if the database includes many tables with a sprawling number of connections. Extra code is time-consuming to write, painful to debug, and frustrating to maintain or rewrite as the schema changes.

We need to cut down on these steps. We can do so by giving SQLAlchemy more information as to the specific relationships between the tables, so that when it materializes an object from a table, it also automatically materializes the related objects of other tables and creates the corresponding associations.

Relationship Types Overview

SQLAlchemy supports a wide range of relationship variations and nuances. However, the SQLAlchemy 1.4 Documentation page on “Relationship Configuration” lists some distinct relationship types. As a basic summary:

  • One-to-One: Each instance of the parent class includes an instance of a child class. Each instance of the child class can be included in only one instance of the parent class.
  • One-to-Many: Each instance of a parent class includes a list of instances of a child class. Each instance of the child class can only belong to one instance of the parent class. (Example: a set of State objects, each including a list of City objects. Each City is in only one State.)
  • Many-to-One: Each instance of a parent class includes an instance of a child class. Each instance of the child class could be included in more than one instance of the parent class. (Example: a set of Person objects, each including one instance of a ZodiacSign object. Each ZodiacSign object can be included in multiple Person objects.)
  • Many-to-Many: Each instance of a parent class includes a list of instances of a child class. Each instance of the child class could be included in the lists of more than one instance of the parent class. (Example: a set of Teacher object, each including a list of Student objects. Each Student can be included in the lists of several Teachers.)
  • Association: A Many-to-Many relationship between a parent class and a child class, but in which each instance of the relationship includes one or more additional pieces of information. An Association class is defined that includes an instance of the child class and the additional fields. The parent class includes a list of instances of the Association class. (Example: an Employer/Employee mapping involving a set of Employer objects, each including a list of Employment objects. Each Employment object includes an Employee object and a JobTitle string. Employees can work for more than one Employer.)

In SQLAlchemy, each table is mapped to a Python-side class. Each relationship is defined by adding a relationship() object to the mapping. However, the particular syntax of each relationship() declaration is different, as are the requirements for the classes. The examples below may be helpful.

Important Note #1: Two distinct kinds of one-to-one relationships are shown below: One-to-One and Reverse-One-to-One. On the object side, both relationship types look identical – each parent contains an instance of a child (or None). On the database, side, both relationships add a column to one table with a foreign-key relationship with another table, and each record stores the ID of a record in the other table (or Null). The difference is which table contains the foreign-key column and relation. In the One-to-One example, the parent table has a foreign key column and relation to the child table. In the Reverse-One-to-One example, the child table has a foreign key column and relation to the parent table.

Important Note #2: In the One-to-Many and Many-to-One examples, both parent tables include the same relationship() definition. How does SQLAlchemy know which structure is intended? In the One-to-Many example, the child table has a foreign key relation to the parent table. In the Many-to-One example, the parent table has a foreign key relation to the child table. SQLAlchemy infers the structure based on that distinction.

One-to-One Relationships

  • The parent class includes a field that is an instance of the child class.
  • The parent table is defined with a relationship that relates the name of the parent-class list field to the name of the child class.
  • The child class includes a field that holds a foreign key of a parent record.
  • In the relational database, the parent table includes a column that stores, for each record, the ID of a record of the child table.
  • Conceptually, this relationship type is similar to the Many-to-One relationship, but with a uniqueness constraint on the parent field.
  • Unlike the Reverse-One-to-One Relationship, this relationship type creates the foreign key field in the parent table. This relationship type is more suitable when all, or at least most, parent records include a child object.

Data Classes:

@dataclass
class Capital:
  name: str
  state_id: int = None                            # foreign key of state object
  id: int = None

@dataclass
class State:
  name: str
  capital: Capital = None                         # instance of child object
  id: int = None

Table Mappings:

capitals_table = db.Table(
  'capitals',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(Capital, capitals_table)

states_table = db.Table(
  'states',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('capital_id', db.Integer, db.ForeignKey('capitals.id')), # foreign key field
  db.Column('id', db.Integer, primary_key=True, autoincrement=True),
)
properties = {'capital': relationship('Capital'), uselist = False, single_parent = True}
    # relationship specifies name of parent instance field and name of child class
mapper_registry.map_imperatively(State, states_table, properties = properties)

metadata.create_all(engine)

Example:

seattle = City('Seattle')
washington = State('Washington')
washington.capital = seattle
session.add(washington)                      # writes seattle and washington to database
session.commit()

Reverse-One-to-One Relationships

  • The parent class includes a field that is an instance of the child class.
  • The parent table is defined with a relationship that relates the name of the parent-class list field to the name of the child class.
  • The child class includes a field that holds a foreign key of a parent record.
  • In the relational database, the child table includes a column that stores, for each record, the ID of a record of the parent table.
  • Conceptually, this relationship type is similar to the One-to-Many relationship, but with a uniqueness constraint on the child field.
  • Unlike the One-to-One Relationship, this relationship type creates the foreign key field in the child table. This relationship type may therefore be better when most parent objects do not have a child record, i.e., the child field of the parent objects is typically Null.

Data Classes:

@dataclass
class Ocean:
  name: str
  id: int = None

@dataclass
class Capital:
  name: str
  ocean: Ocean = None                             # instance of child object
  state_id: int = None                            # foreign key of state object
  id: int = None

Table Mappings:

oceans_table = db.Table(
  'oceans',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('state_id', db.Integer, db.ForeignKey('states.id')),    # foreign key field
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(Ocean, oceans_table)

states_table = db.Table(
  'states',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True),
)
properties = {'ocean': relationship('Ocean'), uselist = False, single_parent = True}
    # relationship specifies name of parent instance field and name of child class
mapper_registry.map_imperatively(State, states_table, properties = properties)

metadata.create_all(engine)

Example:

pacific = Ocean('Pacific')
washington = State('Washington')
washington.ocean = pacific
session.add(washington)                      # writes pacific and washington to database
session.commit()

One-to-Many Relationships

  • The parent class includes a list field that can contain instances of the child class.
  • The parent table is defined with a relationship that relates the name of the parent-class list field to the name of the child class.
  • The child class includes a field that holds a foreign key of a parent record.
  • In the relational database, the child table includes a column that stores, for each record, the ID of a record of the parent table.

Data Classes:

@dataclass
class City:
  name: str
  state_id: int = None                            # foreign key of state object
  id: int = None

@dataclass
class State:
  name: str
  cities: list = field(default_factory=list)      # list of child objects
  id: int = None

Table Mappings:

cities_table = db.Table(
  'cities',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('state_id', db.Integer, db.ForeignKey('states.id')),    # foreign key field
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(City, cities_table)

states_table = db.Table(
  'states',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True),
)
properties = {'cities': relationship('City')}
    # relationship specifies name of the parent list field and name of child class
mapper_registry.map_imperatively(State, states_table, properties = properties)

metadata.create_all(engine)

Example:

seattle = City('Seattle')
washington = State('Washington')
washington.cities.append(seattle)
session.add(washington)                      # writes seattle and washington to database
session.commit()

Many-to-One Relationships

  • The parent class includes a field that is an instance of the child class.
  • The parent table includes a foreign key field that holds a foreign key of a child record. Also, the parent table is defined with a relationship that relates the name of the parent-class list field to the name of the child class.
  • The child class has no requirements, nor does the child table.
  • When an instance of the parent class is materialized, an instance of the child class can be assigned to the child-class field.
  • In the relational database, the parent table includes a column that stores, for each record, the ID of a record of the child table.

Data Classes:

@dataclass
class ZodiacSign:
  name: str
  id: int = None

@dataclass
class Person:
  name: str
  zodiac_sign: ZodiacSign = None                 # instance of ZodiacSign class
  id: int = None

Table Mappings:

zodiac_signs_table = db.Table(
  'zodiac_signs',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(ZodiacSign, zodiac_signs_table)

people_table = db.Table(
  'people',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('zodiac_sign_id', db.Integer, db.ForeignKey('zodiac_signs.id')),    # foreign key field
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
properties = {'zodiac_sign': relationship('ZodiacSign')}
    # relationship specifies name of child instance field in parent class and name of child class
mapper_registry.map_imperatively(Person, people_table, properties = properties)

metadata.create_all(engine)

Example:

aquarius = ZodiacSign('Aquarius')
john_doe = Person('John Doe')
john_doe.zodiac_sign = aquarius
session.add(john_doe)                             # writes john_doe and aquarius to database
session.commit()

Many-to-Many Relationships

  • A link table is defined with two foreign-key columns: one for the parent table and one for the child table.
  • The parent class includes a list field that can contain instances of the child class.
  • The parent table is defined with a relationship that relates the name of the parent-class list field to the name of the child class and to the link table.
  • The child class has no requirements, nor does the child table.
  • When an instance of the parent class is materialized, one or more instances of the child class can be added to the list field of the parent class.
  • In the relational database, the link table is created as a two-column table that stores (parent-table key, child-table key) pairs.

Data Classes:

@dataclass
class Student:
  name: str
  id: int = None

@dataclass
class Teacher:
  name: str
  students: list = field(default_factory=list)        # list of child objects
  id: int = None

Table Mappings:

students_table = db.Table(
  'students',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(Student, students_table)

teachers_table = db.Table(
  'teachers',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
link_table = db.Table(                                # link table
  'teachers_students',
  metadata,
  db.Column('teacher', db.Integer, db.ForeignKey('teachers.id'), primary_key = True),
  db.Column('student', db.Integer, db.ForeignKey('students.id'), primary_key = True)
)
properties = {'students': relationship('Student', secondary = link_table)}
    # relationship specifies the name of parent list field and the name of child class
mapper_registry.map_imperatively(Teacher, teachers_table, properties = properties)

metadata.create_all(engine)

Example:

john = Student('John')
mary = Student('Mary')
mr_smith = Teacher('Mr. Smith')
mr_stone = Teacher('Mr. Stone')
mr_smith.students.append(john)
mr_smith.students.append(mary)
mr_stone.students.append(john)
session.add_all([mr_smith, mr_stone])               # write teachers and students to database
session.commit()

Association Relationships

  • An association class is created that includes a field that is an instance of a child class and one or more additional fields.
  • A table is created for the association class that includes a foreign key column for the parent table, a foreign key column for the child table, and columns for the additional fields. Also, the association table is defined with a relationship that relates the name of the field in the association class for the child instance to the name of the child class.
  • The parent class includes a list field that can contain instances of the association class.
  • The parent table is defined with a relationship that relates the name of the list field to the name of the association class.
  • The child class has no requirements, nor does the child table.
  • When an instance of the parent class is materialized, one or more instances of the association class can be added to the list field of the parent class, where each instance of the association class includes an instance of the child class and values of the additional fields.

Data Classes:

@dataclass
class Employee:
  name: str
  id: int = None

@dataclass
class Employer:
  name: str
  employments: list = field(default_factory=list)       # list of Employment instances
  id: int = None

@dataclass
class Employment:                                       # association class
  job_title: str
  employee: Employee = None                             # instance field for child class

Table Mappings:

employees_table = db.Table(
  'employees',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True)
)
mapper_registry.map_imperatively(Employee, employees_table)

employers_table = db.Table(
  'employers',
  metadata,
  db.Column('name', db.Text, nullable=False),
  db.Column('id', db.Integer, primary_key=True, autoincrement=True),
)
properties = {'employments': relationship('Employment')}
    # relationship specifies parent list field and the name of the association class
mapper_registry.map_imperatively(Employer, employers_table, properties = properties)

employments_table = db.Table(
  'employments',
  metadata,
  db.Column('job_title', db.Text, nullable = False),
  db.Column('employer_id', db.Integer, db.ForeignKey('employers.id'), primary_key = True),  # foreign key field
  db.Column('employee_id', db.Integer, db.ForeignKey('employees.id'), primary_key = True)   # foreign key field
)
properties = {'employee': relationship('Employee')}
    # relationship specifies the name of the child instance field in the association class and the name of child class
mapper_registry.map_imperatively(Employment, employments_table, properties = properties)

metadata.create_all(engine)

Example:

john = Employee('John')
mary = Employee('Mary')
acme = Employer('Acme Corporation')
acme.employments.append(Employment('Manager', john))
acme.employments.append(Employment('Director', mary))
techco = Employer('TechCo')
techco.employments.append(Employment('Advisor', mary))
session.add_all([acme, techco])    # write employers, employees, and employments to database
session.commit()

Blog | Home