How to ignore primary key errors on insert

[Alexis B]

Hi to all,

This may be a newbie question but I just can't find the answer. I have
to make multiple submissions to a postgresql table and I want to use
python. I found everything to execute my insert commands, the problem
is that I have to repeat it regularly, and I expected not to check
which record have already inserted thanks to the primary key ( which
is a couple if integer I set ).
So when it tries to insert again some records, it doesn't insert it,
as expected, but it raises an error which interrupt the script :

**************************************************
Traceback (most recent call last):
File "./script.py", line 44, in <module>
connection.execute(line)
File " . . ./python-2.5.1/lib/python2.5/site-packages/
SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 844, in
execute
return Connection.executors[c](self, object, multiparams, params)
File ". . ./python-2.5.1/lib/python2.5/site-packages/
SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 854, in
_execute_text
self.__execute_raw(context)
File ". . ./python-2.5.1/lib/python2.5/site-packages/
SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 916, in
__execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File ". . ./python-2.5.1/lib/python2.5/site-packages/
SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 960, in
_cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File ". . ./python-2.5.1/lib/python2.5/site-packages/
SQLAlchemy-0.4.5-py2.5.egg/sqlalchemy/engine/base.py", line 942, in
_handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) duplicate key
violates unique constraint "my_primarykey"
"INSERT INTO . . . )" {}
**************************************************

So I just wanted to know if there was an option to ignore the error ,
which possibly raise it, but don't interrupt the script.

Thanks

[Michael Bayer]

just use try/except

from sqlalchemy import exceptions

try:
engine.execute(mytable.insert(), **kwargs)
except exceptions.IntegrityError, e:
print "Error !", e, "well, I guess we'll ignore it."

engine.execute(some_other_insert_statement ... )

[Alexis B]

Oh right, I'll do this . ( I was looking so hard on postgres features
that I forgot the python ones ... )

Thanks

[Hardik Sanghavi]

Did this get resolved or are we to still ignore it 

[Simon King]

If you are using SQLAlchemy core, there's this:

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert

Does that meet your needs?

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

There's also the strategy of doing something within a nested transaction, which will allow you to rollback on an integrity error.

such as...

try:

with s.begin_nested():

    # do stuff

    s.flush()  # this will trigger an integrity error, unless the fkey checks are deferred

except exceptions.IntegrityError:

pass