SQLAlchemy 下的数据库关系设计

SQLAlchemy 主要用于将 Python 中的类映射为数据库中的数据表，方便进行后端的管理操作。>> Object-relational mapper (ORM)

我们从数据库关系的常见设计模式，一对一，一对多，多对一，多对多开始回顾，然后给出 SQLAlchemy 下其具体实现。

引入包 Imports

Import all packages that will be used in the later sections.

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

Base = declarative_base()

一对多 One to Many

我们不妨用Exam表示一次考试，ExamRecord表示一条考试记录。

显而易见，一次考试应该有多条考试记录。

我们需要在 ExamRecord(Child) 的数据成员列表中添加一个数据成员并标记为ForeignKey，指向 Exam(Parent) 的 PrimaryKey。

同时，我们需要在 Exam(Parent) 新增数据成员 records 用于指向 ExamRecord 中的成员。

# Not completed...
import json
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey
from sqlalchemy.sql.type_api import TypeDecorator
from models.database import Base
from sqlalchemy import Column, String, Integer, Boolean, PickleType

class Exam(Base):
    __tablename__ = "exam_exams"
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String)
    state = Column(String)
    records = relationship("ExamRecord")

class ExamRecord(Base):
    __tablename__ = "exam_exam_records"
    id = Column(Integer, autoincrement=True, primary_key=True)
    author_id = Column(Integer, ForeignKey('account_users.id'))
    exam_id = Column(Integer, ForeignKey('exam_exams.id'))
    feedback = Column(PickleType)

到此为止，我们只完成了一对多关系建立的单向更新功能。我们还需要建立该关系的另一边。可能的解决方案有两种：

1. 此时，如果我们想在ExamRecord中直接访问其对应的Exam对象，即要求实现ExamRecord.Exam的效果的话，我们还需要添加一个exam对象，并使用back_populates参数。

2. 我们可以在其中一个relationship()中使用relationship.backref参数创建一个反向引用。

# Example Code for Solution 1
import json
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey
from sqlalchemy.sql.type_api import TypeDecorator
from models.database import Base
from sqlalchemy import Column, String, Integer, Boolean, PickleType

class Exam(Base):
    __tablename__ = "exam_exams"
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String)
    state = Column(String)
    records = relationship("ExamRecord", back_populates="exam") # Here

class ExamRecord(Base):
    __tablename__ = "exam_exam_records"
    id = Column(Integer, autoincrement=True, primary_key=True)
    author_id = Column(Integer, ForeignKey('account_users.id'))
    exam_id = Column(Integer, ForeignKey('exam_exams.id'))
    exam = relationship("Exam", back_populates="records") # And here
    feedback = Column(PickleType)

# Example Code for Solution 2
import json
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey
from sqlalchemy.sql.type_api import TypeDecorator
from models.database import Base
from sqlalchemy import Column, String, Integer, Boolean, PickleType

class Exam(Base):
    __tablename__ = "exam_exams"
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String)
    state = Column(String)
    records = relationship("ExamRecord", backref="exam") # Only here

class ExamRecord(Base):
    __tablename__ = "exam_exam_records"
    id = Column(Integer, autoincrement=True, primary_key=True)
    author_id = Column(Integer, ForeignKey('account_users.id'))
    exam_id = Column(Integer, ForeignKey('exam_exams.id'))
    feedback = Column(PickleType)

如何理解 back_ref？ Why back_ref?

我们该如何理解关系的“单边”性呢？如果理解了“单边”是什么意思，我们自然就知道了back_populates和back_ref的意义了。

# Example Code without back_ref
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent")

这时，如果我们执行如下命令：

>>> parent = Parent()
>>> child = Child()
>>> child.parent = parent
>>> print(parent.child)
[]

我们会发现，parent的child字段并没有随之更新。

也就是说，这个关系的“单边”体现在只有一边的数据进行了更新。

而这显然不是我们在数据关系的处理时想要的。

于是我们就通过back_populates和back_ref字段的设置来完成了这种关系的双向绑定。

自动删除 Cascade on delete

我们想实现是，一旦一个Exam对象被删除，其对应的ExamRecord对象被全部删除的功能。

我们自然可以想到，在原有的数据库中，我们可以通过外键约束来实现这个功能。

那么，该如何在 SQLAlchemy 中实现这个功能呢？

import json
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey
from sqlalchemy.sql.type_api import TypeDecorator
from models.database import Base
from sqlalchemy import Column, String, Integer, Boolean, PickleType

class Exam(Base):
    __tablename__ = "exam_exams"
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String)
    state = Column(String)
    records = relationship("ExamRecord", back_populates="exam",
                           cascade="all, delete", passive_deletes=True)

