SQL Alchemy 默认值函数，用于在一组唯一的父子记录中模拟自动增量

Question

我有一个小问题，我认为应该可以通过 SQL Alchemy 轻松处理，但我似乎无法解决它。我有两张表，一张是父表，另一张是子表。对于每个子记录，它需要一个唯一的 ID，但仅限于唯一父记录的上下文。

我正在使用声明性基础方法。

我使用 FK 和关系函数设置父子关系。我想要实现的是获得类似伪自动增量函数的功能，该函数将查找类别唯一名称组中的最大 CategoryID 值并将其加一。我尝试过使用各种默认函数，但遇到的问题是无法在插入时指定 CategoryUniqueName。我找不到传递 CategoryItems.CategoryUniqueName 的当前值的方法，以便查找查询在尝试选择 func.max(CategoryItems.CategoryID) 之类的内容时应用正确的过滤器。如果我对查询进行硬编码，它就可以正常工作。这是我认为应该可行的方法，但同样，我找不到一种方法来指定过滤器的唯一值。

unique_group='my_group'
result=con.query(func.max(CategoryItems.CategoryID)).filter(and_(
        CategoryItems.CategoryUniqueName==unique_group, 
    )).one()

类别如下所示。非常感谢有关如何在标准 SQL Alchemy 中实现此目的的一些指导。我知道我总是可以查找该值并直接在同一事务中直接指定它，但我正在尝试提出一种独立的 SQL Alchemy 方法，该方法不需要在其他地方添加额外的逻辑。

class Category(Base):
    __tablename__ = 'parent_table'
    __table_args__ = {'mysql_engine':'InnoDB', 'useexisting':True}

    CategoryUniqueName = Column(Unicode(255), primary_key=True)
    CategoryGroupName = Column(Unicode(255), nullable=False)
    CategoryGroupMemo = Column(UnicodeText)
    SortOrder = Column(Integer, index=True)
    IsLocked = Column(Boolean, default=0)

class CategoryItems(Base):
    __tablename__ = 'child_table'
    __table_args__ = {'mysql_engine':'InnoDB', 'useexisting':True}

    CategoryUniqueName = Column(Unicode(255), ForeignKey(Category.CategoryUniqueName), primary_key=True)
    CategoryID = Column(Integer, primary_key=True, autoincrement=False)
    CategoryName = Column(Unicode(255), nullable=False, index=True)
    CategoryMemo = Column(UnicodeText)
    CategoryImage = Column(Unicode(255))
    CategoryFlex1 = Column(Unicode(255), index=True)
    CategoryFlex2 = Column(Unicode(255), index=True)
    CategoryFlex3 = Column(Unicode(255), index=True)
    SortOrder = Column(Integer, index=True)

    category_group = relation(
        Category, 
        backref=backref(
            'items', 
            order_by=SortOrder, 
            collection_class=ordering_list('SortOrder'), 
            cascade="all, delete, delete-orphan"
    ))

Answer 1

我认为有 3 条路可走：

1. 最明显且有据可查的。使用 before_insert() 钩子替换插入的参数创建映射器扩展。
2. 将函数作为 default 参数传递。使用 context 参数调用此函数，其中包含您需要的所有数据：context.compiled_pa​​rameters[0]['CategoryUniqueName']、context.connection。
3. 在 server_default 参数中传递 FetchedValue() 并使用触发器在服务器端执行作业。

所有这些解决方案都具有 ddaa 提到的竞争条件。如果出现竞争条件，您的代码不会破坏数据库状态，但在正确定义主键时会失败并出现异常（这对您的代码来说不是这样！）。在极少数情况下，某些应用程序失败（在 Web 应用程序中显示 500 页）可能是可以接受的。

请注意，您已将 CategoryID 定义为主键。这不允许对 CategoryUniqueName 列的不同值重复使用相同的数字。您必须将其更改为 2 列的复合主索引。

Answer 2

感谢丹尼斯的洞察力，你说得对。我尝试了选项 1 和 2，效果非常好。上下文参数是选项 2 的关键。我没有意识到它是自动传递的。我注意到的一件事是，即使在单个用户提交多个记录的情况下，选项 1 也会引入竞争条件。我认为这与冲洗和保存时间有关。然而选项 2 的效果非常好。

这是现在从默认参数调用的小函数：

def getNextId(context):
    unique_name=context.compiled_parameters[0]['CategoryUniqueName']
    sql = """
        SELECT MAX(CategoryID)
        FROM child_table
        WHERE CategoryUniqueName='%s'""" % (unique_name, )

    result = context.connection.execute(sql).fetchone()[0]
    if result > 0:
         return result + 1
    else:
        return 1 

Answer 3

那么，您想要实现的目标是让每组具有不同 CategoryUniqueName 的 CategoryItem 的 CategoryId 分别自动递增？

如果这是正确的，则您当前的方法（获取要添加到的 CategoryItems 子集中的当前最大 CategoryId）已被破坏。它有一个内在的竞争条件：并发插入将使用相同的 CategoryId。

您真的需要单独递增您的 CategoryId 吗？为什么不直接使用普通的自动增量功能呢？给定 CategoryUniqueName 的 CategoryId 序列将有漏洞，但这真的是一个问题吗？

如果您需要连续的序列号，则需要使用一些手动锁定来防止竞争条件。