Re: [sqlite] What does commit actually do?
The manual says this.
"The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement (i.e.
INSERT/UPDATE/DELETE/REPLACE)."
> If you did NOT specify "isolation_level = None" in the .connect() call then
> you probably ARE in a transaction if you've been running queries.
I just use the default .connect() without specifying isolation_level
explicitly. Then I am in a transaction?
import sqlite3
conn=sqlite3.connect(f)
c=conn.cursor()
import zlib
c.execute('SELECT name FROM sqlar')
result = c.fetchall()
for x in result:
print(x[0])
> But why do I need to commit my transaction if I'm just reading?
Is there any .commit() implied in the SELECT statement?
> If you're just reading then you can commit or rollback.
So I should close the `conn` immediately after `c.fetchall()` to
release resources used by sqlite3?
> If the database is not in WAL mode
In the above example, should I use WAL mode or not?
https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/
--
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does commit actually do?
You can tell if you are in a transaction by the in_transaction property of the
connection. This tracks whether or not a BEGIN of some kind has been issued by
the wrapper (either through magic or because you explicitly issued a command to
BEGIN a transaction). If in_transaction is True, then you need to issue a
commit or rollback to end that transaction. This property reflects the what
the WRAPPER thinks is the transaction state, and not the underlying SQLite3
database transaction state.
So, if you are issuing multiple SELECTs and you need repeatable read for these
SELECTs, then you need to issue the BEGIN explicitly.
If you want something that allows Python to use SQLite3 as SQLite3 (rather than
as a dbapi compliant generic database) then you should look at APSW instead.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Peng Yu
>Sent: Friday, 25 October, 2019 14:13
>To: SQLite mailing list
>Subject: Re: [sqlite] What does commit actually do?
>
>The manual says this.
>
>"The Python sqlite3 module by default issues a BEGIN statement
>implicitly before a Data Modification Language (DML) statement (i.e.
>INSERT/UPDATE/DELETE/REPLACE)."
>> If you did NOT specify "isolation_level = None" in the .connect() call
>then
>> you probably ARE in a transaction if you've been running queries.
>
>I just use the default .connect() without specifying isolation_level
>explicitly. Then I am in a transaction?
>
>import sqlite3
>conn=sqlite3.connect(f)
>c=conn.cursor()
>import zlib
>c.execute('SELECT name FROM sqlar')
>result = c.fetchall()
>for x in result:
> print(x[0])
>
>> But why do I need to commit my transaction if I'm just reading?
>
>Is there any .commit() implied in the SELECT statement?
>
>> If you're just reading then you can commit or rollback.
>
>So I should close the `conn` immediately after `c.fetchall()` to
>release resources used by sqlite3?
>
>> If the database is not in WAL mode
>
>In the above example, should I use WAL mode or not?
>
>https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/
>
>--
>Regards,
>Peng
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does commit actually do?
"I just use the default .connect() without specifying isolation_level explicitly. Then I am in a transaction?" E, umm, well. Even regular users may not know the answer to that do to "automatic stuff behind the scenes" which may have corner cases or maybe bad documentation. Which is why we prefer to use the "isolation_level = None" at connection time to be absolutely sure. If you don't want to deal with that you can always just run .commit() anyway just to make sure. If you weren't in a transaction it won't complain, and you'll be sure to be no longer in a transaction immediately afterwards. "Is there any .commit() implied in the SELECT statement?" Nope. "So I should close the `conn` immediately after `c.fetchall()` to release resources used by sqlite3?" If you're done reading from the database then it never hurts. If you're gonna go back and do more queries then you can keep the connection live which will keep things cached. Just don't be in a transaction or you'll be blocking anyone else trying to write. If you're the only one ever using that file then all this is a moot point though as there's no one else to block. "In the above example, should I use WAL mode or not?" If you're just getting started with SQLite then probably don't worry about it for now, especially if everything is working fine with the default mode. You can look at it later for fun. https://www.sqlite.org/wal.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does commit actually do?
"https://docs.python.org/2/library/sqlite3.html; Also, please consider going to Python 3 instead of 2. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does commit actually do?
This question could span the entire range of experience level from beginner to expert. When you're in a transaction, .commit() says "make everything done in this transaction on this connection permanent." If there are any crashes or improper disconnections in the middle of a transaction then nothing you did in the transaction will persist in the database, it will all be rolled back. If you haven't made any changes then it's still fine to run, it'll just say "that was easy, nothing for me to save." Are you in a transaction? (Python library specific question) See Keith's message for a longer version. But in short... If you did NOT specify "isolation_level = None" in the .connect() call then you probably ARE in a transaction if you've been running queries. If you DID then you're usually NOT in a transaction, unless you explicitly started one (in which case you probably know about transactions). But why do I need to commit my transaction if I'm just reading? If you're just reading then you can commit or rollback. Either is fine. Just don't leave the transaction lingering around still open because someone else may want to write to the database, or to do a checkpoint. If the database is not in WAL mode then their attempt to write will have to wait for your read transaction to finish before it can do any writing. If the database IS in WAL mode, then writers can commit ok, but checkpointing of the WAL will be blocked from the point you started your transaction. You also will not see in your own queries any of those changes from writers until you start a new transaction. (Which may or may not be what you want) Under the hood in the Python library I think all it does is issue a "commit;" command, and suppress any exception raised because you weren't in a transaction already. -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Friday, October 25, 2019 12:44 PM To: SQLite mailing list Subject: [sqlite] What does commit actually do? The python manual just tell me what I should do but it is not very clear what commit() actually does under the hood. https://docs.python.org/2/library/sqlite3.html """ commit() This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method. """ So, only if I want to write something to the db, I need to call commit()? If I just read something from the db, there is no need to call commit()? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does commit actually do?
On Friday, 25 October, 2019 10:44, Peng Yu wrote: >The python manual just tell me what I should do but it is not very >clear what commit() actually does under the hood. >https://docs.python.org/2/library/sqlite3.html >""" >commit() >This method commits the current transaction. If you don’t call >this method, anything you did since the last call to commit() is not >visible from other database connections. If you wonder why you don’t >see the data you’ve written to the database, please check you didn’t >forget to call this method. >""" commit() returns the database to autocommit mode ending the transaction that was commenced with a begin command (it issues a COMMIT to the underlying sqlite3 database). If the wrapper is operating in "magical mode" then statements which might update the database might be preceded with a magical begin statement (with a probability somewhere between 0% and 100% of the time). See the isolation_level parameter when opening a connection for a description of the magical incantation modes available. The text you quoted above assumes that magical mode is in effect. By default the 'DEFERRED' magic mode is in effect. You can turn off magical mode by using isolation_level=None when opening the connection, in which case you must BEGIN and COMMIT transactions yourself (the magical faery dust is turned off). When the magical mode is turned off, each statement is executed in its own transaction in autocommit mode, or inside an explicit transaction which you BEGIN and COMMIT/ROLLBACK. >So, only if I want to write something to the db, I need to call >commit()? If I just read something from the db, there is no need to >call commit()? If you write to the database, and magical mode is enabled (isolation_level is any value other than None), and the wrapper faery managed to figure out that you were writing to the database and therefore issued a BEGIN, then you must commit the transaction in order to end the transaction that the magical transaction faery started for you. Also, if you commenced a transaction with an explicit BEGIN command, then you must COMMIT that transaction in order for it to be visible to anyone else. When a transaction is in process outstanding transactions are rolled back when the connection is closed. -- 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
