Using SQLAlchemy with MariaDB Connector/Python: Part 2
In the previous post, “Using SQLAlchemy with MariaDB Connector/Python: Part 1”, we walked through the steps of setting up a new Python application that uses MariaDB Connector/Python and SQLAlchemy to connect to and communicate with MariaDB databases.
Building from the information in Part 1, this blog post will introduce you to the SQLAlchemy entity relationships. We’ll take a look at what relationships are, what makes them so useful when developing solutions using SQLAlchemy and relational databases, and, most importantly, how to set up and use them within Python applications that use MariaDB.
Going forward I’m going to assume that you have an existing environment that allows you to set up, configure, and execute Python applications using MariaDB Connector/Python and SQLAlchemy. If you don’t, don’t fret! The foundational information that you need to know to walk-through this post can be found in “Using SQLAlchemy with MariaDB Connector/Python: Part 1”.
SQLAlchemy Relationships
Before diving into the steps of setting up and using SQLAlchemy relationships it’s important to understand what they are and why they’re useful.
Most simply put, SQLAlchemy provides an API for creating relationships between mapped classes. Ultimately, there are four types of relationships that are supported: one-to-one, one-to-many, many-to-one, and many-to-many. The following describes each of the relationship types and provides Parent and Child classes as very simple examples of how to configure such relationships using SQLAlchemy capabilities.
One-to-many
In a one-to-many relationship, one record in a table can be associated with one or more records in another table. In SQLAlchemy, a one-to-many relationship requires a foreign key to be specified on the Child class that is used to reference a Parent class. For example:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship("Child")
Note: The relationship method is used on the Parent model in order to reference a collection of items used by the Child model.
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
To establish a bidirectional relationship, where the Child model contains a parent property that is mapped to a single parent record, you can use relationship.back_populates.
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", back_populates="children")
Many-to-one
Many-to-one relationships can be configured by setting a foreign key in the Parent model referencing the Child model.
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child")
class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True)
One-to-one
One-to-one relationships can be configured by setting a bidirectional relationship, using a scalar attribute, on both sides (e.g. Child and Parent). To achieve this, the relationship.uselist flag indicates the placement of a scalar attribute instead of a collection on the “many» side of the relationship.
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child = relationship("Child", uselist=False, back_populates="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", back_populates="child")
Many-to-many
Many-to-many relationships require an association to be created between two classes.
association_table = Table('association', Base.metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id'))
)
Then the association table is targeted by using the relational.secondary argument, via the relationship function.
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship("Child", secondary=association_table, back_populates="parents")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
parents = relationship("Parent", secondary=association_table, back_populates="children")
Note: Bidirectional relationships can be established by using the back_populates attribute in combination with a common association table.
SQLAlchemy in Action
Now let’s take a look at a more practical example. The following is going to walk you through the process of setting up a sample Python application that uses SQLAlchemy to manage and use the relationship capabilities within SQLAlchemy. If you want to jump directly into the sample code you can find it here.
The sample we’ll be walking through uses a simple collection of tables that contain all of the previously mentioned relationship types (one-to-one, one-to-many, many-to-one, many-to-many).
Preparing a Python application
Just like in the Part 1 blog post, you will create a new virtual environment, which is simply a directory tree that contains Python executable files and other files which indicate that it is a self-contained environment. This will be the case whether you are building this application from scratch or if you have pulled down the pre-existing source from GitHub.
Using a new terminal window, navigate to the root directory and execute the following command.
$ python3 -m venv venv
Next, activate the new virtual environment.
$ . venv/bin/activate
Finally, install the Maria Connector/Python and SQLAlchemy packages from the Python Package Index (PyPi).
$ pip install mariadb sqlalchemy
Configuring the models
Now that a Python virtual environment has been set up, and the necessary packages have been installed, you’re ready to start creating a new application. Ramping up the complexity a bit from the first blog post, you’re going to be creating several Python files.
base.py
Similarly to Part 1, you’re going to need to create a SQLAlchemy engine instance that can be used to create a new Session object, which is used to connect to and communicate with an underlying database. To do this create a new file called base.py then copy and paste the following code into it.
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker
engine = create_engine("mariadb+mariadbconnector://app_user:Password123!@127.0.0.1:3306/planning")
Session = sessionmaker(bind=engine)
Base = declarative_base()
Note: The connection string indicated in the previous code snippet assumes you have a local MariaDB database instance with a user called “app_user” with the password “Password123!” and an existing database named planning.
From here, you’ll be adding a new file for each model. However, unlike the models that you used in Part 1, the following models will contain additional functionality to define the appropriate mappings between them.
project.py
The Project model functions as a mapping to the products table. The Project model contains information about the fields contained within the products table as well as a property called employees, which defines the many-to-many relationship between it and the Employee model.
The many-to-many relationship between Product and Employee is made possible through an association object called projects_employees_associations, which is defined through use of the Table mapping of the projects_employees table.
Create a new file called project.py then copy and paste the following code into it.
from sqlalchemy import Column, String, Integer, Date, Table, ForeignKey from sqlalchemy.orm import relationship
from base import Base
projects_employees_association = Table(
'projects_employees', Base.metadata,
Column('project_id', Integer, ForeignKey('projects.id')),
Column('employee_id', Integer, ForeignKey('employees.id'))
)
class Project(Base): __tablename__ = 'projects'
id = Column(Integer, primary_key=True)
name = Column(String(length=100))
description = Column(String(length=500))
due_date = Column(Date)
employees = relationship("Employee", secondary=projects_employees_association)
def __init__(self, name, description, due_date): self.name = name self.description = description self.due_date = due_date
employee.py
The Employee model functions as a mapping to the employees table. The Employee model contains configuration that establishes a many-to-one relationship, via department, and a one-to-one relationship, through the contact_details property.
from sqlalchemy import Column, String, Integer, Boolean, ForeignKey from sqlalchemy.orm import relationship, backref
from base import Base
class Employee(Base): __tablename__ = 'employees'
id = Column(Integer, primary_key=True)
department_id = Column(Integer, ForeignKey('departments.id'))
firstname = Column(String(length=100))
lastname = Column(String(length=100))
active = Column(Boolean, default=True)
department = relationship("Department", back_populates="employees")
contact_details = relationship("ContactDetails", uselist=False, back_populates="employee")
def __init__(self, firstname, lastname, department): self.firstname = firstname self.lastname = lastname self.department = department
department.py
The Department model functions as a mapping to the departments table. The Department model contains configuration for a one-to-many relationship via the employees property.
from sqlalchemy import Column, String, Integer, ForeignKey from sqlalchemy.orm import relationship
from base import Base
class Department(Base): __tablename__ = 'departments'
id = Column(Integer, primary_key=True)
name = Column(String(length=50))
employees = relationship("Employee")
def __init__(self, name): self.name = name
contact_details.py
The ContactDetails model functions as a mapping to the contact_details table. Using the employee_id field, the ContactDetails model contains a one-to-one relationship with the Employee model.
from sqlalchemy import Column, String, Integer, ForeignKey from sqlalchemy.orm import relationship, backref
from base import Base
class ContactDetails(Base): __tablename__ = 'contact_details'
id = Column(Integer, primary_key=True)
phone_number = Column(String(length=12))
address = Column(String(length=100))
employee_id = Column(Integer, ForeignKey('employees.id'))
employee = relationship("Employee", backref=backref("contact_details", uselist=False))
def __init__(self, phone_number, address, employee): self.phone_number = phone_number self.address = address self.employee = employee
Loading data
As you learned in the previous blog post, object-relational mapping (ORM) libraries often give you the ability to create and modify database schemas through the use of code.
The following code, which can be added to a new file called load.py, will import all of the previously defined models then use them to create the database tables and finally load data into them.
from datetime import date
from base import Session, engine, Base from employee import Employee from project import Project from department import Department from contact_details import ContactDetails
# Generate database schema Base.metadata.create_all(engine)
# Create a new session session = Session()
# Create projects
project_1 = Project("Project 1", "Project 1 description", date(2021, 5, 31))
project_2 = Project("Project 2", "Project 2 description", date(2021, 4, 30))
project_3 = Project("Project 3", "Project 3 description", date(2021, 6, 15))
# Create departments
dept_marketing = Department("Marketing")
dept_engineering = Department("Engineering")
# Create employees
emp_john = Employee("John", "Locke", dept_marketing)
emp_kate = Employee("Kate", "Austin", dept_engineering)
emp_jack = Employee("Jack", "Shepherd", dept_marketing)
emp_ben = Employee("Ben", "Linus", dept_marketing)
emp_sun = Employee("Sun", "Kwan", dept_engineering)
# Add employees to projects project_1.employees = [emp_john,emp_kate] project_2.employees = [emp_jack,emp_ben,emp_sun] project_3.employees = [emp_john,emp_kate,emp_jack,emp_ben,emp_sun]
# Create contact details
cd_john = ContactDetails("417 315 2531", "123 S Main ST", emp_john)
cd_kate = ContactDetails("212 315 2533", "124 S Main ST", emp_kate)
cd_jack = ContactDetails("816 315 2534", "125 S Main ST", emp_jack)
cd_ben = ContactDetails("913 315 2535", "126 S Main ST", emp_ben)
cd_sun = ContactDetails("773 315 2536", "127 S Main ST", emp_sun)
# Persist data session.add(project_1) session.add(project_2) session.add(project_3)
session.add(dept_marketing) session.add(dept_engineering)
session.add(cd_john) session.add(cd_kate) session.add(cd_jack) session.add(cd_ben) session.add(cd_sun)
session.commit() session.close()
Once you’ve added the previous code block into a new load.py file you can execute it using the following command:
$ python load.py
Querying data
After configuring the relationships and loading information into MariaDB, all directly through Python code, it’s time to take a look at how you can use the classes.
The following code provides a small sample of what’s possible with the models that you’ve created. Using the basic querying knowledge that you learned about in the first blog post, you can dive deeper to examine the relationship data that has been automatically added. To do this simply create a new file called query.py then copy and paste the following code into it.
from base import Session from project import Project from employee import Employee from department import Department from contact_details import ContactDetails
session = Session()
# Get all employees
employees = session.query(Employee).all()
print('### Employees ###')
for employee in employees:
print(f' - {employee.firstname} {employee.lastname}, phone: {employee.contact_details.phone_number}')
# Get all projects projects = session.query(Project).all()
print('### Projects ###')
for project in projects:
print(project.name)
for employee in project.employees:
print(f' - {employee.firstname} {employee.lastname} ({employee.department.name})')
# Get all departments departments = session.query(Department).all()
print('### Departments ###')
for department in departments:
print(department.name)
for employee in department.employees:
print(f' - {employee.firstname} {employee.lastname}')
# John Lock projects john_lock_projects = session.query(Project) \ .join(Employee, Project.employees) \ .filter(Employee.firstname == 'John') \ .all()
print('### John Locke projects ###')
for project in john_lock_projects:
print(f'- {project.name}')
Once you’ve added the previous code block into a new query.py file you can execute it using the following command:
$ python query.py
Next Steps
In this tutorial you learned how to set up relationships between Python classes, and more specifically SQLAlchemy models. Hopefully this walkthrough has given you the foundation you need to start creating your own solutions.
In fact, now that you’ve gotten your feet wet with both SQLAlchemy and MariaDB Connector/Python we’re anxious to see what you’ll do with it! Please reach out to us at developers@mariadb.com to let us know what you’re creating, with any questions or feedback you have, or with ideas of more content you’d like to see us provide.
Additional Resources
You can find the first part of this blog series at https://mariadb.com/resources/blog/using-sqlalchemy-with-mariadb-connector-python-part-1/.
Wanting to dive deeper? Take a look at the official MariaDB Connector/Python documentation available at https://mariadb.com/docs/clients/connector-python/.
For more development content like this be sure to check out the MariaDB Developer Hub.
