基于Python与SQLAlchemy、Pandas建立MySQL主键与外键关联

从你描述的表结构和业务需求来看，整体思路符合关系型数据库范式设计，但有几个细节可以优化，确保结构更清晰、避免冗余：

1. 表命名与语义优化 #

建议使用更规范的英文命名（符合MySQL最佳实践），比如：

• 类别类型表 → category_types（存储所有幻灯片类别，主键为category_id）
• 类别类型与患者编号关联表 → patient_category_mapping（关联患者ID和类别ID）
• 患者信息与幻灯片索引关联表 → patient_slides（存储患者人口统计+幻灯片索引，主键为自增的patient_slide_id）
• 幻灯片索引与Accession编号关联表 → slide_accessions（关联patient_slide_id和accession_no）
• Accession编号与注释关联表 → accession_notes（关联accession_no和注释内容）

2. 外键约束的准确性 #

你提到的外键约束需要修正逻辑：

• slide_category关联category_types：合理，确保幻灯片类别只能是预定义的合法值
• 患者编号应关联独立的patients表（建议拆分原第3表的人口统计部分到patients表），避免同一患者的人口统计信息因多个patient_slide_id重复存储
• patient_slide_id应关联patient_slides表的自增主键，而非patient_info表，否则逻辑关联错误
• accession_no需通过slide_accessions表的patient_slide_id关联patient_slides表，再由accession_notes关联slide_accessions.accession_no

3. 满足“每个患者对应两个幻灯片类别”的约束 #

需要在patient_category_mapping表添加复合唯一约束：UNIQUE(patient_id, category_id)，同时在业务导入环节控制每个patient_id对应恰好2条记录（数据库层面可通过触发器辅助校验，但业务层控制更灵活）。

4. 冗余检查 #

如果一个幻灯片索引对应一个accession_no，可考虑合并patient_slides和slide_accessions表，减少关联层级；若存在一对多关系，则拆分结构合理。

pandas.to_sql本身不会自动创建外键约束和唯一约束，需结合SQLAlchemy手动处理，具体有两种方案：

方案1：先通过SQLAlchemy定义带约束的表结构，再导入数据 #

适合提前明确表结构的场景，步骤如下：

1. 用SQLAlchemy ORM定义模型（包含约束） #

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# 类别类型表
class CategoryType(Base):
    __tablename__ = 'category_types'
    category_id = Column(Integer, primary_key=True, autoincrement=True)
    category_name = Column(String(50), unique=True, nullable=False)

# 患者基础信息表
class Patient(Base):
    __tablename__ = 'patients'
    patient_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    age = Column(Integer)
    gender = Column(String(10))

# 患者-类别关联表
class PatientCategoryMapping(Base):
    __tablename__ = 'patient_category_mapping'
    id = Column(Integer, primary_key=True, autoincrement=True)
    patient_id = Column(Integer, ForeignKey('patients.patient_id'), nullable=False)
    category_id = Column(Integer, ForeignKey('category_types.category_id'), nullable=False)
    __table_args__ = (UniqueConstraint('patient_id', 'category_id', name='uq_patient_category'),)

# 患者-幻灯片索引表
class PatientSlide(Base):
    __tablename__ = 'patient_slides'
    patient_slide_id = Column(Integer, primary_key=True, autoincrement=True)
    patient_id = Column(Integer, ForeignKey('patients.patient_id'), nullable=False)
    slide_index = Column(String(50), nullable=False)

# 幻灯片-Accession编号表
class SlideAccession(Base):
    __tablename__ = 'slide_accessions'
    accession_no = Column(String(50), primary_key=True)
    patient_slide_id = Column(Integer, ForeignKey('patient_slides.patient_slide_id'), nullable=False)

# Accession-注释表
class AccessionNote(Base):
    __tablename__ = 'accession_notes'
    id = Column(Integer, primary_key=True, autoincrement=True)
    accession_no = Column(String(50), ForeignKey('slide_accessions.accession_no'), nullable=False)
    note = Column(String(255))

# 创建数据库连接并生成表
engine = create_engine('mysql+pymysql://user:password@host:port/db_name')
Base.metadata.create_all(engine)

2. 按依赖顺序用Pandas导入数据 #

import pandas as pd

# 1. 导入类别类型表
df_categories = pd.read_excel('data.xlsx', sheet_name='类别类型')
df_categories.to_sql('category_types', engine, if_exists='append', index=False)

# 2. 导入患者基础信息表（去重避免重复）
df_patients = pd.read_excel('data.xlsx', sheet_name='患者信息').drop_duplicates(subset=['patient_id'])
df_patients.to_sql('patients', engine, if_exists='append', index=False)

# 3. 导入患者-类别关联表
df_patient_category = pd.read_excel('data.xlsx', sheet_name='患者类别关联')
df_patient_category.to_sql('patient_category_mapping', engine, if_exists='append', index=False)

# 后续表按依赖顺序导入...

方案2：先导入数据，再通过SQLAlchemy执行ALTER语句添加约束 #

适合已通过Workbench创建空表的场景：

1. 先导入所有表数据（按依赖顺序） #

df_categories.to_sql('category_types', engine, if_exists='replace', index=False)
df_patients.to_sql('patients', engine, if_exists='replace', index=False)
# ...其他表依次导入

2. 执行ALTER语句添加约束 #

from sqlalchemy import text

with engine.connect() as conn:
    # 添加患者-类别关联表的复合唯一约束
    conn.execute(text("ALTER TABLE patient_category_mapping ADD CONSTRAINT uq_patient_category UNIQUE(patient_id, category_id);"))
    # 添加外键约束
    conn.execute(text("ALTER TABLE patient_category_mapping ADD FOREIGN KEY (patient_id) REFERENCES patients(patient_id);"))
    conn.execute(text("ALTER TABLE patient_category_mapping ADD FOREIGN KEY (category_id) REFERENCES category_types.category_id;"))
    # 其他外键约束同理添加
    conn.commit()

注意事项 #

• 导入数据时必须遵循外键依赖顺序，先导入被依赖表的数据
• 自增主键列不要包含在导入的DataFrame中，让数据库自动生成
• 导入前需确保数据符合唯一约束要求，否则会触发导入失败

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