[DB-SIG] Questions about the DB API

[Greg Stein]

On 6 Nov 1999, Hrvoje Niksic wrote:
> * Why is a cursor required to execute an SQL query?
> 
> Supporting database cursors is of course nice, but other DB API's
> (e.g. Perl's) allow executing SQL statements directly from connection
> objects.  It sometimes seems silly to have to create a cursor object
> for the sole purpose of executing something.

An older version of the API did exactly that -- allow execution directly
from the connection object. It was not broadly implemented (i.e. against
the spec). When 2.0 came around, we decided that creating a cursor is not
a hardship on the programmer and simplified the API to allowing execution
only thru cursors.

> * Why is there no `prepare' support?
> 
> Some database API's support "preparing" a statement before sending
> it.  Why doesn't Python's DB API include such a thing?

There is, although it is not explicit, and it isn't termed a "prepare".
Take a look at the execute() method. It states that if you pass the same
string *object* (not value!) to the execute() method a second time, then
the underlying system can take advantage of a prepared cursor from the
last call to execute(). Several DBAPI modules take advantage of this -- it
is a big win on sequences of queries with different params and on multiple
inserts.

> * Is there any specification on what you can do with Python cursors?
> 
> For instance, can I send more than one query to the same cursor, or
> need I create a new one?  If I use a new query and want to retrieve
> the (new) data, will old query's data be ignored?

Hrm. Guess the spec doesn't make this explicit...

Yes, you can definitely send more than one query to a cursor; the old
query's contents will be tossed.

> * Supporting Python's iteration would be nice.
> 
> It would be nice if there were a ready cursor method, other than
> .fetchall(), which I could use in conjunction with `for'.  Code could

It would be very easy to write a small wrapper object to do this. The
DBAPI is intended to impose as little effort on the module writers as
possible. The multiple fetch methods are almost a bit much to request, but
they exist so that different optimizations can be done (e.g. fetchmany()
exists so that a module writer can take advantage of array fetches).

The current trend is actually to do a very thin C layer and then write the
DBAPI module in Python.

Anyhow: the point is that if you'd like to use iterators, then write
yourself a small class to do the fetchone() calls and expose that using
Python's iteration semantics (__getitem__; IndexError).

Cheers,
-g

--
Greg Stein, http://www.lyra.org/