[DB-SIG] Questions about the DB API

[Hrvoje Niksic]

I am not very experienced with database programming, and some things
in the DB API seem strange.  Perhaps there are easy explanations for
those, but they're not obvious.  So:

* 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.

* 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?

* 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?

* 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
look like this:

cursor.exec("select blah blah blah...")
for (customerid, address) in cursor.fetch_iterate():
  ...

Currently you can replace fetch_iterate() with fetchall(), but it will
get all the data at once, which can be (needlessly) memory consuming
if you only need one (customerid, address) pair at a time.

You can also use fetchone(), but the code is not as nice:

while 1:
  tuple = cursor.fetchone()
  if tuple is None:
    break
  customerid, address = tuple