On Wednesday, 29 January, 2020 22:45, Peng Yu <pengyu...@gmail.com> wrote:
>In python sqlite3 program, if I call .execute() multiple times then
>call .commit(). Does it ensure that all the sqlite3 commands specified
>by execute()'s either all take effect or none effect?
Mayhaps yes, mayhaps no. .commit() is merely syntactic sugar for
.execute('COMMIT')
Basically, the method does the following:
if (sqlite3_get_autocommit(connection) == 0) {
sqlite3_stmt* stmt = 0;
sqlite3_prepare(connection, "COMMIT", -1, &stmt, NULL);
if (stmt) {
sqlite3_step(stmt);
sqlite3_finalize(stmt);
}
}
That is, if there is a transaction in progress on the connection, it does a
commit. Mutatis mutandis for .rollback() doing a 'ROLLBACK' rather than commit.
It makes sure that all changes made in the current transaction are committed to
the database. If you are using "magical mode" then hopefully a transaction was
started when you updated something however this is not guaranteed (such is the
nature of magic). The "magical mode" of the sqlite3 wrapper decides when to
BEGIN and COMMIT transactions on its own. Sometimes the magician is a little
daffy though and gets it wrong so it may begin/commit/rollback whenever the
magician feels like it, which may or may not be when you want.
If you want to ensure that transactions BEGIN and COMMIT/ROLLBACK when *you*
want them to then use explicit .execute('BEGIN') (or .execute('BEGIN
IMMEDIATE') to start an immediate transaction) command to start a transaction
on a connection opened with isolation_level=None. You can still use .commit()
for .execute('COMMIT') and .rollback() for .execute('ROLLBAC') if you want.
The connection has a property in_transaction that lets you test whether a
transaction is in progress (True) or the database is in autocommit (False) mode.
>In other words, if any error occurs while running the sqlite3 commands
>specified in execute(), what happens?
That depends on the nature of the error. If you put in a bad SQL statement
(got a syntax error) then that statement did nothing. Otherwise it depends on
the conflict resolution method in effect for the statement causing the error,
which is usually ABORT (by default) and it just ABORTs the current statement.
>Also, is .executemany() the same as calling .execute() many times?
Yes. x.executemany(sql, stuff) is syntactic sugar for
for e in stuff:
x.execute(sql, e):
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
