CODEWITHSUNDEEP
flasksqlalchemymany-to-manyflask-sqlalchemy
Till
Till

Reputation: 323

SQLAlchemy many-to-many relationship updating association object with extra column

In my application, a 'set' can have a number of 'products' associated with it. Products listed against a set must have quantities defined. For this many-to-many relationship I have followed the SQLAlchemy documentation to use an association table with an additional column (quantity).

I am trying to create a form where the user can assign products and quantities against a given set. Both the sets and products already exist in the database. The data from the form are:

This works to create a new association (e.g. set 1 is 'linked' to product 3 with quantity=XYZ) but I get an integrity error when I try to update an existing record.

I can manually add a relationship/record (dummy data) or within the Flask view function as follows:

s = Set.query.get(2)
p = Product.query.get(3)
a = Set_Product_Association(set=s, product=p, quantity=23)
db.session.add(a)
db.session.commit()

Updating the record (different quantity) manually as follows works:

s.products[0].quantity = 43
db.session.add(s)
db.session.commit()

However when I use the code from the first block instead (with the aim to update the quantity field for a given, existing set and product ID), i.e.:

a = Set_Product_Association(set=s, product=p, quantity=43)

I get an integrity error

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: set_product_association.set_id, set_product_association.product_id [SQL: 'INSERT INTO set_product_association (set_id, product_id, quantity) VALUES (?, ?, ?)'] [parameters: (2, 3, 43)]

I assume this is to tell me that I'm trying to append a new record rather than updating the existing one.

How should I approach this? The 'manual' method works but relies on working out the correct index in the list (i.e. for the correct product.id).

Curiously, if I use form.popluate_obj(set) in my Flask view function to process the form data as described in my question here, I can update fields but not create new 'associations'. Unfortunately, I don't know what goes on behind the scenes there....

My models are defined like so:

class Set_Product_Association(db.Model):
    __tablename__ = 'set_product_association'

    set_id = db.Column(db.Integer, db.ForeignKey('sets.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), primary_key=True)

    quantity = db.Column(db.Integer)

    product = db.relationship("Product", back_populates="sets")
    set = db.relationship("Set", back_populates="products")

class Set(db.Model):
    __tablename__ = 'sets'

    id = db.Column(db.Integer, primary_key=True)
    products = db.relationship("Set_Product_Association", 
                                        back_populates="set")

class Product(db.Model):
    __tablename__= 'products'

    id = db.Column(db.Integer, primary_key=True)
    part_number = db.Column(db.String(100), unique=True, nullable=False)
    sets = db.relationship("Set_Product_Association", 
                                     back_populates="product")

Edit: I've also tried reversing the operation as suggested here:

s = Set.query.get(2)
a = Set_Product_Association()
a.quantity = 43
a.product = Product.query.get(3)
a.set = s
db.session.commit()

But I still get an error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: set_product_association.set_id, set_product_association.product_id [SQL: 'INSERT INTO set_product_association (set_id, product_id, quantity) VALUES (?, ?, ?)'] [parameters: (2, 3, 43)]

Upvotes: 3

Views: 4048

Answers (1)

Thomas Gak-Deluen
Thomas Gak-Deluen

Reputation: 2921

You get an integrity error because you are trying to create a new object with the same primary keys.

This:

a = Set_Product_Association(set=s, product=p, quantity=43)

Does not update, but create.

If you want to update the actual row in the table, you need to update the existing one:

assoc = Set_Product_Association.query.filter_by(set=s, product=p).first()
assoc.quantity = 43
db.session.commit()

Also, from the documentation it is advised to not use a model but an actual table.

Upvotes: 7

Related Questions