Automatic unique Many-To-Many Relationships

[sjo...@congressus.nl]

Hi,

Is there a way for SQLAlchemy to silently avoid duplicate entries in a Many-To-Many Association Table?
My current set-up;

class UserRole(db.Model):
    __tablename__ = 'user_roles'

    id = Column(Integer, primary_key=True)
    rolename = Column(Unicode(80), unique=True)

UserUserRoleTable = Table('user_user_roles', db.Model.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('user_role_id', Integer, ForeignKey('user_roles.id')),
    UniqueConstraint('user_id', 'user_role_id', name='uix_1')
)

class User(db.Model):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    roles = relationship("UserRole", secondary=lambda: UserUserRoleTable, backref="users")

Code::

    r1 = UserRole('news_add')
    db.session.add(r1)
    r3 = UserRole('news_del')
    db.session.add(r3)

    me = Administrator()
    me.fullname = 'Sjoerd Huisman'
    me.roles.append(r1)
    me.roles.append(r3)
    me.roles.append(r1)
    db.session.add(me)

Without the UniqueConstraint rows are the r1 is added twice. With the UniqueConstraint, there is a big error.

Thanks for any thoughts!

[Michael Bayer]

On Jan 6, 2013, at 6:48 AM, sjo...@congressus.nl wrote:

Hi,

Is there a way for SQLAlchemy to silently avoid duplicate entries in a Many-To-Many Association Table?
My current set-up;

class UserRole(db.Model):
    __tablename__ = 'user_roles'

    id = Column(Integer, primary_key=True)
    rolename = Column(Unicode(80), unique=True)

UserUserRoleTable = Table('user_user_roles', db.Model.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('user_role_id', Integer, ForeignKey('user_roles.id')),
    UniqueConstraint('user_id', 'user_role_id', name='uix_1')
)

class User(db.Model):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    roles = relationship("UserRole", secondary=lambda: UserUserRoleTable, backref="users")

pretty simple just use a set() for the collection:

relationship("UserRole", secondary=..., collection_class=set)

http://docs.sqlalchemy.org/en/rel_0_8/orm/collections.html?highlight=collection_class#customizing-collection-access

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mNJa_CN1Id8J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[sjo...@congressus.nl]

Easy as that, but I could not find it myself, hmm - Thanks!