CODEWITHSUNDEEP
pythonsqlflaskflask-sqlalchemy
SeanPlusPlus
SeanPlusPlus

Reputation: 9033

Saving duplicate entries in a Flask SQLAlchemy association table

I am designing a schema to audit which user has which monitors.

For a given audit, we have users. And each user can have zero or many monitors.

Also, a user can have many of the same monitor.

Here is my User class:

class User(db.Model):
    id = db.Column(db.Integer, db.Sequence('user_id_seq'), 
                   autoincrement=True, primary_key=True)

    login = db.Column(db.String(140), unique=True)

    def __init__(self, login):
        self.login = login

    def __repr__(self):
        return '<User %r>' % self.login

Here is my Audit class:

class Audit(db.Model):
    id = db.Column(db.Integer, db.Sequence('audit_id_seq'), 
                   autoincrement=True, primary_key=True)

    start_date = db.Column(db.DateTime)
    end_date = db.Column(db.DateTime)

    def __init__(self):
        self.start_date  = datetime.now()

    def __repr__(self):
        return '<Audit %r>' % self.id

Here is my Monitor class:

class Monitor(db.Model):
    id = db.Column(db.Integer, db.Sequence('monitor_id_seq'), 
                   autoincrement=True, primary_key=True)

    description = db.Column(db.String(140), unique=True)

    def __init__(self, description):
        self.description = description

    def __repr__(self):
        return '<Monitor %r>' % self.description

Here is my UserAudit class:

class UserAudit(db.Model):
    id = db.Column(db.Integer, db.Sequence('user_audit_id_seq'), 
                   autoincrement=True, primary_key=True)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('user',
        backref=db.backref('user_audits', lazy='dynamic'))

    audit_id = db.Column(db.Integer, db.ForeignKey('audit.id'))
    audit = db.relationship('Audit')

    monitors = db.relationship('Monitor', 
                                secondary=userAuditMonitor.association_table,
                                backref='user_audit_monitors')

    def __init__(self, user, audit):
        self.user = user
        self.audit = audit

    def __repr__(self):
        return '<UserAudit %r>' % self.id

And finally, here is my UserAuditMonitor class which glues the whole thing together:

class UserAuditMonitor():
    association_table = Table('user_audit_monitor', db.Model.metadata,
        Column('user_audit_id', db.Integer, db.ForeignKey('user_audit.id')),
        Column('monitor_id', db.Integer, db.ForeignKey('monitor.id'))
    )

The above association table is super useful as I can simply use the .append() method and add more monitors to a UserAudit sqlalchemy object.

Example:

>>> u = UserAudit.query.get(1)
>>> monitors = [Monitor.query.get(3), Monitor.query.get(4)]
>>> u.append(monitors)
>>> print u.monitors
[<Monitor u'HP'>, <Monitor u'Dell'>]
>>> db.session.commit()
>>> print u.monitors
[<Monitor u'HP'>, <Monitor u'Dell'>]

However, if I try and append more than one of the same monitors to a UserAudit object, only one monitor gets stored.

Example:

>>> u = UserAudit.query.get(2)
>>> monitors = [Monitor.query.get(3), Monitor.query.get(3)]
>>> u.append(monitors)
>>> print u.monitors
[<Monitor u'HP'>, <Monitor u'HP'>]
>>> db.session.commit()
>>> print u.monitors
[<Monitor u'HP'>] # ONLY ONE MONITOR GOT SAVED HERE!!!

How do I configure the UserAuditMonitor class to save duplicates?

Thanks!

Upvotes: 2

Views: 1265

Answers (1)

munchybunch
munchybunch

Reputation: 6153

My guess is that UserAuditMonitor doesn't have an explicitly defined primary key, so SQLA is using user_audit_id x monitor_id as the key and de-duplicates the relationship. Perhaps try adding an autoincrement primary key to the association_table?

Upvotes: 1

Related Questions