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.

The above is the detailed content of One-to-Many and Many-to-Many Relationships in Python with SQLite. For more information, please follow other related articles on the PHP Chinese website!