用Python+SQLAlchemy实战解析PTA数据库ER模型填空题

当你在PTA数据库原理考试中遇到"根据语义设计ER图"的填空题时，是否感到抽象概念难以落地？本文将以医院管理系统为例，带你用SQLAlchemy将ER模型填空题转化为可执行的Python代码。通过这种"逆向工程"式学习，你会发现那些难记的"实体完整性"、"参照完整性"规则，不过是数据库设计中的自然约束。

1. 从填空题到实体类：医院系统建模实战

假设我们拿到这样一道PTA填空题：

"某医院管理系统包含科室、医生、病人实体。其中：一个科室有多个医生，一个医生属于一个科室；一个医生可诊治多个病人，一个病人有唯一主管医生；一个科室有多个病房，一个病房属于一个科室；一个病房可入住多个病人。"

1.1 识别实体与属性

首先提取题目中的实体和属性。根据常见医院管理系统，我们可以补充典型属性：

from sqlalchemy import Column, Integer, String, ForeignKey, Date
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Department(Base):  # 科室实体
    __tablename__ = 'departments'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)  # 科室名称
    location = Column(String(100))  # 科室位置
    phone = Column(String(20))  # 联系电话
    
    # 定义一对多关系
    doctors = relationship("Doctor", back_populates="department")
    wards = relationship("Ward", back_populates="department")

这里体现了ER模型到类定义的转换技巧：

• 实体 → Python类
• 实体属性 → 类属性(Column)
• 主键 → primary_key=True
• 唯一约束 → unique=True

1.2 处理1:N联系类型

题目中"一个科室有多个医生"是典型的1:N关系。在SQLAlchemy中，这种关系通过外键+relationship实现：

class Doctor(Base):  # 医生实体
    __tablename__ = 'doctors'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    title = Column(String(20))  # 职称
    hire_date = Column(Date)  # 聘用日期
    
    # 外键指向科室表
    department_id = Column(Integer, ForeignKey('departments.id'))
    # 定义关系
    department = relationship("Department", back_populates="doctors")
    patients = relationship("Patient", back_populates="doctor")

注意：在1:N关系中，外键总是放在"N"方（这里是医生表）。这与ER模型转换规则完全一致——"在N端实体类型中加入1端实体类型的主键"。

2. 复杂关系建模：M:N联系与关联表

当遇到"一个医生可诊治多个病人，一个病人有唯一主管医生"这样的描述时，初学者常会困惑。实际上这是两个不同的关系：

2.1 1:N的诊治关系

class Patient(Base):  # 病人实体
    __tablename__ = 'patients'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    admission_date = Column(Date)  # 入院日期
    
    # 外键指向主管医生（1:N）
    doctor_id = Column(Integer, ForeignKey('doctors.id'))
    doctor = relationship("Doctor", back_populates="patients")
    
    # 外键指向病房（1:N）
    ward_id = Column(Integer, ForeignKey('wards.id'))
    ward = relationship("Ward", back_populates="patients")

2.2 M:N的会诊关系（扩展场景）

如果题目变为"病人可由多名医生会诊"，就需要建立M:N关系。这正是PTA常考的考点：

# 首先创建关联表
doctor_patient_association = Table(
    'doctor_patient_association', Base.metadata,
    Column('doctor_id', Integer, ForeignKey('doctors.id')),
    Column('patient_id', Integer, ForeignKey('patients.id')),
    Column('consultation_date', Date)  # 会诊日期作为联系属性
)

# 修改Doctor类添加多对多关系
class Doctor(Base):
    # ... 其他属性同上 ...
    consulting_patients = relationship(
        "Patient",
        secondary=doctor_patient_association,
        back_populates="consulting_doctors"
    )

# 修改Patient类
class Patient(Base):
    # ... 其他属性同上 ...
    consulting_doctors = relationship(
        "Doctor",
        secondary=doctor_patient_association,
        back_populates="consulting_patients"
    )

这种模式完美对应了ER模型中"M:N联系转换为独立关系模式"的规则，关联表的主键由两端实体的主键组成。

3. 完整性约束的代码实现

PTA填空题常考的"实体完整性"、"参照完整性"等概念，在SQLAlchemy中都有直接对应：

3.1 实体完整性规则

class Ward(Base):  # 病房实体
    __tablename__ = 'wards'
    id = Column(Integer, primary_key=True)  # 主键非空 → 实体完整性
    room_number = Column(String(10), unique=True, nullable=False)
    bed_count = Column(Integer)
    
    department_id = Column(Integer, ForeignKey('departments.id'))
    department = relationship("Department", back_populates="wards")
    patients = relationship("Patient", back_populates="ward")

• primary_key=True 保证主键唯一且非空
• nullable=False 强制属性非空
• unique=True 确保值唯一

3.2 参照完整性实践

当设置外键约束时，SQLAlchemy自动实现参照完整性：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///hospital.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 尝试插入违反参照完整性的数据会报错
invalid_doctor = Doctor(
    name="张医生",
    title="主任医师",
    department_id=999  # 不存在的科室ID
)
# session.add(invalid_doctor)  # 这将引发IntegrityError

4. 从模型到数据库：完整工作流演示

让我们用代码回答一道典型PTA填空题："医院系统中，病房与科室是___联系"。

通过实际建模，我们可以验证答案是1:N：

# 创建测试数据
cardiology = Department(
    name="心内科",
    location="住院部3楼",
    phone="123456"
)

ward301 = Ward(
    room_number="301",
    bed_count=4,
    department=cardiology  # 建立关系
)

# 添加到数据库
session.add(cardiology)
session.add(ward301)
session.commit()

# 验证关系
test_ward = session.query(Ward).filter_by(room_number="301").first()
print(f"病房所属科室：{test_ward.department.name}")  # 输出：心内科
print(f"科室下病房数：{len(cardiology.wards)}")  # 输出：1

这种实践方式让抽象的ER概念变得具体可见。当你在PTA考试中遇到类似问题时，脑海中会自然浮现出对应的代码结构。

通过SQLAlchemy的 automap 扩展，我们甚至可以从现有数据库逆向生成模型类，这种"逆向工程"体验能加深对ER模型的理解：

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

# 假设已有医院数据库
engine = create_engine('sqlite:///hospital_existing.db')
Base = automap_base()
Base.prepare(engine, reflect=True)

# 获取自动生成的类
Department = Base.classes.departments
Doctor = Base.classes.doctors

# 查询示例
doctors = session.query(Doctor).join(Department).filter(Department.name=="心内科").all()

这种从实践到理论再回到实践的学习闭环，正是掌握数据库设计的关键。下次当你在PTA中遇到ER模型填空题时，不妨先想象对应的Python类结构，答案往往会变得清晰明了。