[DB-SIG] DBAPI-2.0 clarifications

M.-A. Lemburg mal@lemburg.com
Fri, 16 Mar 2001 18:30:08 +0100

Federico Di Gregorio wrote:
> Hi *,
>         i am about to fix the lastest DBAPI-2.0 compliance quirks in
> psycopg and to write a test suite but i need some clarifications on
> some parts of the DBAPI. this will be a long post, sorry for that...
> 1/ cursors and transactions
>   if a db support transactions, how two cursors derived from the same
>   connection are supposed to see changes done to the db? if cursor A
>   does an insert, is cursor B supposed to see the change suddenly or
>   only after a commit() on the connection? imho, it would be better
>   if the api specifies that a commit() is required, even if for the
>   drivers that does not support transactions commit() is a no-op.

This is left undefined in the DB API spec since database usually
have their own specific idea about how to handle such a situation
(it is called transaction isolation level in the ODBC docs). The
transaction isolation level can be specified on a per connection
basis if the ODBC driver supports this.

Note that txn isolation is only an issue for the situation where
you have one cursor e.g. adding data which could appear in the
result set that another cursor currently fetches.
> 2/ nextset() and executemany()
>   the api is not very clear on what a "set" is. it just says that not
>   all db support "multiple result sets". imho, it is logical that a call
>   to executemany() (with a SELECT operation) produces multiple sets.
>   is this the right interpretation or multiple result sets are something
>   different? what should happen if a SELECT is passed to executemany()
>   and the driver does not support nextset()?

No, .executemany() is there to allow passing multiple rows of
arguments to the database in one go. The statement is then executed
for each row of arguments binding those arguments to the parameter

Multiple result sets refer to the output format and only comes
into play for stored procedure which may return multiple result
sets. .nextset() then switches to the next available result set.

> 3/ constructor objects
>   are the constructor objects (Date, Time, Binary) expected to return
>   a valid *already quoted* string? and what method should return the
>   string, __repr__() or __str__()? i'll bet on __str__ but i am not
>   sure. a little example to better explain: if a generate the object
>   as t = module.Time('13:23:00') and then cursor.execute("SELECT * FROM
>   table WHERE time > %(time)s", {'time':t}) the string that gets
>   passed to the db is "SELECT * FROM table WHERE time > '13:23:00'",
>   right? (note the quotes...)

The constructors are there to wrap the date/time value in a
way which lets the interface tell the type. Just passing in a
string wouldn't allow this.

I'd recommend using mxDateTime for this purpose, since it was
written to implement these constructors.
> 4/ closing connections
>   closing a connection does an implicit commit() on it? this is important
>   because, following the file object style, most drivers implicitly close()
>   the connection when the object is collected by the gc and the user can
>   find the program commiting changes even if it did not tell it to.

No. It should do an implicit .rollback() if possible.

> 5/ the description field
>   how are the precision and scale field to be interpreted? are the fields
>   mandatory? sometimes obtaining the information in the description is
>   plain slow (like the display_size, you have to scan every single row
>   in the result set) or requires accessing system tables. very few programs
>   use the most esoteric fields (most programs only use type_code and name.)
>   what about (i know that this will make people flame me) add a new method
>   to obtain a detailed description and put a shorter description in the
>   current .description field?

These two fields can probably be safely set to None (mxODBC does
this), since today 132-column printers and ASCII terminals
for formatting result set outputs are not really relevant anymore ;-)
> thank you very much for your time,
> federico (expectiong comments)
> --
> Federico Di Gregorio
> MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
> Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
>               All programmers are optimists. -- Frederick P. Brooks, Jr.
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig

Marc-Andre Lemburg
Company & Consulting:                           http://www.egenix.com/
Python Pages:                           http://www.lemburg.com/python/