[PYTHON DB-SIG] database API

[Greg Stein]

>----------
>From: 	Thomas Breuel[SMTP:tmb@best.com]
>Sent: 	Thursday, October 10, 1996 9:19 PM
>To: 	db-sig@python.org
>Subject: 	[PYTHON DB-SIG] database API
>
>Hi,
>
>I found the Python Database API (PyDB) spec, but I'm a little
>confused:  it seems to lack some abstractions that are present in JDBC
>and ODBC.

We (urm, the defunct eShop :-) implemented PyDB for ODBC quite snappily.
Works great. The interface also worked quite fine for Oracle and
Informix. Further, the interface had more than enough functionality for
actually building a serious server application.

>PyDB's concept of a "cursor" seems to encompass (somehow) the concepts
>of "statement", "prepared statement", and "result set".  But I can't
>tell from the API spec how a statement is prepared in the first
>place.  And if I prepare it, I can't tell how I can use it multiple
>times, or how I can generate multiple result sets and then
>step through them simultaneously.

my_statement = "select * from some_table where col1 = :1 and col2 = :2"
cursor = connection.cursor()
cursor.execute(my_statement, params1)
rows1 = cursor.fetchall()
cursor.execute(my_statement, params2)
rows2 = cursor.fetchall()

Specifically, if you pass in the *same* string objet for the statement,
then the implementations should reuse the statement that it prepared on
the prior execute().

The above example fetched all the rows at once; you can then step
through them as needed (most cases, you simply fetch them all). If your
results are too large to pull into memory and/or you need to be able to
terminate, you can do:

cursor1 = connection.cursor()
cursor1.execute(statement1)
cursor2 = connection.cursor()
cursor2.execute(statement2)
while 1:
  row1 = cursor1.fetchone()
  row2 = cursor2.fetchone()


>Another reason why this kind of interface seems odd to me is
>because I view a "prepared statement" kind of like a collection
>class and a "result set" like an iterator over that collection
>class.  Those are distinct concepts.

The cursor object is a prepared statement. A result set is a list of
tuples.

>While ODBC is perhaps unnecessarily complex, it seems to me that
>PyDB has been simplified a bit too much.  Or maybe I just don't
>understand it.  Can anyone explain?  Also, why not base the PyDB 
>interface directly either on the JDBC or the Perl DBI specs?  It
>seems like there is a lot of design reuse possible.

PyDB exposes just about everything you might need for database
operations. Even high-performance reuse of prepared statements, input
and output binding, and notation of bind sizes.

I would ask: what does it not allow you to do?

I believe that the interface can be even simpler (and still retain its
functionality). Jim Fulton has mentioned on several occasions over the
past 5 months ways to make the API simpler to work with, but has never
written anything down.

-g
>

=================
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
=================