SQLAlchemy ORM 基本关系模式——提供示例或模板。特别是对于“一对多”和“一对一”

Question

您能给我一个如何使用软件库 SQLAlchemy ORM 的示例吗？特别是，如何构建“一对多”和“一对一”等标准数据库关系？

我知道 SQLAlchemy 文档已经在 基本关系模式 中提供了一些示例，但我正在寻找一些示例来解释初学者用户所发生的情况，特别是讨论需要考虑的权衡。

Answer 1

我创建了一些带有解释性注释的示例/模板：（

这里有更重的格式版本 ）

# Building  1-to-Many  Relationship
# https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-many
# back_populates()  targets are  class attribute names.
# The example is made clearer using my data type prefix notation  and  specifically  o_  as class attributes (but  o_  are not table columns!)
# Note the difference between  parent_id (integer)  and  o_parent_obj (sqla object)
# Note:  l_children_list  is a list of sqla objects.
# Read back_populates() as  "this relationship back populates as the following class attribute on the opposing class"
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    l_children_list = relationship("Child", back_populates="o_parent_obj")   # not a table column

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    o_parent_obj = relationship("Parent", back_populates="l_children_list")   # not a table column



# Building  1-to-1  Relationship
# https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-one
# Two changes:
# To convert this to “one-to-one”, the “one-to-many” or “collection” side is converted into a scalar relationship using the uselist=False flag.
# Add unique constraint (optional)
#
# Child.o_parent_obj  will be 1-to-1 because there is only 1 value in the  Child.parent_id  column. 
#
# Parent.o_first_child  will be 1-to-1 at the ORM level, because ORM forces the value to be a scalar via  uselist=False  flag.
# Tip in docs: 1-to-1 enforcement at the db level is also possible and can be considered:
# This is a db design decision because it's a trade off: it provides referential integrity at the db level but at the cost of an additional db index.
# Enforce 1-to-1 for  Parent.o_first_child  at the db level as follows:
# put unique constraint on  Child.parent_id  column to make sure all  Child  rows point to different  Parent  rows.
# Note: this unique constraint is different from the foreign key designation because foreign key is uniqueness on the Parent table (not the Child table). 
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    o_first_child = relationship("Child", back_populates="o_parent_obj",  uselist=False )   # uselist=False  enforces 1-to-1 at ORM level

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'),  unique=True )   # unique constraint enforces 1-to-1 at db level.  Optional. Creates db index.
    o_parent_obj = relationship("Parent", back_populates="o_first_child")     



# Building  1-way bookkeeping properties  Relationship.  "many-to-one" 
"""
In the db schema design, sometimes there are "bookkeeping" property fields whose value is a row in another table.
These are typically less important fields that are not part of the core db design.
Rather, they are more like bookkeeping properites and the data type for this field is another table.
For example,  created_by  field to track which user created the data entry.
The python code might want to get the user who created the data, but it won't start from the user to get all the data entries that he created.
Thus, these are called "1-way bookkeeping properties".
Oh, there is a name for these: "many-to-one" relationships, but even the top google search results are poor at explaining them.

How to build a "1-way bookkeeping property"?
"""
class UserAccount(Base):
    __tablename__ = 'user_account'
    id = Column(Integer, primary_key=True)
    # UserAccount does not track any of the bookkeeping properties that point to it.

class SomeData(Base):
    __tablename__ = 'some_data'
    id = Column(Integer, primary_key=True)
    i_user_who_created_the_data = Column(Integer, ForeignKey('user_account.id'))
    o_user_who_created_the_data = relationship("UserAccount", foreign_keys="[SomeData.i_user_who_created_the_data]")    # 1-way ORM ability: from SomeData to UserAccount
    i_user_who_last_viewed_the_data = Column(Integer, ForeignKey('user_account.id'))
    o_user_who_last_viewed_the_data = relationship("UserAccount", foreign_keys="[SomeData.i_user_who_last_viewed_the_data]")    # 1-way ORM ability: from SomeData to UserAccount