All Python developers have something to gain from SQLAlchemy. Whether you're looking for a better way to manage database connections, or build out an ORM data layer for your application, there's no reason for any of us to omit "pip install sqlalchemy" from our Python vocabulary.  

Engineers who work on large-scale applications overwhelming prefer handling data via ORMs over raw SQL. For those with heavy data backgrounds (like myself), the abstraction hiding SQL behind Python objects can be off-putting. Why do we need foreign keys to execute JOINs between two tables? Why do eningeers working on large-scale software seem to overuse terminology such as "one-to-many" versus "many-to-many" relationships, when SQL itself has no such terminology? If you've felt this sentiment, you're in good company.

After a couple years, I've come find that ORMs do result in less work in the context of building applications, and aren't just a crutch for people who are "afraid of SQL." We save significant time by handling sensitive data transactions as reproducible code patterns, but this benefit pales in comparison to what we gain in security and integrity. ORMs don't write destructive SQL queries; people do.

So, yes. It is annoying that engineers who write ORMs use different lingo than those who understand the underlying SQL, and it is annoying how much upfront work it takes to set up an ORM, but it is worth it. Today we're getting the hardest part of ORM development out of the way by learning how to define table relationships in SQLAlchemy.

Setting up some Data Models

We already covered SQLAlchemy data models in our last post, so I'll skip the finer details. If you arrived here by frantically Googling questions about SQLAlchemy, you should probably catch up on what models are and how to define them.

We're going to create to two models to demonstrate how to create a SQL relationship between them. Below we define a Player model (to represent a team sports athlete), and a corresponding Team model:

"""Declare models and relationships."""
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    String,
    Text
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func


Base = declarative_base()


class Player(Base):
    """Individual player belonging to a team."""

    __tablename__ = "player"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    team_id = Column(Integer, ForeignKey("team.id"), nullable=False)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)
    position = Column(String(100), nullable=False)
    injured = Column(Boolean)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__(self):
        return "<Player {}>".format(self.id)


class Team(Base):
    """Team consisting of many players."""

    __tablename__ = "team"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    name = Column(String(255), nullable=False)
    city = Column(String(255), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__(self):
        return "<Team {}>".format(self.id)
models.py

One-to-Many & Many-to-One Relationships

One-to-many (or many-to-one) relationships are perhaps the most common type of database relationships. Examples of these include a customers + orders relationship (where single customers have multiple orders), or a player + team relationship (where a sportsball player belongs to a single team).

Let's demonstrate the latter:

...

from sqlalchemy.orm import relationship
from sqlalchemy.sql import func


Base = declarative_base()


class Player(Base):
    """Individual player belonging to a team."""

    __tablename__ = "player"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    team_id = Column(Integer, ForeignKey("team.id"), nullable=False)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)
    position = Column(String(100), nullable=False)
    injured = Column(Boolean)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    # Relationships
    team = relationship("Team")

    def __repr__(self):
        return "<Player {}>".format(self.id)


