Python(SQLAlchemy)+MariaDB多插入事务约束失败问题排查

使用Python（SQLAlchemy）和MariaDB执行三个插入操作，第三个插入的外键约束依赖前两个插入的结果。希望在一个事务中完成所有操作，出错时统一回滚，但目前只有在前两个插入提交后，第三个才能成功。相关代码如下：

业务逻辑代码：

try:
    ulcdb.add_person(uid, lang)
    ulcdb.add_group(gid, gid_number)
    ulcdb.add_membership(uid, gid, 'owner')
    ulcdb.commit()
except Exception as e:
    ulcdb.rollback()
    logger.error(str(e))
    raise

数据库操作类定义：

class UserLifecycleDatabase:

    def __init__(self):
        engine = sqlalchemy.create_engine(url)
        Session = sqlalchemy.orm.sessionmaker(bind=engine)
        self.session = Session()

    def commit(self):
        self.session.commit()

    def rollback(self):
        self.session.rollback()

    def add_person(self, uid, lang):
        self.session.add(Person(uid=uid, lang=lang))

    # 省略其他方法实现

核心原因 #

SQLAlchemy无法识别三个插入操作之间的外键依赖关系，导致可能乱序执行INSERT语句（例如先插入关联表membership，再插入person和group），触发MariaDB的外键约束错误。

解决步骤 #

1. 修正Membership模型的外键定义
   必须在Membership模型中明确声明外键关联，让SQLAlchemy知晓依赖关系，从而自动排序INSERT语句的执行顺序。示例代码：

   from sqlalchemy import Column, String, ForeignKey
   from sqlalchemy.orm import relationship
   from sqlalchemy.ext.declarative import declarative_base
   
   Base = declarative_base()
   
   class Person(Base):
       __tablename__ = 'person'
       uid = Column(String, primary_key=True)
       lang = Column(String)
   
   class Group(Base):
       __tablename__ = 'group'
       gid = Column(String, primary_key=True)
       gid_number = Column(Integer)
   
   class Membership(Base):
       __tablename__ = 'membership'
       uid = Column(String, ForeignKey('person.uid'), primary_key=True)
       gid = Column(String, ForeignKey('group.gid'), primary_key=True)
       role = Column(String)
       
       # 可选：定义ORM关联关系，方便后续业务操作
       person = relationship("Person")
       group = relationship("Group")
   

2. 确保add_membership方法的正确性
   该方法需直接创建Membership实例并添加到Session，避免使用原生SQL（若使用原生SQL需手动保证执行顺序）：

   def add_membership(self, uid, gid, role):
       self.session.add(Membership(uid=uid, gid=gid, role=role))
   

3. 验证事务执行逻辑
   SQLAlchemy的Session在commit()时会自动flush所有待执行的操作，并根据外键依赖关系排序SQL语句的执行顺序（Person → Group → Membership），确保在同一个事务内完成所有插入，不会触发外键约束错误。

内容的提问来源于stack exchange，提问作者loris