Hi Gerhard, hi all,
in the last days, we have been trying to run django on pypy, using the ctypes based implementation of pysqlite.
In doing this, we encountered a problem; we have exchanged a bit of private mails, so I try to sum up here:
This snippet explodes when run with pysqlite-ctypes, either on cpython or pypy-c:
from pysqlite2.dbapi2 import connect db = connect(':memory:') db.execute('BEGIN IMMEDIATE TRANSACTION')
<pysqlite2.dbapi2.Cursor object at 0xb7dc4f6c>
db.execute('COMMIT')
Traceback (most recent call last): File "<stdin>", line 1, in ? File "/home/exarkun/Scratch/Source/pysqlite3/pysqlite2/dbapi2.py", line 315, in execute return cur.execute(*args) File "/home/exarkun/Scratch/Source/pysqlite3/pysqlite2/dbapi2.py", line 483, in execute raise self.connection._get_exception() pysqlite2.dbapi2.OperationalError: SQL logic error or missing database
The very same thing happens on python 2.4 + pysqlite2 (non-ctypes version):
from pysqlite2.dbapi2 import connect db = connect(':memory:') db.execute('BEGIN IMMEDIATE TRANSACTION')
<pysqlite2.dbapi2.Cursor object at 0xb7cb0860>
db.execute('COMMIT')
Traceback (most recent call last): File "<stdin>", line 1, in ? pysqlite2.dbapi2.OperationalError: cannot commit - no transaction is active
However, it works perfectly on cpython 2.5 + sqlite3:
from sqlite3.dbapi2 import connect db = connect(':memory:') db.execute('BEGIN IMMEDIATE')
<sqlite3.Cursor object at 0xf7cff050>
db.execute('COMMIT')
<sqlite3.Cursor object at 0xf7cff020>
Samuele pointed out that maybe it's just a difference between pysqlite2 and pysqlite3; after more digging, he changed pysqlite-ctypes to print every SQL statement before sending it to sqlite; what he got is this:
Python 2.5.1 (r251:54863, Jan 17 2008, 19:35:17) [GCC 4.0.1 (Apple Inc. build 5465)] on darwin Type "help", "copyright", "credits" or "license" for more information.
from pysqlite2 import dbapi2 db = dbapi2.connect(':memory:') db.execute('BEGIN IMMEDIATE TRANSACTION')
BEGIN IMMEDIATE TRANSACTION <pysqlite2.dbapi2.Cursor object at 0x55e90>
db.execute('COMMIT')
COMMIT COMMIT Traceback (most recent call last): File "<stdin>", line 1, in <module> File "pysqlite2/dbapi2.py", line 318, in execute return cur.execute(*args) File "pysqlite2/dbapi2.py", line 489, in execute raise self.connection._get_exception() pysqlite2.dbapi2.OperationalError: SQL logic error or missing database
The double COMMIT is probably causing the problem; not sure if it's a bug in pysqlite-ctypes or an expected behavior.
Gerhard, what do you think about all of this? What's the best way to solve?
ciao, Anto
Samuele pointed out that maybe it's just a difference between pysqlite2 and pysqlite3; after more digging, he changed pysqlite-ctypes to print every SQL statement before sending it to sqlite; what he got is this:
Please note that for my pysqlite2 it works just fine (on both cpython2.4 and cpython2.5)
Maciej Fijalkowski wrote:
Samuele pointed out that maybe it's just a difference between pysqlite2 and pysqlite3; after more digging, he changed pysqlite-ctypes to print every SQL statement before sending it to sqlite; what he got is this:
Please note that for my pysqlite2 it works just fine (on both cpython2.4 and cpython2.5)
which pysqlite2? The standard version or the ctypes-based version?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Antonio Cuni wrote:
Hi Gerhard, hi all,
in the last days, we have been trying to run django on pypy, using the ctypes based implementation of pysqlite.
In doing this, we encountered a problem; we have exchanged a bit of private mails, so I try to sum up here:
This snippet explodes when run with pysqlite-ctypes, either on cpython or pypy-c:
from pysqlite2.dbapi2 import connect db = connect(':memory:') db.execute('BEGIN IMMEDIATE TRANSACTION')
That's illegal usage of pysqlite. In the very unlikely case that what you really want to do is *not* use DB-API implicit transactions. You have to switch to autocommit mode first:
set isolation_level=None; either the property on the connection object, or directly in the connect() call. That was the root of the problem - pysqlite's ctypes branch apparently will need to do a few more sanity checks.
[...] The double COMMIT is probably causing the problem; not sure if it's a bug in pysqlite-ctypes or an expected behavior.
Both, kind of ;-)
Gerhard, what do you think about all of this? What's the best way to solve?
I just pushed an ad-hoc fix for this particular problem to the hg repo:
changeset: 328:c42db28b5031 branch: ctypes tag: tip parent: 317:89e6da6ea1cb user: Gerhard Haering gh@ghaering.de date: Sat May 17 03:19:37 2008 +0200 summary: Quick fix: only use implicit transactions when autocommit is off.
HTH & good night; just shout if there's anything else that needs fixing
- -- Gerhard
Gerhard Häring wrote:
I just pushed an ad-hoc fix for this particular problem to the hg repo:
changeset: 328:c42db28b5031 branch: ctypes tag: tip parent: 317:89e6da6ea1cb user: Gerhard Haering gh@ghaering.de date: Sat May 17 03:19:37 2008 +0200 summary: Quick fix: only use implicit transactions when autocommit is off.
HTH & good night; just shout if there's anything else that needs fixing
Hi Gerhard, thank you very much for the fix; indeed, your fix, together with a small patches by me, solved the problem:
--- a/pysqlite2/dbapi2.py Tue Jan 15 16:31:23 2008 +0100 +++ b/pysqlite2/dbapi2.py Sat May 17 09:57:52 2008 +0200 @@ -236,7 +236,7 @@ def connect(database, **kwargs): return factory(database, **kwargs)
class Connection(object): - def __init__(self, database, isolation_level="", detect_types=0, *args, **kwargs): + def __init__(self, database, isolation_level=None, detect_types=0, *args, **kwargs): self.db = c_void_p() ret = sqlite.sqlite3_open(database, byref(self.db))
Now the django test that was previously failing passes; I've not run the others yet, but I expect them to pass as well, or to be broken for other reasons :-)
One more questions: I have two versions of pysqlite-ctypes around:
1) the one which was originally at http://hg.ghaering.de/pysqlite3/; that link is now broken but you can still download it from http://codespeak.net/~cfbolz/pysqlite3.tar.gz
2) the one in the official pysqlite repo; I've got it by doing hg clone http://oss.itsystementwicklung.de/hg/pysqlite/ && hg up -C ctypes pysqlite
I found that while (2) is supposed to be newer, it misses some features that (1) has, in particular Connection.create_function (which is needed by django). I'm not an expert of mercurial, but it seems that some changesets went lost when moving from the old url to the new one.
thank you again for your efforts! ciao, Anto
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Antonio Cuni wrote:
[...] One more questions: I have two versions of pysqlite-ctypes around:
- the one which was originally at http://hg.ghaering.de/pysqlite3/;
that link is now broken but you can still download it from http://codespeak.net/~cfbolz/pysqlite3.tar.gz
That was more a proof-of-concept (*). Without deep ctypes wizardry I couldn't make anything with callbacks from SQLite work reliably. That's why create_function, create_aggregate and friends were removed. If it's possible to make that work at all, it's beyond my understanding of ctypes.
FWIW I'd be very surprised if you'd get callbacks from C to Python working with PyPy, using the same ctypes API. Basically anything that involes a py_object smells like a problem for me.
- the one in the official pysqlite repo; I've got it by doing hg clone
http://oss.itsystementwicklung.de/hg/pysqlite/ && hg up -C ctypes pysqlite
I found that while (2) is supposed to be newer, it misses some features that (1) has, in particular Connection.create_function (which is needed by django).
create_function wasn't really fully implemented there, either. Also, everything that involved callbacks led to lost references and/or segfaults. That's why I dumped all that.
I'm not an expert of mercurial, but it seems that some changesets went lost when moving from the old url to the new one.
OTOH all pysqlite branches are now conveniently (for me, at least) in a single repository.
- -- Gerhard
(*) The current pysqlite-ctypes at least passes all tests and doesn't leak references running the tests.
That was more a proof-of-concept (*). Without deep ctypes wizardry I couldn't make anything with callbacks from SQLite work reliably. That's why create_function, create_aggregate and friends were removed. If it's possible to make that work at all, it's beyond my understanding of ctypes.
Hi Gerhard.
We tackled the issue by creating closures instead of py_object. This way it's saner and does not segfault for example on django test suite. We temporarily commited it to svn branch on codespeak, here: http://codespeak.net/svn/user/fijal/pysqlite2/, but we would rather prefer to keep it in your mercurial branch. Do you think our changes makes any sense? If so, can we have a commit permissions on mercurial branch? Otherwise, we'll at least try to keep it in sync.
Cheers, fijal
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Maciej Fijalkowski wrote:
That was more a proof-of-concept (*). Without deep ctypes wizardry I couldn't make anything with callbacks from SQLite work reliably. That's why create_function, create_aggregate and friends were removed. If it's possible to make that work at all, it's beyond my understanding of ctypes.
Hi Gerhard.
We tackled the issue by creating closures instead of py_object. This way it's saner and does not segfault for example on django test suite. We temporarily commited it to svn branch on codespeak, here: http://codespeak.net/svn/user/fijal/pysqlite2/, but we would rather prefer to keep it in your mercurial branch. Do you think our changes makes any sense? If so, can we have a commit permissions on mercurial branch? Otherwise, we'll at least try to keep it in sync.
I'll need to send the username / password in encrypted form. Do you have a PGP key?
- -- Gerhard
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Gerhard Häring wrote:
Maciej Fijalkowski wrote:
That was more a proof-of-concept (*). Without deep ctypes wizardry I couldn't make anything with callbacks from SQLite work reliably. That's why create_function, create_aggregate and friends were removed. If it's possible to make that work at all, it's beyond my understanding of ctypes.
Hi Gerhard.
We tackled the issue by creating closures instead of py_object. This way it's saner and does not segfault for example on django test suite. We temporarily commited it to svn branch on codespeak, here: http://codespeak.net/svn/user/fijal/pysqlite2/, but we would rather prefer to keep it in your mercurial branch. Do you think our changes makes any sense? If so, can we have a commit permissions on mercurial branch? Otherwise, we'll at least try to keep it in sync.
I'll need to send the username / password in encrypted form. Do you have a PGP key?
I'll happily set up authentication on my public pysqlite repository so you guys can have commit permissions. But I won't send sensitive information in cleartext, so if you're still interested, do you have a PGP key or anything?
- -- Gerhard
participants (3)
-
Antonio Cuni -
Gerhard Häring -
Maciej Fijalkowski