使用SQLAlchemy 2.0为MySQL表添加主键与外键遇阻求助

一、直接执行ALTER语句报错的原因与修正 #

你遇到的ObjectNotExecutableError是因为SQLAlchemy 2.0+版本要求执行原生SQL时必须用text()包裹字符串，同时MySQL中带空格的列名需要用反引号`（而非单引号）转义。修正后的代码如下：

import pandas as pd
from sqlalchemy import create_engine, text
import sqlalchemy as sa

# 读取Excel文件
df = pd.read_excel(r"C:filepath" + "TV.xlsx")
df2 = pd.read_excel(r"C:filepath" + "DIB.xlsx")
df3 = pd.read_excel(r"C:filepath" + "Reference.xlsx")

# 定义Schema
df_schema = {
    "Item #": sa.Integer, 
    "Short Description": sa.String(255),
    "Model #": sa.String(255),
    "Pack": sa.String(255), 
    "Member Cost": sa.String(255), 
    "Suggested Retail": sa.String(255),
    "Available": sa.Integer,
}

# 创建数据库连接
engine = create_engine("mysql://root:1234@localhost/HardwareDB")

# 导入数据到数据库
df.to_sql('truevalue', con=engine, index=False, if_exists='replace', dtype=df_schema) 
df2.to_sql('doitbest', con=engine, index=False, if_exists='replace', dtype=df2_schema) 
df3.to_sql('reference', con=engine, index=False, if_exists='replace', dtype=df3_schema) 

# 添加主键与外键约束
with engine.connect() as conn:
    # 先给truevalue表添加主键（若之前未定义）
    conn.execute(text("ALTER TABLE truevalue ADD PRIMARY KEY (`Item #`);"))
    # 添加外键约束，注意列名用反引号包裹
    conn.execute(text("""
        ALTER TABLE truevalue 
        ADD CONSTRAINT fk_truevalue_reference 
        FOREIGN KEY (`Item #`) REFERENCES reference(`TV SKU`);
    """))
    conn.commit()  # 必须提交事务才会生效

二、MetaData方法无效的原因与正确用法 #

你之前的MetaData代码仅定义了表结构，但未执行创建操作，且后续用df.to_sql(if_exists='replace')会直接覆盖手动定义的表，导致约束丢失。正确用法分两种场景：

场景1：先通过MetaData创建表结构，再导入数据 #

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table, ForeignKey
import pandas as pd

metadata = MetaData()

# 先定义被依赖的reference表（外键关联需保证父表先存在）
reference = Table(
    "reference",
    metadata,
    Column("TV SKU", Integer, primary_key=True),
    # 补充其他列的定义，需与Excel列匹配
    # Column("其他列名", String(255)),
)

# 定义truevalue表，包含主键与外键
truevalue = Table(
    "truevalue",
    metadata,
    Column("Item #", Integer, primary_key=True),
    Column("Short Description", String(255)),
    Column("Model #", String(255)),
    Column("Pack", String(255)), 
    Column("Member Cost", String(255)), 
    Column("Suggested Retail", String(255)),
    Column("Available", Integer),
    # 定义外键关联
    Column("Item #", Integer, ForeignKey("reference.`TV SKU`")),
)

# 创建数据库连接并生成表结构
engine = create_engine("mysql://root:1234@localhost/HardwareDB")
metadata.create_all(engine)

# 导入数据，使用if_exists='append'避免覆盖表结构
df3 = pd.read_excel(r"C:filepath" + "Reference.xlsx")
df3.to_sql('reference', con=engine, index=False, if_exists='append')

df = pd.read_excel(r"C:filepath" + "TV.xlsx")
df.to_sql('truevalue', con=engine, index=False, if_exists='append')

场景2：反射已存在的表，再添加约束 #

如果已经通过to_sql导入了数据，可通过反射表结构来修改：

from sqlalchemy import create_engine, MetaData, text

engine = create_engine("mysql://root:1234@localhost/HardwareDB")
metadata = MetaData()
metadata.reflect(bind=engine)

# 获取已存在的表
truevalue = metadata.tables['truevalue']
reference = metadata.tables['reference']

# 添加主键与外键（需用原生SQL执行）
with engine.connect() as conn:
    conn.execute(text("ALTER TABLE truevalue ADD PRIMARY KEY (`Item #`);"))
    conn.execute(text("""
        ALTER TABLE truevalue 
        ADD CONSTRAINT fk_truevalue_reference 
        FOREIGN KEY (`Item #`) REFERENCES reference(`TV SKU`);
    """))
    conn.commit()

关键注意事项 #

• 外键关联的两列数据类型必须完全一致（如都是Integer，长度相同），否则会触发约束错误。
• 导入数据时，需先导入父表（如reference）的数据，再导入子表（如truevalue），避免外键冲突。
• MySQL中含特殊字符（如空格）的表/列名，必须用反引号`包裹。

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