Sqlalalchemy postgresql insert statement ignore duplicates

Question

I am trying to bulk insert data to postgres using SQLAlchemy, but can't find out how to say it to ignore duplicates values by unique constraint

My code

from sqlalchemy.dialects.postgresql import insert

stmt = insert(User, inline=True)

values = [{"id": 1, "name": "David"}, {"id": 2, "name": "Cris"}]

session.execute(stmt, values)
session.commit()

Got such error

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "uq_users_name"

Stearate Stearate · Accepted Answer · 2019-08-30T10:27:52

By default sqlalchemy doesn't support ignore_duplicates option, so we need to add custom method to sqlalchemy query compiler by simply adding ON CONFLICT DO NOTHING string to the end of the query

from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.ext.compiler import compiles

@compiles(Insert, 'postgresql')
def ignore_duplicates(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    ignore = insert.kwargs.get('postgresql_ignore_duplicates', False)
    return s if not ignore else s + ' ON CONFLICT DO NOTHING'

Insert.argument_for('postgresql', 'ignore_duplicates', None)


stmt = insert(User, postgresql_ignore_duplicates=True, inline=True)

values = [{"id": 1, "name": "David"}, {"id": 2, "name": "Cris"}]

session.execute(stmt, values)
session.commit()

Sqlalalchemy postgresql insert statement ignore duplicates

1 Answers