pysqlite multiple connections

[Gerhard Häring]

Tertius wrote:
> How can I manage multiple connections or cursors with
>  pysqlite? (ver 0.4.1)

> import sqlite
> 
> cnct1 = sqlite.connect(db="/tmp/db")
> crsr1 = cnct1.cursor()
> print "cnct1:", cnct1
> print "crsr1:", crsr1
> 
> cnct2 = sqlite.connect(db="/tmp/db")
> crsr2 = cnct2.cursor()
> print "cnct2:", cnct2
> print "crsr2:", crsr2

I know this code doesn't work as expected.

.cursor() automatically opens a transaction, and an open transaction 
locks the database file. cnct2.cursor() tries to open another 
transaction and SQLite tries to grab the lock on the file, which is 
already locked. So this fails.

I don't recommend to use PySQLite in a multiuser context, and I see no 
reason why you'd need multiple connections in a singlethreaded app, 
anyway. So the best solution might be to create a single database 
connection, if feasible.

Anyway, here's something that might work better for you:

#v+
import sqlite

cnct1 = sqlite.connect(db="/tmp/db", autocommit=True)
cnct1.db.sqlite_busy_timeout(5000)
crsr1 = cnct1.cursor()
print "cnct1:", cnct1
print "crsr1:", crsr1

cnct2 = sqlite.connect(db="/tmp/db", autocommit=True)
cnct1.db.sqlite_busy_timeout(5000)
crsr2 = cnct2.cursor()
print "cnct2:", cnct2
print "crsr2:", crsr2
#v-

This will

a) open the connection in autocommit mode. You don't have any 
transactions, then. This might be ok for simple use cases, but for many 
real-life db apps, transactions are a must.

b) If two connections try to hold the lock at the same time (with 
autocommit=True, this can really only happen in a 
multi-process/multi-thread environment), the SQLite engine immediately 
throws an error by default. You can change this behaviour, though. The 
low-level call sqlite_busy_timeout sets the interval how long SQLite 
will try to grab the lock before giving up.

We've received a patch today for adding the timeout as an optional 
parameter to the module-level connect function. This patch makes sense 
to me and I'll apply it to CVS soon.

-- Gerhard