你能给我一个如何使用软件库SQLAlchemy ORM的例子吗？特别是，我如何建立标准的数据库关系，如"；"一对多"；以及"；一对一"；？
我知道SQLAlchemy文档已经提供了一些关于基本关系模式的示例，但我正在寻找解释初学者用户正在发生的事情的示例，尤其是讨论需要考虑的权衡。
我创建了一些带有解释性注释的示例/模板：
(此处为较重格式的版本(
# 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