CODEWITHSUNDEEP
pythonsqlalchemy
Kaia
Kaia

Reputation: 907

Sqlalchemy many-to-many association proxy: silently reject duplicates

I have a many to many association using association proxies as follows:

import sqlalchemy.orm as orm
import sqlalchemy as sa
import sqlalchemy.ext.associationproxy as ap

class Asset(BaseModel):
    __tablename__ = 'assets'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.VARCHAR(255))

    asset_tags = orm.relationship(
            "AssetTag", back_populates='asset', cascade='all, delete-orphan')
    tags = ap.association_proxy(
            "asset_tags", "tag",
            creator=lambda tag: AssetTag(tag=tag))

class AssetTag(BaseModel):
    __tablename__ = 'asset_tags'
    asset_id = sa.Column(sa.Integaer, sa.ForeignKey("assets.id"), primary_key=True)
    tag_id = sa.Column(sa.Integer, alsach.ForeignKey("tags.id"), primary_key=True)

    asset = orm.relationship("Asset", back_populates='asset_tags')
    tag = orm.relationship("Tag", back_populates='asset_tags')


class Tag(BaseModel):
    __tablename__ = 'tags'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.VARCHAR(255))

    asset_tags = orm.relationship("AssetTag",
                                  back_populates='tag',
                                  cascade='all, delete-orphan')
    assets = ap.association_proxy("asset_tags", "asset",
                                  creator=lambda asset: AssetTag(asset=asset))

Note that the asset_tas table has a uniqueness constraint on (asset_id, tag_id).

If I do

with Session() as s:
    a = s.get(Asset, 1)
    tag = a.tags[0]
    a.tags.append(tag)
    s.commit()

SQLAlchemy creates a new AssetTag between Asset<1> and Tag<31> (example number), and tries to commit that, violating the uniqueness constraint.

pymysql.err.IntegrityError: (1062, "Duplicate entry '1-31' for key 'PRIMARY'")

Is there any way to make asset.tags have the behavior of a set, where adding an existing item is skipped?

asset.tags.append(tag)
asset.tags.append(tag) # fails silently

Upvotes: 1

Views: 392

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55799

You can pass set as the relationship's collection_class argument. This will have the collection behave as a set rather than as a list.

    asset_tags = orm.relationship(
            "AssetTag", 
            back_populates='asset',
            cascade='all, delete-orphan',
            collection_class=set,
    )

Note that you must use set methods when operating on the collection, for example

asset.tags.add(some_tag)

not

asset.tags.append(some_tag)

Upvotes: 1

Related Questions