Assuming a many to many relationship (sqlalchemy, python), how to avoid adding duplicates?

The tables authors and books are in a many to many relationship. A book can have many authors. An author has possibly written many books.

author_1 = Author('Dan')
session.add(author_1)
author_1.books = [Paper('Illuminati'), Book('Sacrileg')]
session.commit()

This adds two books and an author to the database and links them. So far everything is fine. both columns (authors.name and books.title) in the different tables are unique.

Let's add a new author, and let's assume Dan had some help writing Illuminati.

author_2 = Author('Brownie')
session.a开发者_C百科dd(author_2)
author_2.books = [Paper('Illuminati')]
session.commit()

This results in a duplicate Error! Why is that? Do I have to query for Paper('Illuminati') first? What if I would have a whole list of books? Would I Have to query every single one of them? Or is there a automatic function, how sqlalchemy could determine if an entry is already there and just link to it?

This is basically the long version of @renatopp comment

This results in a duplicate Error! Why is that?

You create a new book/paper that is similar to the one you have in the db. I take it you set paper/book's name with unique constraint

Do I have to query for Paper('Illuminati') first?

Let's see what you said before

Let's add a new author, and let's assume Dan had some help writing Illuminati.

This new author helps Dan write Illuminati. The same Illuminati that is already on the db. Your code there means "Brownie writes a new book that has the same name like Dan's". So there are two books, hence duplication error.

If you want "Brownie helping Dan", then you have to get him write the same book. Get Dan's Illuminati from the db, and tell Brownie to help Dan.

    # illuminati = Dan's book from db
    author_2.books.append(illuminati)

I change the code there. Correct me if Im wrong, I think you meant adding a book to Brownie, not removing all his books and give him a book afterward (author_2.books = [Paper('Illuminati')])

What if I would have a whole list of books? Would I Have to query every single one of them?

That depends. Are they all new books? Or existing books? Maybe only some of them new? Or you dont know at all?

You'll have to check them, or try catching the exception