[Python-Dev] SQLite module for Python 2.5

[Barry Warsaw]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 307 bytes
Desc: This is a digitally signed message part
Url : http://mail.python.org/pipermail/python-dev/attachments/20041020/a83299fe/attachment.pgp