Re: Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

Could you show a python example on how to make multiple entries either
being all inserted (each entry done by an insert statement) or none on any
error (e.g. ctrl-c)? I also want want to make sure no two python processes
simultaneously editing these entries.

I am not sure I fully understand how to implement this correctly.

On Thu, Jan 30, 2020 at 12:43 AM Keith Medcalf <[email protected]> wrote:

>
> On Wednesday, 29 January, 2020 22:45, Peng Yu <[email protected]> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users