[DB-SIG] Proposed DB-API extensions

[M.-A. Lemburg]

On 2008-03-19 13:23, Gerhard Häring wrote:
> I'd like to propose the attached changes to the DB-API spec. My main 
> motivation is to make DB-API modules less cumbersome to use when used 
> directly.
> 
> If you want to try it out live, everything except .fetchscalar() is 
> already implemented in pysqlite. The __enter__/__exit__ thing is in 
> pysqlite and cx_Oracle.
> 
> 
> ==> .execute(), .executemany() returning self.
> 
> Ability to write shorter code. No more:
> 
> cur.execute("select ...")
> for row in cur:
>   ...
> 
> instead:
> 
> for row in cur.exeucte("..."):
>   ...

Nice to have, yes, but I'm -1 on putting that into the standard:
it doesn't work well with implementations that already return a
result set object for .execute*().

Remember that the return value for .execute*() was left undefined
in DB-API 2.0 to make this possible. In DB-API 1.0, .execute*()
used to return the cursor.rowcount.

> ==> .execute(), .executemany() in connection object.
> 
> They should have been placed here anyway. *cough* Users shouldn't have 
> to mess with cursor objects normally. Ability to write shorter code:
> 
> con = module.connect(...)
> for row in con.execute("..."):
>   ...

-1. We explicitly removed this option in the transition from
DB-API 1.0 to 2.0, so adding it back again is not a good idea.

> ==> __enter__ and __exit__ in the connection object.
> 
> Ability to automatically wrap database code in transactions, when using 
> Python 2.5 or higher:
> 
> with con:
>     con.execute(DML1)
>     con.execute(DML2)
> 
> no more:
> 
> try:
>    cur = con.cursor()
>    cur.execute(DML1)
>    cur.execute(DML2)
>    con.commit()
> except:
>     con.rollback()

-1 on putting that definition in the standard.

con.execute() is a no-no (see above). Even if you do implement things
that way, the "with" statement would not be thread-safe.

IMHO, it's much better to write a context manager that takes
care of whatever logic is needed for your application and then
use:

with context.transactional(con):
     cur = con.cursor()
     cur.execute(DML1)
     cur.execute(DML2)

> ==> fetchscalar method in cursor object
> 
> This one is low-prio for me, but it's a common use case to only query a 
> scalar value.

-1 on adding that method to the standard.

While it's true that many queries just need the first row and
the first value from that query, the error handling will get more
complicated due to some databases returning an empty result set
instead of a single row return value e.g. when using
"SELECT COUNT(*) ..."

Note that all the -1s are just for adjusting the DB-API standard.
You are still free to implement these things in your modules.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 20 2008)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
            Registered at Amtsgericht Duesseldorf: HRB 46611