[Python-Dev] SQLite module for Python 2.5

[Gerhard Haering]

On Thu, Oct 21, 2004 at 02:42:19AM -0500, Ian Bicking wrote:
> Skip Montanaro wrote:
> >[Putting PySQLite into stdlib]  That means it will almost certainly
> >be stretched beyond its limits and used in situations where it
> >isn't appropriate (multiple writers, writers that hold the database
> >for a long time, etc).  That will reflect badly on both SQLite and
> >Python.
> 
> While I like the idea of SQLite wrappers in the standard library, I 
> think this is a good point -- and indeed, a lot of people run up against 
> the limits of SQLite at some point (e.g. PyPI).

Off-topic here, but that must have been PySQLite < 0.5, because since
then, concurrent readers is no problem any longer. With SQLite3 btw.,
SQLite has much better concurrency support. And with "using it right",
it can scale up a lot better now. But that's irrelevant here, IMO.

My point is to include a usable DB-API 2.0 implementation that people
can use as a starting point when developing applications that need a
relational database. Other languages do the same btw. Java (win32?)
includes a JDBC driver or ODBC, and PHP5 includes a SQLite module.

> [...] I am particularly concerned if the SQLite bindings become less
> like the other DB-API bindings, so that it is hard to port
> applications away from SQLite. Specifically, while the type coercion
> isn't perfect, it makes SQLite *much* more like other RDBMS's; I'd
> be bothered if by default SQLite acted significantly different than
> other databases.  While the DB-API doesn't address this issue of
> return types, it's only an issue for SQLite, since all the other
> databases are typed.

That's an important issue for me. And because I believe you guys here
are good at creating good API designs I'd like to hear suggestions.
(*)

- Worse is better - stay with the old scheme that works in 90 % of all
  cases, but in 10 % lets the users be surprised and complain?

- Stupid by default, which works 100%. If people want the "smart
  mode", then they need to read the docs and thus know its
  limitations. OTOH, the "stupid" behaviour is probably surprising
  too, but at least coherent.

For those not so used to (Py)SQLite:

All in all, SQLite *is* still typeless. (*)

PySQLite builds all the type guessing on top of SQLite, but because of
the limitations in the engine, it can't always guess right.

WAIT!

I *can* implement something that is smarter than always converting to
unicode/string, and that is, I can ask the SQLite engine which type a
column has, but the limitation is it will only return its internal
types:

#define SQLITE_INTEGER  1
#define SQLITE_FLOAT    2
#define SQLITE_TEXT     3
#define SQLITE_BLOB     4
#define SQLITE_NULL     5

As soon as you want anything more fancy, like DATE or TIMESTAMP, or
BOOLEAN, or whatever, you need PySQLite support again.

Would it be a good default for the standard library if the module only
knew about these SQLite internal types?

-- Gerhard
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://mail.python.org/pipermail/python-dev/attachments/20041021/3ff94b41/attachment.pgp