Trying to understand many-to-many relationship in flask

Question

Currently iam working on a project in python with flask. I need a many to many relationship in my database and i used this simple example:

https://www.digitalocean.com/community/tutorials/how-to-use-many-to-many-database-relationships-with-flask-sqlalchemy

Now my problem is, that everytime i add a post with tags, the tag will be added in the tag table (even if it already exists) and link that in the "relationship" table... But i wanna only add the link in the "relationship" table. What am i doing wrong?

double entry in tag table

Code to add new post

Also how would the edit_post page look like? Just found the append and delete method.

Everything found in the posted tutorial

Answer 1

The example you use as a base is very simple and explains the basics of defining tables for a many-to-many relationship. As you've probably already noticed, it can be expanded.

Basically, you want the name of a tag to be unique. For this reason you should mark it that way.

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

Furthermore, you want a specific tag to be assigned only once per post. For this it makes sense to declare both columns as primary keys. Since a primary key is unique to a table, you ensure that a combination of both columns only appears once.
To ensure that all entries are deleted from the association table when a referenced entry is deleted, I also recommend defining an additional cascade for the foreign key columns.

post_tag = db.Table('post_tag',
    db.Column('post_id', 
        db.Integer, 
        db.ForeignKey('post.id', ondelete='CASCADE'), 
        primary_key=True
    ),
    db.Column('tag_id', 
        db.Integer, 
        db.ForeignKey('tag.id', ondelete='CASCADE'), 
        primary_key=True
    )
)

With these extensions to the example, an error is thrown if one of the conditions is not met when adding an entry to the database table. It doesn't do the work for you to check whether an entry already exists in the database. So if you want to create a new post, it is your job to check whether a tag already exists or needs to be created.

for tag in set(form.tags.data):
    tag = Tag.query.filter_by(name=tag).first()
    if not tag:
        tag = Tag(name=tag)
    post.tags.append(tag)

Databases and their structure are such a comprehensive topic that we can only touch on them here. The deeper you delve into the subject matter, the more customizations you will want to make to the table definitions and your code.