Sqlalchemy add_all() ignore duplicate key IntegrityError

Question

I'm adding a list of objects entries to a database. Sometimes it may happen that one of this objects is already in the database (I do not have any control on that). With only one IntegrityError all the transactions will fail, i.e. all the objects in entries will not be inserted into the database.

try:
    session.add_all(entries)
    session.commit()
except:
    logger.error(f"Error! Rolling back")
    session.rollback()
    raise
finally:
    session.close()

My desired behavior would be: if there is a IntegrityError in one of the entries, catch it and do not add that object to the database, otherwise continue normally (do not fail)

Edit: I'm usign MySQL as backend.

Answer 1

I depends on what backend you're using.

PostgreSQL has a wonderful INSERT() ON CONFLICT DO NOTHING clause which you can use with SQLAlchemy:

from sqlalchemy.dialects.postgresql import insert
session.execute(insert(MyTable)
                .values(my_entries)
                .on_conflict_do_nothing())

MySQL has the similar INSERT IGNORE clause, but SQLAlchemy has less support for it. Luckily, according to this answer, there is a workaround, using prefix_with:

session.execute(MyTable.__table__
                .insert()
                .prefix_with('IGNORE')
                .values(my_entries))

The only thing is that my_entries needs to be a list of column to value mappings. That means [{ 'id': 1, 'name': 'Ringo' }, { 'id': 2, 'name': 'Paul' }, ...] et cetera.

Answer 2

A solution I have found is to query the database before adding it

try:
    instance = session.query(InstancesTable).filter_by(id=entry.id).first()
    if instance:
        return
    session.add(entry)
    session.commit()
except:
    logger.error(f"Error! Rolling back")
    session.rollback()
    raise