CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
you
you

Reputation: 303

Flask SQLAlchemy Many To Many Insert Duplicates Entries

I have created a Flask-SQLAlchemy API that contains a many-to-many relationship of Items to Tags.

A simple add works with no problems (inserts item and tags in appropriate tables), but if I add another item with the same tag names it creates a duplicate set of tag names instead of just linking the association table to the existing tag entry.

I found some situations that are pretty close to what I am asking, but not exactly the same and I am really struggling trying to understand the best way to accomplish this.

So the question is: what is the best way to have only unique entries in the tags table while still correctly associating tags with the items table?

Thank you for any help/guidance!

Here are my models:

items_tags = db.Table('items_tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('item_id', db.Integer, db.ForeignKey('item.id'))
)

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), unique=False)
    code = db.Column(db.String(), unique=False)
    notes = db.Column(db.String(), unique=False)

    tags = db.relationship('Tag', secondary=items_tags, back_populates="items")

    def json(self):
        list_tag = []
        for tag in self.tags:
            list_tag.append({"tag": tag.name, "id": tag.id })
        return json.loads(json.dumps({"id": self.id, "title": self.title, "code": self.code, "notes": self.notes, "tags" : list_tag }))

    def __init__(self, title, code, notes, tags):
        self.title = title
        self.code = code
        self.notes = notes        
        self.tags = tags

    def __repr__(self):
        return self.title

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    items = db.relationship('Item', secondary=items_tags, back_populates="tags")

    def json(self):
        return {'id': self.id, 'tag': self.name}

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

    def __repr__(self):
        return self.name

Here is the controller:

# curl -k -H "Content-Type: application/json" -X POST https://x.x.x.x/api/v1.0/items/ -d '{"title": "title03", "code": "code03", "notes": "notes03", "tags": [{"tag": "tag01"}, {"tag": "tag02"}]}'
@app.route('/api/v1.0/items/', methods=['GET', 'POST'])
def items():
    if request.method == 'GET':
        return jsonify({"items": [item.json() for item in Item.query.all()]})

    if request.method == 'POST':
        if 'tags' in request.json:
            tags = request.json['tags']    
            for tag in tags:
              list_tags = [Tag(tag['tag']) for tag in tags]

        item = Item(request.json['title'], request.json['code'], request.json['notes'], list_tags)
        db.session.merge(item)
        db.session.commit()
        return jsonify({}), 201

Upvotes: 5

Views: 2144

Answers (1)

you
you

Reputation: 303

I was missing the fact that I needed to query to see if the tag existed first (what @dirn was getting at with "change how you do that to load existing records").

Here is what I did to make sure duplicate tags were not added:

if request.method == 'POST':
    if 'tags' in request.json:
        list_tags = []
        for tag in request.json['tags']:
            if Tag.query.filter_by(name=tag['tag']).first():
                list_tags.append(Tag.query.filter_by(name=tag['tag']).first())
            else:
                list_tags.append(Tag(tag['tag']))

    item = Item(request.json['title'], request.json['code'], request.json['notes'], list_tags)
    db.session.merge(item)
    db.session.commit()
    return jsonify({}), 201

Upvotes: 3

Related Questions