Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.xs4all.nl!newsfeed3a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: How to use SQLite (sqlite3) more efficiently
Date: Thu, 05 Jun 2014 20:42:16 -0400
Organization: IISS Elusive Unicorn
References: <5390ABF2.5020805@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.10793.1402015344.18130.python-list@python.org>
Lines: 52
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:72801

On Thu, 05 Jun 2014 13:42:10 -0400, R Johnson
<ps16thypresenceisfullnessofjoy@gmail.com> declaimed the following:


> >        While /maybe/ not required for a SELECT operation, I'd put a
> >conn.commit() somewhere in there before the return(s). The standard for
> > Python DB-API interfaces is that auto-commit is turned off -- meaning the
> > SELECT has started a database transaction.
>
>I don't exactly understand why conn.commit() should be called there. I 
>thought it's only necessary to call it when the database has been 
>changed, which a SELECT call doesn't do. Am I misunderstanding something 
>here?
>

	For a single process accessing the database, maybe not... But DB-API
does not do auto-commit, which means the select probably starts a
transaction, putting SQLite3 into a shared-read lock. Any other
connection/cursor that tries to commit a change will be blocked waiting for
all the shared-read connections to commit or rollback.

http://www.sqlite.org/lockingv3.html
{Note: while SQLite3 default is auto-commit, the DB-API spec for Python
says NO auto-commit, so opening a connection should implicitly also turn
off auto-commit}

https://pysqlite.readthedocs.org/en/latest/sqlite3.html#controlling-transactions
Does imply that it only starts a transaction on a statement that will
change the database contents... But I'd prefer to play safe -- after all,
if one later changes to a different DBMS, the transaction start point may
be different.


> > >        with conn:
> >
> >         This isn't really doing anything useful. You aren't opening a new
> > connection object, so there isn't really anything to close on block 
>exit.
>
>See 
>https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager. 
>I removed it from my code, though, because it doesn't really seem necessary.
>
	Okay -- mea culpa... I'd not encountered that style of "context
manager"... I'm used to something that uses the "with" to "open" an object,
and the context exit closes said object. Not something where the object
hangs around in a usable state, but an implicit commit() is performed.

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/