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)
到此为止,我们只完成了一对多关系建立的单向更新功能。我们还需要建立该关系的另一边。可能的解决方案有两种:
此时,如果我们想在
ExamRecord
中直接访问其对应的Exam
对象,即要求实现ExamRecord.Exam
的效果的话,我们还需要添加一个exam
对象,并使用back_populates
参数。我们可以在其中一个
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
是不可或缺的。