SQLite's default ON CONFLICT algorithm

[Gerhard Häring]

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>.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