egbert wrote:
No, it's just like every other database error - the command fails but
the connection is left untouched.
Sure, the successful ones ;-)
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.
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
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 ?
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 transaction
are preserved, or that nothing at all is preserved.
are preserved, or that nothing at all is preserved.
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.
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 transactions
in a try-except if I don't like the default ABORT choice ?
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>.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