时间戳

首页 > 解决方案 > 使用 SqlAlchemy 代理到基于字典的集合时如何避免 IntegrityError

python - 使用 SqlAlchemy 代理到基于字典的集合时如何避免 IntegrityError

问题描述

使用 sqlalchemy 1.4.x，我设置了以下类：

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm import backref, relationship
from sqlalchemy import ForeignKey, Column, Integer, Unicode


from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:", echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class Infra(Base):

    __tablename__ = "infra"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(200), index=True, unique=True)
    protections = association_proxy(
        "infra_protections",
        "pscore",
        creator=lambda k, v: Infra_Protection(protection=k, pscore=v),
    )



class Protection(Base):

    __tablename__ = "protection"
    id = Column(Integer, primary_key=True, autoincrement=True)
    ptype = Column(Unicode(200), index=True, unique=True)

    def __init__(self, protection):
        self.ptype = protection


class Infra_Protection(Base):

    __tablename__ = "infraprotection"
    infra_id = Column(
        Integer, ForeignKey("infra.id", ondelete="CASCADE"), primary_key=True
    )
    protection_id = Column(
        Integer, ForeignKey("protection.id", ondelete="CASCADE"), primary_key=True
    )
    prot = relationship("Protection")
    protection = association_proxy("prot", "ptype")
    infra = relationship(
        Infra,
        backref=backref(
            "infra_protections",
            collection_class=attribute_mapped_collection("protection"),
            cascade="all, delete-orphan",
        ),
    )
    pscore = Column(Integer, nullable=False, unique=False, server_default="0")

现在我想添加一些Infra对象和相关Protection的s：

Base.metadata.create_all(engine)
i = Infra(name="Foo")
i.protections["test"] = 1
i.protections["test 2"] = 2

session.add(i)
session.commit()

# now, add another
j = Infra(name="Bar")
j.protections["test"] = 3
j.protections["test 2"] = 4

session.add(j)
session.commit() # UNIQUE constraint failed: protection.ptype

很明显，为什么违反了唯一约束，但我想知道如何修改我的关联代理设置，以合理稳健的方式避免这种情况。某种get_or_create关于Protection __init__?

标签： pythonsqlalchemy

解决方案

处理这个问题的方法是从这里实现 UniqueObject 配方之一：https ://github.com/sqlalchemy/sqlalchemy/wiki/UniqueObject

推荐阅读