Re: [pysqlite] repeatable reads with pysqlite?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Phil Budne wrote:
> I started a project using apsw, since I hadn't known about lastrowid
> in DB-API (or it's support in pysqlite).

One thing I don't like about the DB-API spec is that lastrowid is part
of the Cursor and not the Connection.  It is really part of the database
and there are race conditions with other cursors on the same Connection
as well as other processes that may have the database open.  The way I
deal with this is:

  lastrow=None
  for row in cursor.execute("begin; insert blah blah ; select
last_insert_rowid(); commit"):
    lastrow=row[0]

> (and installation of apsw under both FreeBSD "ports" and Mac "MacPorts" failed)

I am having a look at the former to see what they did.  You don't need
to use those ports things.  For the record this is how hard it is to
install apsw with the latest version of SQLite (latest at the time of
running the command, not latest as when apsw was released):

$ wget http://apsw.googlecode.com/files/apsw-3.6.3-r1.zip
$ unzip apsw-3.6.3-r1.zip
$ cd apsw-3.6.3-r1
$ /path/to/python setup.py --fetch-sqlite install

There is more detail in the building section at
http://apsw.googlecode.com/files/apsw-3.6.3-r1.html

> However, I've found that sqlite3 has it's
> own ideas about issuing BEGIN statements, which can cause execute()
> calls to fail.

For some reason I never understood, pysqlite tries to do transaction
management behind your back.  The good news is that you can supply
isolation_level=None in the connect() call and it will stop doing all that.

Also be aware that SQLite does not support nested transactions (unlike
other databases).  IMHO if you are using SQLite then you must manage
transactions yourself.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkj+cd8ACgkQmOOfHg372QR1HQCgmxSjC0I3PGUFtnu6yYUKeYeQ
G7UAoNnAh7Wd3X5CNdqvGy6Z5/jWhsoV
=YldM
-----END PGP SIGNATURE-----