class ExamRecord(Base):
    __tablename__ = "exam_exam_records"
    id = Column(Integer, autoincrement=True, primary_key=True)
    author_id = Column(Integer, ForeignKey(
        'account_users.id', ondelete="CASCADE"))
    exam_id = Column(Integer, ForeignKey('exam_exams.id', ondelete="CASCADE"))
    exam = relationship("Exam", back_populates="records")
    feedback = Column(PickleType)

我们可以通过配置子表的 ForeignKey.ondelete 字段和父表的 relationship.cascade 字段来实现该功能。

多对一 Many to One

由于类似于一对多关系，我们不再单独分 Section.

如果我们在Exam中添加字段 records_id，其值赋为Column(Integer, ForeignKey('exam_exam_records.id'))，我们便实现了一个多对一关系单边更新的创建。

另一边同理，也是使用back_populates或back_ref实现的。

一对一 One to One

这里我们仍然使用 Parent 类和 Child 类做例子，即使在一对一关系中不应存在父子关系。

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)

    # previously one-to-many Parent.children is now
    # one-to-one Parent.child
    # uselist=False #
    child = relationship("Child", back_populates="parent", uselist=False)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

    # many-to-one side remains, see tip below
    parent = relationship("Parent", back_populates="child")

当然，不使用back_populates，我们仍然可以使用back_ref。

from sqlalchemy.orm import backref

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", backref=backref("child", uselist=False))

多对多 Many to Many

我们考虑用户类User与用户组类Group。一个用户组中可以有多名用户，一个用户也可以同时属于多个用户组。

使用 Table

为了实现多对多关系的建立，我们需要在User与Group之外，单独再建立一个描述二者之间关系的数据表。

# Not Implemented...
from sqlalchemy import Column, String, Integer, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey, Table
from models.database import Base

UserGroupAssociation = Table("account_user_group", Base.metadata,
                             Column('user', ForeignKey('User.id'), primary_key=True),
                             Column('group', ForeignKey('Group.id'), primary_key=True))

class User(Base):
    __tablename__ = "account_users"
    id = Column(Integer, primary_key=True, index=True)
    password = Column(String)
    is_active = Column(Boolean, default=True)
    groups = relationship("Group", secondary=UserGroupAssociation)


class Group(Base):
    __tablename__ = "account_groups"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String)
    is_active = Column(Boolean, default=True)
    users = relationship("User", secondary=UserGroupAssociation)    

然后，我们只需要再使用back_populates或者back_ref进行双边关系绑定就好了。

from sqlalchemy import Column, String, Integer, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey, Table
from models.database import Base

UserGroupAssociation = Table("account_user_group", Base.metadata,
                             Column('user', ForeignKey('User.id'), primary_key=True),
                             Column('group', ForeignKey('Group.id'), primary_key=True))

class User(Base):
    __tablename__ = "account_users"
    id = Column(Integer, primary_key=True, index=True)
    password = Column(String)
    is_active = Column(Boolean, default=True)
    groups = relationship("Group", secondary=UserGroupAssociation, back_populates="users")


class Group(Base):
    __tablename__ = "account_groups"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String)
    is_active = Column(Boolean, default=True)
    users = relationship("User", secondary=UserGroupAssociation, back_populates="groups")

使用 Association 类 (Optional)

我们可以使用一个新类来存储数据关系。

from sqlalchemy import Column, String, Integer, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql.schema import ForeignKey
from models.database import Base

class UserGroupAssociation(Base):
    __tablename__ = "account_user_group"
    user = Column(ForeignKey('User.id'), primary_key=True)
    group = Column(ForeignKey('Group.id'), primary_key=True)
    role = Column(String)

class User(Base):
    __tablename__ = "account_users"
    id = Column(Integer, primary_key=True, index=True)
    password = Column(String)
    is_active = Column(Boolean, default=True)
    groups = relationship("UserGroupAssociation")


class Group(Base):
    __tablename__ = "account_groups"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String)
    is_active = Column(Boolean, default=True)
    users = relationship("UserGroupAssociation")

总结 Conclusion

SQLAlchemy 是一个 Python 的 ORM 管理包，可以协助 API 及后端的开发。

在一对一，一对多，多对一关系中，数据关系的建立主要是通过外键Foreign Key来实现的。

而在多对多的关系中，数据关系主要是通过建立新数据表Association来存储的。

为了建立能够双向更新的数据关系，back_populates和back_ref是不可或缺的。

参考资料 Reference

• https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-many
• https://stackoverflow.com/questions/39869793/when-do-i-need-to-use-sqlalchemy-back-populates