Python 与 SQLite 中的一对多和多对多关系

wufei123 2025-01-26 阅读:64 评论:0

在python中使用数据库时，理解表间关系至关重要。本文以wnba为例，探讨一对多和多对多关系在sqlite中的实现方法，并提供python代码示例。一对多与多对多关系一对多关系: 一个表的一条记录与另一个表的多条记录关联。例如...

在python中使用数据库时，理解表间关系至关重要。本文以wnba为例，探讨一对多和多对多关系在sqlite中的实现方法，并提供python代码示例。

一对多与多对多关系

一对多关系: 一个表的一条记录与另一个表的多条记录关联。例如，一支球队可以有多名运动员，但每名运动员只属于一支球队。
多对多关系: 一个表的多条记录与另一个表的多条记录关联。例如，一名运动员可以与多个品牌签约，一个品牌也可以与多名运动员签约。在SQLite中，需要使用连接表（桥接表）来实现多对多关系。

Python与SQLite的数据库操作

1. 数据库设置

首先，创建一个SQLite数据库并连接：

PHP

import sqlite3

conn = sqlite3.connect("sports.db")  # 创建或连接数据库
cursor = conn.cursor()

2. 创建表

创建team、athlete、brand和deal四个表：

PHP

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()

3. 一对多关系：球队和运动员

插入数据并查询：

PHP

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()

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

4. 多对多关系：运动员和品牌

插入品牌和交易记录，并查询运动员的品牌：

PHP

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

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()

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)