Re: SQLite's default ON CONFLICT algorithm

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

egbert wrote:

Yes, I know that this is off-topic, but I feel justified by sqlite3
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 ?

No, it's just like every other database error - the command fails but
the connection is left untouched.

After all, you expect that either all changes within a transaction
are preserved, or that nothing at all is preserved.

Sure, the successful ones ;-)

The Python Library Reference on sqlite3 says in paragraph 13.13.5
that I should not use the ROLLBACK conflict algorithm in my sql.
Instead I have to catch the IntegrityError and call the rollback method.

FWIW, this restriction is not any longer true in Python 2.6 and 3.0 btw.
You can now safely use "ON CONFLICT ROLBLACK" with the sqlite3 module.

Does that mean that I have to wrap all multi-command transactions
in a try-except if I don't like the default ABORT choice ?

Well, you have to do that *always* anyways if you want your app to
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>.Databas eError:
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