Hi. I've been working on some refactoring of the Python bindings for sqlite. I now have a working Python binding for sqlite 3 which is fairly different than the bindings for sqlite 2. I created a quick test case that creates a new table, inserts 500,000 rows, then selects all of them. Memory utilization is printed after the rows have been inserted, after the transaction is committed, after the SELECT is executed, and after the result set has been iterated through. The test can be seen here:
http://www.specifixinc.com/~msw/t4.py results: --- sqlite 2 --- [EMAIL PROTECTED] conary]$ rm -f test.db; time python2.3 t4.py 8656 5968 40520 40520 real 0m36.269s user 0m35.520s sys 0m0.310s --- sqlite3 --- [EMAIL PROTECTED] conary-work]$ rm -f test.db; time python2.3 t4.py 8084 5840 5840 8084 real 0m28.635s user 0m28.330s sys 0m0.130s The biggest difference in memory utilization comes from storing a compiled statement in the cursor object and fetching new rows only when cursor.fetch*() is called. The old python binding retrieved the entire result set at cursor.execute() time. Using a cursor as an iterator allows us to traverse the result set while using constant memory. Some other major changes: 1) Wildcards in the SQL passed to cursor.execute() now use the sqlite native '?' or ':N:' format. Previously Python syntax was allowed. Making this change lets us bind parameters to compiled SQL statements natively, without converting them to strings. This will also allow us to re-use a compiled statement by rebinding parameters later. 2) Currently the code that does parameter binding does not handle as many types as the old quoting code did, nor does it allow one to register their own converters for custom types. None of the old mx.DateTime works anymore. Newer versions of Python will have the objects required built-in, then adding support is trivial. 3) The pysqlite-specific magic "-- types" SQL statement is no longer supported. Results are returned according to the schema definition or sqlite_column_type() results. 4) Multiple statements in one cursor.execute() call were supported in the old binding. This won't work with the new compiled-statement method, so an exception will be raised. A _PRELIMINARY_ snapshot of the binding can be found here: http://www.specifixinc.com/~msw/pysqlite3-0.0.0.tar.gz I've been doing development in our Conary CVS repository. You can browse the repository here: http://cvs.specifixinc.com/viewcvs/viewcvs.cgi/conary/pysqlite3/ I'm not trying to hijack the work by Gerhard - but our project needed new features that sqlite 3 brings, and we couldn't wait for a few months while new support was added. I welcome feedback, and am more than happy to throw this work away if a better Python binding for sqlite emerges. Cheers, Matt
