基于两个DataFrame创建SQLite3数据库遇conn未定义报错求助

问题分析 #

1. 原代码中重复调用sqlite3.connect('musicten.db')属于冗余操作，虽不会直接引发conn未定义，但易导致连接混乱；
2. music表中使用的SECONDARY KEY并非SQLite支持的语法，需替换为外键约束关联singer表的Singer_ID；
3. 未完成DataFrame数据写入数据库的步骤，且music表存在多歌手ID（如(4,5)）的非规范数据，需先拆分处理；
4. 原日期格式DD.MM.YYYY不符合SQLite的DATE类型要求，需转换为YYYY-MM-DD格式。

完整代码实现 #

1. 导入依赖并预处理DataFrame #

假设你已拥有music_df和singer_df两个DataFrame，先完成数据格式修正：

import sqlite3
import pandas as pd

# ---------------------- 模拟你的DataFrame（已有可跳过） ----------------------
music_data = [
    ["LA", "01.05.2009", 1, 1, 1],
    ["Second", "13.07.2009", 1, 2, 2],
    ["Mexico", "13.07.2009", 1, 3, 1],
    ["Let's go", "13.09.2009", 1, 4, 3],
    ["Hello", "18.09.2009", 1, 5, "(4,5)"],
    ["Don't give up", "12.02.2010", 2, 6, "(5,6)"],
    ["ZIC ZAC", "18.03.2010", 2, 7, 7],
    ["Blablabla", "14.04.2010", 2, 8, 2],
    ["Oh la la", "14.05.2011", 3, 9, 4],
    ["Food First", "14.05.2011", 3, 10, 5],
    ["La Vie est..", "17.06.2011", 3, 11, 8],
    ["Jajajajajaja", "13.07.2011", 3, 12, 9]
]
music_df = pd.DataFrame(music_data, columns=["name", "Date", "Edition", "Song_ID", "Singer_ID"])

singer_data = [
    ["JT Watson", "USA", 1],
    ["Rafinha", "Brazil", 2],
    ["Juan Casa", "Spain", 3],
    ["Kidi", "USA", 4],
    ["Dede", "USA", 5],
    ["Briana", "USA", 6],
    ["Jay Ado", "UK", 7],
    ["Dani", "Australia", 8],
    ["Mike Rich", "USA", 9]
]
singer_df = pd.DataFrame(singer_data, columns=["Singer", "nationality", "Singer_ID"])
# -----------------------------------------------------------------------------

# 转换日期格式为SQLite支持的YYYY-MM-DD
music_df["Date"] = pd.to_datetime(music_df["Date"], format="%d.%m.%Y").dt.strftime("%Y-%m-%d")

# 拆分多歌手ID，展开为单独行
def split_singer_ids(row):
    singer_id = row["Singer_ID"]
    if isinstance(singer_id, str) and singer_id.startswith("(") and singer_id.endswith(")"):
        ids = [int(id.strip()) for id in singer_id[1:-1].split(",")]
        return pd.DataFrame([{**row, "Singer_ID": id} for id in ids])
    else:
        return pd.DataFrame([row])

music_df_expanded = pd.concat([split_singer_ids(row) for _, row in music_df.iterrows()], ignore_index=True)
music_df_expanded["Singer_ID"] = music_df_expanded["Singer_ID"].astype(int)

2. 创建数据库并写入数据 #

# 建立数据库连接（仅调用一次）
conn = sqlite3.connect('musicten.db')
# 启用SQLite外键约束（默认关闭）
conn.execute("PRAGMA foreign_keys = ON")
c = conn.cursor()

# 创建singer表
c.execute('''
CREATE TABLE IF NOT EXISTS singer (
    Singer_ID INTEGER PRIMARY KEY,
    Singer TEXT NOT NULL,
    nationality TEXT
)
''')

# 创建music表，添加外键关联singer表
c.execute('''
CREATE TABLE IF NOT EXISTS music (
    Song_ID INTEGER PRIMARY KEY,
    Singer_ID INTEGER NOT NULL,
    name TEXT NOT NULL,
    Date DATE NOT NULL,
    Edition INTEGER,
    FOREIGN KEY (Singer_ID) REFERENCES singer(Singer_ID)
)
''')

# 将DataFrame数据批量写入数据库
singer_df.to_sql('singer', conn, if_exists='replace', index=False)
music_df_expanded.to_sql('music', conn, if_exists='replace', index=False)

# 提交操作并关闭连接
conn.commit()
conn.close()

关键说明 #

• 移除冗余的连接调用，确保conn正确定义；
• 替换无效的SECONDARY KEY为标准外键约束，保证数据关联一致性；
• 拆分多歌手ID数据，符合数据库范式要求；
• 使用to_sql方法高效写入DataFrame数据，替代手动INSERT语句；
• 统一日期格式，确保SQLite能正确识别DATE类型字段。

内容的提问来源于stack exchange，提问作者user19965401