[DB-SIG] API Enhancements

[M.-A. Lemburg]

Hi everybody,

It's been a long time since we've last had some discussion
about a novelation of the API. As you probably remember I have
a proposed spec available at:

	http://starship.skyport.net/~lemburg/DatabaseAPI-1.1.html

The old one ist at:

	http://www.python.org/sigs/db-sig/DatabaseAPI.html

I have some more points that I would like to clarify:

1. Threading

The API spec should make some notes about the scope of thread
safety imposed on the interfaces. Thread safety could be given
at module level (threads all use the same module, but maintain
their own connections), connection level (threads share modules
and connections, cursors are not shared between threads) and
cursor level (threads share module, connections and cursors).

The last level is probably not feasable, but I think connection
level could be reached.

2. Stored procedures

Basically I want to revisit the discussion. The 1.1 proposal
defines this interface:

callproc(procname,list_of_parameters) 
              This method is optional since not all databases
              provide stored procedures. 

              Call a stored database procedure with the given
              name. The list of parameters must contain one
              entry for each argument that the procedure
              expects. The result of the call is returned by
              modifying the list contents in place. Input
              parameters are left untouched, output and
              input/output parameters replaced with the new
              values. 

              The procedure may also provide a result set as
              output. This must then be made available through
              the standard fetch-methods.

Is this general enough to fit everybody's needs ? I know that
Jim Fulton would rather like an interface which returns a callable
type... but it seems overkill to ask module writers to implement
this just to be DB API conform.

3. A standard catalog interface

There is often a need to connect to a database without knowing
in advance what tables it contains and how the table columns
are named.

The API should define a catalog method for this purpose,
returning a sequence of all known objects of a certain
type in the database, e.g. tables and views, in a predefined
way, e.g.

db.catalog('tables')

ODBC has a wide variety of such functions, but they all
return result sets with different schemas. Therefore I'd suggest
having the interface not return a result set, but instead a Python
list using a single format for all types of objects (passing None
for non applicable or unkown entries):

(qualifier,name,type,owner[,optional additional entries])

To find out the column names a function could then use
empty SELECTs and the cursor description attribute, e.g.
c = db.cursor()
c.execute('SELECT * FROM %s WHERE 1=0' % tablename)
# look at c.description

4. Optional named cursors

The cursor constructor should be allowed to have an optional
argument for the cursor name. This is useful for UPDATEs.

Comments ?

-- 
Marc-Andre Lemburg                               Y2000: 347 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------