[DB-SIG] API Enhancements

M.-A. Lemburg mal@lemburg.com
Wed, 03 Mar 1999 10:00:12 +0100

Greg Stein wrote:
> > > A discussion in the API would be good. I might even say that a module
> > > could export an attribute that states which level is supported. For
> > > example, I know that many Win32 ODBC drivers are NOT thread-safe at all.
> > > You must open a connection per thread.
> >
> > How about a constant defined as module global:
> >
> > threadsafety:
> >
> > 0 - module level
> > 1 - connection level
> > 2 - cursor level
> >
> > If it's not available, then module level should be assumed.
> Strings constants are easy in Python. Would strings make more sense?
> 'module', 'connection', and 'cursor' ?

Hmm, strings wouldn't indicate order. Cursor level thread safety
implies connection level and module level.

> A module global stating the specification level would also be handy.
> '1.1' for modules conforming to the DBAPI 1.1 spec. If the global is not
> present, then the module is a 1.0 module.

How about: apilevel ?!

> > ...
> > Hmm. This takes us to another project: that of turning database
> > support for Python into packages... I'm currently building all my
> > extensions this way: there is usually one C extension and a whole
> > bunch of Python scripts which build upon its functionality. The user
> > always accesses the package through the main door :-) and thus does
> > not see what actually happens at the back end.
> >
> > Database interfaces could use a similar approach, e.g. code
> > the catalog interface in Python using the C extension to access
> > those special tables.
> Sure, but I'd say that the spec for any package is separate from the
> DBAPI spec for C modules. In general, it seems more difficult for
> C-level support to occur, than Python-level. IMO, providing a clean spec
> which is easy to implement really helps.

Well, that's why I said it's another project. The C level stuff
should be fairly easy to implement; all fancy extensions can then
be done in Python.

> > > > 4. Optional named cursors
> > > >
> > > > The cursor constructor should be allowed to have an optional
> > > > argument for the cursor name. This is useful for UPDATEs.
> > >
> > > I do not understand the use or operation of this, and how it would
> > > benefit an UPDATE. Could you elaborate?
> >
> > In ODBC, for example, the ODBC driver uses generated names for
> > all cursors. Now, it is sometimes useful to be able to refer to
> > the cursors under known names, e.g. to do UPDATE ... WHERE CURRENT
> > OF <cursorname>. The optional argument allows this by providing
> > an explicit name.
> But this update is passed to the execute() method of the cursor object
> that you're talking about, isn't it? Or do you create a second cursor,
> which updates the record currently indicated by the first cursor?

Both are possible. It depends on the database capabilities whether
this technique is of much use: e.g. you could update all the records
starting from the 10th row in a result set. Some DBs support scrolling
cursors in backward direction too, making updates relying on external
resources (e.g. file in a file system) possible.

> Isn't that kind of technique a bit difficult without a rigorous
> specification of the module's fetching behavior? Just because I called
> fetchone() doesn't mean the cursor is sitting on the first record... an
> array-fetch may have occurred, and the cursor is 90 rows later.

Good point. fetchone() ought do what the name implies: fetch exactly
one record. Otherwise usage of cursors would be pointless anyway.

> By naming a cursor, it would seem that any array-fetching would need to
> be disabled so that a program can actually determine what record is
> being referenced by the cursor. For safety, it may even be prudent to
> disable fetchmany() and fetchall().

Leave that to the user of the database interface. It should
be mentioned in the spec. though. fetchmany() may actually fetch
more records than requested. fetchall() wouldn't make sense
in this context anyway.

> Finally: is there a performance gain to the named-cursor technique?
> (compared to an UPDATE ... WHERE <primary-key> = <value>)

Hard to say... it could safe a temporary table though.

> Oh... a question about the nextresult() method. Why is that exposed?
> Shouldn't the module just automatically move to the next result set
> inside the fetch* methods? I don't see the benefit of exposing that to
> the user. It seems an artifact of the underlying database interface,
> rather than a useful user-level concept.

As Tod already pointed out, the nextset() method does have its
use for stored procedures: result sets are needed to group data.
If the interface would just silently move to the next set, there
would be no way to identify the set boundary.

I've uploaded an update of the spec. It's now at 1.1a4:


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