One-to-Many and Many-to-Many Relationships in Python with SQLite

When working with databases in Python, understanding relationships between tables is crucial. Two of the most common relationships you’ll encounter are one-to-many and many-to-many. Let’s use a WNBA example to explore what these relationships are, how they work in SQLite, and how to implement them using Python.

What Are One-to-Many and Many-to-Many Relationships?

One-to-Many

A one-to-many relationship means that one record in a table is associated with multiple records in another table. For example, in a database of teams and athletes:

• One team can have many athletes.
• Each athlete belongs to only one team.

Many-to-Many

A many-to-many relationship occurs when multiple records in one table are associated with multiple records in another table. For example, in a database of athletes and sponsorship deals:

• An athlete can have deals with many brands.
• A brand can have deals with many athletes.

To implement many-to-many relationships in SQLite, you need a junction table (also known as a bridge or association table) to link the two main tables.

Implementing Relationships in Python with SQLite

Setting Up the Database

First, let’s create a database to demonstrate these relationships.

import sqlite3

Connect to the SQLite database (or create one if it doesn't exist)

conn = sqlite3.connect("sports.db")
cursor = conn.cursor()

Create tables

cursor.execute("""
CREATE TABLE IF NOT EXISTS Team (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS Athlete (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    team_id INTEGER,
    FOREIGN KEY (team_id) REFERENCES Team (id)
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS Brand (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS Deal (
    id INTEGER PRIMARY KEY,
    athlete_id INTEGER,
    brand_id INTEGER,
    FOREIGN KEY (athlete_id) REFERENCES Athlete (id),
    FOREIGN KEY (brand_id) REFERENCES Brand (id)
)
""")

conn.commit()

One-to-Many: Teams and Athletes

Let’s add data to demonstrate the one-to-many relationship between teams and athletes.

Insert a team and athletes

cursor.execute("INSERT INTO Team (name) VALUES (?)", ("New York Liberty",))
team_id = cursor.lastrowid

cursor.execute("INSERT INTO Athlete (name, team_id) VALUES (?, ?)", ("Breanna Stewart", team_id))
cursor.execute("INSERT INTO Athlete (name, team_id) VALUES (?, ?)", ("Sabrina Ionescu", team_id))

conn.commit()

To query all athletes on a team:

cursor.execute("SELECT name FROM Athlete WHERE team_id = ?", (team_id,))
athletes = cursor.fetchall()
print("Athletes on the team:", athletes)

Many-to-Many: Athletes and Brands

Now, let’s add data to demonstrate the many-to-many relationship between athletes and brands using the Deal table.

Insert brands

cursor.execute("INSERT INTO Brand (name) VALUES (?)", ("Nike",))
brand_id_nike = cursor.lastrowid

cursor.execute("INSERT INTO Brand (name) VALUES (?)", ("Adidas",))
brand_id_adidas = cursor.lastrowid

Insert deals

cursor.execute("INSERT INTO Deal (athlete_id, brand_id) VALUES (?, ?)", (1, brand_id_nike))
cursor.execute("INSERT INTO Deal (athlete_id, brand_id) VALUES (?, ?)", (1, brand_id_adidas))
cursor.execute("INSERT INTO Deal (athlete_id, brand_id) VALUES (?, ?)", (2, brand_id_nike))

conn.commit()

To query all brands associated with an athlete:

cursor.execute("""
SELECT Brand.name 
FROM Brand
JOIN Deal ON Brand.id = Deal.brand_id
WHERE Deal.athlete_id = ?
""", (1,))
brands = cursor.fetchall()
print("Brands for Athlete 1:", brands)

Conclusion

By defining relationships with foreign keys in SQLite and using Python to manage data, you can create robust databases with clear connections between tables. Understanding one-to-many and many-to-many relationships is essential for structuring data effectively.

This simple example scratches the surface, but you can expand it to handle more complex relationships.