Background: I've had a lot of occasions to use SQLite and PySQLite recently and I've been so happy with the results that it is a very serious contender for the default storage in Mailman 3. This would replace BSDDB which the current code base uses. In fact I think the bsddb module support in Python's stdlib makes for a good comparison with Gerhard's proposal.
On Wed, 2004-10-20 at 12:29, Gerhard Haering wrote:
- Uses iterator-style SQLite 3.x API: sqlite3_compile, sqlite3_step() etc. This way, it is possible to use prepared statements, and for large resultsets, it requires less memory, because the whole resultset isn't fetched into memory at once any longer.
Cool. BTW, this makes me wonder whether it might be time to work on a DBAPI 3 that takes advantages of some of the more recent developments in Python. Apologies if such an effort is already underway and I haven't seen it.
- Completely incompatible with the SQLite 0.x/1.x API: I'm free to create a much better API now.
It's both fun and scary to be able to "do it right this time" :)
- "In the face of ambiguity, refuse the temptation to guess." - PySQLite 1.x tries to "guess" which Python type to convert to. It's pretty good at it, because it queries the column type information. This works for, I'd say 90 % of all cases at least. But as soon as you use anything fancy like functions, aggregates or expressions in SQL, the _typeless_ nature of SQLite breaks through and it will tell us nothing about the declared column type (of course, because the data is not coming from a database column).
I'd also like to see something in the middle. For example, I know what the intended types of my columns are, so I'd like to be able to set up a mapping of converters and pass that to PySQLite. I'd get the best of both worlds then -- explicit type conversion (because it always uses my mappings) and automatic type conversion (because I'll get the target type back from PySQLite directly without having to apply conversions myself everywhere).
I think that a simple embedded relational database would be a good thing to have in Python by default. And as Python 2.5 won't happen anytime soon, there's plenty of time for developing it, getting it stable, and integrating it.
I'm for it. Again, because we have batteries-included support for BSDDB, I see no reason why we can't also have batteries-included support for SQLite. Both are embedded databases, so if you've got the libraries and headers laying around, it should be a snap to configure and build the module.
Especially those of you that have used PySQLite in the past, do you have any suggestions that would make the rewrite a better candidate for inclusion into Python?
A few. Having used the Python bindings for MySQL also, there are a few things in its interface that I'd like to see in PySQLite (but please correct me if they're there but I missed them!).
The converter idea comes from mysql-python. I also liked their use of a 'how' argument to the fetch methods, which allowed me to retrieve the row as a dictionary. Very handy! I'm sure there are other nice features that I've forgotten about.
One problem I see is that even the new PySQLite will grow and try to wrap much of the SQLite API that are not directly related to the DB-API.
That's a /good/ thing, not a problem! :)
If such a thing is too complicated/big for the standard library, then maybe it would be better to produce a much simpler PySQLite, especially for the Python standard library that leaves all the fancy stuff out. My codename would be "embsql".
So, what would you like to see? "import sqlite", "import embsql", or "pypi.install('pysqlite')" ?
Personally, I'd like to see both a DB-API interface and a full-blown wrapping of the SQLite API. From what I can tell, it would be much smaller than the BSDDB wrapper, so it's potential size or complication doesn't bother me.
i-might-even-be-a-guinea-pig-for-ya-ly y'rs, -Barry