[Python-Dev] SQLite module for Python 2.5
barry at python.org
Wed Oct 20 19:13:52 CEST 2004
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
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
> - 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
> 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
> 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
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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Size: 307 bytes
Desc: This is a digitally signed message part
Url : http://mail.python.org/pipermail/python-dev/attachments/20041020/a83299fe/attachment.pgp
More information about the Python-Dev