CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Logovskii Dmitrii
Logovskii Dmitrii

Reputation: 2973

Sqlalalchemy postgresql insert statement ignore duplicates

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"

Upvotes: 1

Views: 1262

Answers (1)

Stearate
Stearate

Reputation: 56

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()

Upvotes: 4

Related Questions