[pysqlite] pysqlite2.dbapi2.OperationalError: cannot commit transaction - SQL statements in progress

[F. GEIGER]

I've troubles to let my app take off using pysqlite.

What I wonder most for now is that "pysqlite2.dbapi2.OperationalError:
cannot commit transaction - SQL statements in progress" when I do this:

t = time.time()
n = len(self)
while len(self):
del self[0]
self.commit()
print "%d items deleted in %.3f secs. " % (n, time.time() - t)

self is of class DbTable which lets db tables be used like ordinary Python
objects (after an idea of Scott Scriven):

def __len__(self):
self._query_("select count(*) from %s %s" % (self._name,
self._whereClause))
r = int(self._dbc.fetchone()[0])
return r

and

def __delitem__(self, i):
'''Enables you to remove rows this way: del movies[58]
'''
q = "select %s from %s %s %s limit %s, 1" % (self._nameOfIdCol,
self._name, self._whereClause, self._orderClause, i)
self._query_(q)
rid = self._dbc.fetchone()[0]
q = "delete from %s where %s='%s'" % (self._name, self._nameOfIdCol,
rid)
self._query_(q)
return

So it boils down to SELECT and DELETE statements called in a loop. After
that a commit is made.

What does pysqlite try to tell me here?
Kind regards
Franz GEIGER
PS.: I use pysqlite 2.0.1 on Python 2.3.4

[Gerhard Haering]

On Wed, May 18, 2005 at 09:41:39PM +0200, F. GEIGER wrote:

I've troubles to let my app take off using pysqlite.

What I wonder most for now is that "pysqlite2.dbapi2.OperationalError:
cannot commit transaction - SQL statements in progress" when I do this:

Urgh! I would have preferred simplified demo code. But after a little
thinking, I guessed right.
[...]

pysqlite 2 currently has problems doing .commit() .rollback() on the
connection object after a cur.execute("select ...") in a few cases.

I know why and I will fix it in 2.0.2 (originally only MacOS X fixes
planned) or if it takes longer to test, in 2.0.3.

Current workarounds:

- don't commit/rollback after SELECT. It doesn't make much sense anyway.
.commit()/.rollback() after your DML statements instead
(INSERT/UPDATE/DELETE).

- Use fetchall()[0] for fetching single rows instead of fetchone() for
queries like your SELECT COUNT.

-- Gerhard
--
Gerhard Häring - [e-mail] - Python, web & database development

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

iD8DBQFCjFjddIO4ozGCH14RAkqpAJ4m2Q3fh/Ta4pFm/v6mw45qBvJI/ACaA/2e
RHYiscfmDQTSWV6HZ+QSfZ4=
=HOgy
-----END PGP SIGNATURE-----