class Team(Base):
    """Team consisting of many players."""

    __tablename__ = "team"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    name = Column(String(255), nullable=False)
    city = Column(String(255), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__(self):
        return "<Team {}>".format(self.id)
models.py

Off the bat there are a few things we do recognize. We have two models consisting of Columns: one model for players, and another model for teams. There are two additions here that are new.

First, we have the concept of Foreign keys (set on PlayerModel's team_id column). If you're familiar with SQL, you should be good-to-go here. If not, think of it this way: a foreign key is a property of a column. When a foreign key is present, we're saying that this particular column denotes a relationship between tables: most common items of one table "belong" to items of another table, like when customers "own" orders, or when teams "own" players. In our example, we're saying that each player has a team as specified by their team_id. This way, we can marry data between our players table and our team table.

The other new concept here is relationships. Relationships complement foreign keys, and are a way of telling our application (not our database) that we're building relationships between two models. Notice how the value of our foreign key is 'sqlalchemy_tutorial_teams.id': example is our Postgres schema, and sqlalchemy_tutorial_teams is table name for our teams table. Compare this to the value we pass to our relationship, which is "TeamModel": the class name of the target data model (not the table name!). Foreign keys tell SQL which relationships we're building, and relationships tell our app which relationships we're building. We need to do both.

The point of all this is the ability to easily perform JOINs in our app. When using an ORM, we wouldn't be able to say "join this model with that model", because our app would have no idea which columns to join on. When our relationships are specified in our models, we can do things like join two tables together without specifying any further detail: SQLAlchemy will know how to join tables/models by looking at what we set in our data models (as enforced by the foreign keys & relationships we set). We're really just saving ourselves the burden of dealing with data-related logic while creating our app's business logic by defining relationships upfront.

SQLAlchemy only creates tables from data models if the tables don't already exist. In other words, if we have faulty relationships the first time we run our app, the error messages will persist the second time we run our app, even if we think we've fixed the problem. To deal with strange error messages, try deleting your SQL tables before running your app again whenever making changes to a model.

Back References

Specifying relationships on a data model allows us to access properties of the joined model via a property on the original model. If we were to join our Player with our Team, we'd be able to access properties of a player's team via Player.team.name, where team is the name of our relationship, and name is a property of the associated model.

Relationships created in this way are one-directional, in that we can access team details through a player, but can't access player details from a team. We can solve this easily by setting a back reference.

When creating a relationship, we can pass an attribute called backref to make a relationship bi-directional. Here's how we'd modify the relationship we set previously:

 # Relationships
team = relationship("Team", backref="player")
models.py

With a backref present, we can now access player details of a team by calling Team.player.

Performing a JOIN

Once you've successfully implemented a relationship between two data models, the best way to check your work is to perform a JOIN on these models. We won't waste time going into creating advanced SQLAlchemy ORM queries here, but at least we can check our work:

def join_example():
    records = session.query(Player).\
    	join(Team, Team.id == Player.team_id).all()
    for record in records:
        player_record = {
            'name': record.name,
            'position': record.position,
            'team_name': record.team.name,
            'team_city': record.team.city
        }
        print(player_record)

Here we JOIN Team on Player, so we reference everything as a property of Player. Here's what this outputs with sample data:

{
  'name': 'Joe McMan',
  'position': 'Quarterback',
  'team_name': 'The Piggers',
  'team_city': 'Austin, TX'
}
Record output

Many-to-Many Relationships

Setting foreign key relationships serve us well when we're expecting a table in our relationship to only have a single record per multiple records in another table (ie: one player per team). What if players could belong to multiple teams? This is where things get complicated.

As you might've guessed, many-to-many relationships happen between tables where n number of records from table 1 could be associated with n number of records from table 2. SQLAlchemy achieves relationships like these via association tables. An association table is a SQL table created for the sole purpose of explaining these relationships, and we're going to build one.

Check out how we define the association_table variable below:

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

association_table = Table('association', Base.metadata,
    Column('team_id', Integer, ForeignKey('example.sqlalchemy_tutorial_players.team_id')),
    Column('id', Integer, ForeignKey('example.sqlalchemy_tutorial_teams.id'))
)


class Player(Base):
    """Individual player belonging to a team."""

    __tablename__ = "player"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    team_id = Column(Integer, ForeignKey("team.id"), nullable=False)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)
    position = Column(String(100), nullable=False)
    injured = Column(Boolean)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    # Relationships
    team = relationship("Team")

    def __repr__(self):
        return "<Player {}>".format(self.id)


class Team(Base):
    """Team consisting of many players."""

    __tablename__ = "team"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    name = Column(String(255), nullable=False)
    city = Column(String(255), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__(self):
        return "<Team {}>".format(self.id)
models.py

We're using a new data type Table to define a table which builds a many-to-many association. The first parameter we pass is the name of the resulting table, which we name association. Next we pass Base.metadata to associate our table with the same declarative base that our data models extend. Lastly, we create two columns which serve as foreign keys to each of the tables we're associating: we're linking Player's team_id column with Team's id column.

The essence of we're really doing here is creating a third table which associates our two tables. We could also achieve this by creating a third data model, but creating an association table is a bit more straightforward. From here on out, we can now query association_table directly to get records from our players and teams table.

The final step of implementing an association table is to set a relationship on our data model. Notice how we set a relationship on Player like we did previously, but this time we set the secondary attribute equal to the name of our association table.