Yes, I know that this is off-topic, but I feel justified by sqlite3No, it's just like every other database error - the command fails but
being a builtin.
The default ON CONFLICT algorithm in SQLite is ABORT.
The SQLite documentation ("ON CONFLICT clause") says that when
a constraint violation occurs under ABORT, no rollback is executed,
so changes from prior commands within the same transaction are
preserved.
Isn't this a strange choice for a default ?
the connection is left untouched.
After all, you expect that either all changes within a transactionSure, the successful ones ;-)
are preserved, or that nothing at all is preserved.
The Python Library Reference on sqlite3 says in paragraph 13.13.5FWIW, this restriction is not any longer true in Python 2.6 and 3.0 btw.
that I should not use the ROLLBACK conflict algorithm in my sql.
Instead I have to catch the IntegrityError and call the rollback method.
You can now safely use "ON CONFLICT ROLBLACK" with the sqlite3 module.
Does that mean that I have to wrap all multi-command transactionsWell, you have to do that *always* anyways if you want your app to
in a try-except if I don't like the default ABORT choice ?
behave correctly. Typical usage of the DB-API looks like this:
cur = con.cursor()
try:
cur.execute(...)
cur.execute(...)
cur.execute(...)
con.commit()
except <DB-API-MODULE>.DatabaseError:
con.rollback()
With the sqlite3 module, there's a shortcut:
from __future__ import with_statement
with con:
cur.execute(...)
cur.execute(...)
cur.execute(...)
which does exactly the same as the code above.
-- Gerhard