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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Peng Yu
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?

In other words, if any error occurs while running the sqlite3 commands
specified in execute(), what happens?

Also, is .executemany() the same as calling .execute() many times?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf

On Wednesday, 29 January, 2020 22:45, Peng Yu <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Peng Yu
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 <[hidden email]> wrote:

>
> On Wednesday, 29 January, 2020 22:45, Peng Yu <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

David Raymond
-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 5:16 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] 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.



Using the manual transactions (isolation_level = None) it might look something along the lines of this:
<Braces for impact of comments on my style>


import sqlite3
import contextlib

conn = sqlite3.connect(myFile, isolation_level = None)
try:
    with contextlib.closing(conn.cursor()) as cur:
        #stuff
        cur.execute("begin;")
        try:
            for thing in otherThing:
                cur.execute("insert into myTable values (?,?,?);", thing)
        except:
            conn.rollback()
            print("Error message here")
            return someCode #or re-raise the exception
        else:
            conn.commit()
finally:
    conn.close()
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Peng Yu
Thanks. What is the purpose of contextlib.

If I just use `cur = conn.cursor()`, what problems it will cause?

>     with contextlib.closing(conn.cursor()) as cur:

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

David Raymond
That's just my own personal paranoia wanting to make sure the cursor always gets closed, even on an exception. As I don't think the normal context manager on a cursor closes it when it exits.

In the real world it's probably overkill as
a) The destructors probably take care of that
b) It's the connection you're more worried about and not the cursor
c) SQLite is designed to handle crashes, etc.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Thursday, January 30, 2020 11:02 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

Thanks. What is the purpose of contextlib.

If I just use `cur = conn.cursor()`, what problems it will cause?

>     with contextlib.closing(conn.cursor()) as cur:

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users