How Do I Establish Relationships Between Tables Using SQLAlchemy?

Read
Discuss

Creating a relationship between tables is an important Object Relationship Model feature of SqlAlchemy. SQLAlchemy is a popular Python library used for Object Relational Mapping (ORM) to interact with databases. One of the key features of SQLAlchemy is the ability to create relationships between tables in a database, which can be defined using the relationship() function. In this tutorial, we will go over the four basic relationship patterns found in SQLAlchemy: One To Many, Many To One, One To One, and Many To Many. We will see how each of these relationships is established and how they can be used in a database.

The types of SQLAlchemy relationships are provided in the table below:

Serial No	Relationship	Explanation
1	One To Many	To establish a One To Many relationship in SQLAlchemy, you need to create a foreign key in the child table which references the parent table. Then, you declare the relationship() on the parent table, which references a collection of items represented by the child table.
2	Many To One	In a Many To One relationship, you need to add a foreign key in the parent table which references the child table.
3	Many To Many	To establish a Many To Many relationship in SQLAlchemy, you need to create an association table with attributes and foreign keys related to two classes.
4	One To One	One To One relationship is a bidirectional relationship where you place a scalar attribute instead of a collection on the “many” side of the relationship. To convert a one-to-many relationship into a one-to-one relationship, set the uselist parameter to false.

How To Create a Relationship Between Tables (One to Many Relationships)?

In this tutorial, we will be creating a new table related to an existing one in our database using SQLAlchemy ORM. We will be using a declarative approach to define the table and its related class.

Step 1: Import Libraries

To get started, we need to first import the necessary modules and set up the SQLite engine:

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///teacher.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

STEP 2: Create a table to get started

We will be creating teacher class to create teacher table:

class Teacher(Base):
  __tablename__ = 'teachers'

  id = Column(Integer, primary_key = True)
  name = Column(String)
  subject = Column(String)
  email = Column(String)
  phone= Column(String)

The teacher table is created successfully.

STEP 3: Create a second table to establish the relationship with the first one.

Now, we will define the courses class that maps to the course table:

class Course(Base):
  __tablename__ = 'courses'
 
  course_id = Column(Integer, primary_key = True)
  teacherid = Column(Integer, ForeignKey('teachers.id'))
  coursename = Column(String)
  duration_in_months = Column(Integer)
  teachers = relationship("Teacher", back_populates = "courses")

Teacher.courses = relationship("Course", order_by = Course.course_id, back_populates = "teachers")
Base.metadata.create_all(engine)

The output will be:

2023-01-29 21:58:04,038 INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
2023-01-29 21:58:04,044 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("teachers")
INFO:sqlalchemy.engine.Engine:PRAGMA main.table_info("teachers")
2023-01-29 21:58:04,051 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-01-29 21:58:04,054 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("teachers")
INFO:sqlalchemy.engine.Engine:PRAGMA temp.table_info("teachers")
2023-01-29 21:58:04,057 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-01-29 21:58:04,060 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("courses")
INFO:sqlalchemy.engine.Engine:PRAGMA main.table_info("courses")
2023-01-29 21:58:04,063 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-01-29 21:58:04,066 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("courses")
INFO:sqlalchemy.engine.Engine:PRAGMA temp.table_info("courses")
2023-01-29 21:58:04,069 INFO sqlalchemy.engine.Engine [raw sql] ()
INFO:sqlalchemy.engine.Engine:[raw sql] ()
2023-01-29 21:58:04,080 INFO sqlalchemy.engine.Engine
CREATE TABLE teachers (
id INTEGER NOT NULL,
name VARCHAR,
subject VARCHAR,
email VARCHAR,
phone VARCHAR,
PRIMARY KEY (id)
)


INFO:sqlalchemy.engine.Engine:
CREATE TABLE teachers (
id INTEGER NOT NULL,
name VARCHAR,
subject VARCHAR,
email VARCHAR,
phone VARCHAR,
PRIMARY KEY (id)
)


2023-01-29 21:58:04,087 INFO sqlalchemy.engine.Engine [no key 0.00666s] ()
INFO:sqlalchemy.engine.Engine:[no key 0.00666s] ()
2023-01-29 21:58:04,100 INFO sqlalchemy.engine.Engine
CREATE TABLE courses (
course_id INTEGER NOT NULL,
teacherid INTEGER,
coursename VARCHAR,
duration_in_months INTEGER,
PRIMARY KEY (course_id),
FOREIGN KEY(teacherid) REFERENCES teachers (id)
)


INFO:sqlalchemy.engine.Engine:
CREATE TABLE courses (
course_id INTEGER NOT NULL,
teacherid INTEGER,
coursename VARCHAR,
duration_in_months INTEGER,
PRIMARY KEY (course_id),
FOREIGN KEY(teacherid) REFERENCES teachers (id)
)


2023-01-29 21:58:04,103 INFO sqlalchemy.engine.Engine [no key 0.00273s] ()
INFO:sqlalchemy.engine.Engine:[no key 0.00273s] ()
2023-01-29 21:58:04,114 INFO sqlalchemy.engine.Engine COMMIT
INFO:sqlalchemy.engine.Engine:COMMIT