[DB-SIG] Python 2.0 DB Api - Threading and Transactions not known until connected

[M.-A. Lemburg]

Brad Clements wrote:
> 
> I'm writing an ADO DA for Python to the 2.0 DB spec.
> 
> I've run into two problems:
> 
> 1. The threading level isn't known until a connection is established,
> since ADO can connect through ODBC, Ole DB and other providers, the
> thread safety isn't set until connected.
> 
> Is it reasonable to extend the 2.0 spec to say that if
> connection.threadsafety exists, use that instead of a module level
> variable?

I'd suggest setting the module level variable to the lowest
level and then add a per connection attribute as you indicated.
 
> 2. Transactions - I don't see a connection.begintransaction() method,
> just rollback() and commit(). The spec mentions that auto-commit has
> to be disabled, so how does a person turn it on? Why wasn't
> begintransaction() included in the spec? Simply calling commit() to turn
> on transactions is rather ugly, since the first transaction won't be able to
> be rolled-back.

When the cursor is created an implicit transactions start is
performed. This is normal SQL behaviour. You don't need to do
a .commit() to start a new transaction.

Auto-commit is enabled per default for ODBC. Since it renders
transactions useless (you can't rollback), the DB API spec
explicitly states to turn it off per default. You can provide
a method to turn it back on again, if you like, e.g. mxODBC
has connection.setconnectoption(option,value) which allows
you to reenable auto-commit.

Connections that don't support transactions should not implement
the .rollback() method or raise a NotSupportedError exception whenever
the method is called.

> Same problem as in 1, I won't know if transactions are supported until a
> connection is established.
> 
> Given that thread safety, and transactions are per-connection in this
> DA, what is the best way to support this concept within the bounds of
> the 2.0 spec?
> 
> Since begintransaction() is not specified in the spec, what is the
> recommended method for auto-commit vs auto-begin, etc? I'd prefer,
> and will need, an explicit begin,commit for use with Zope. Whats the
> recommended way to merge this in?

Make the .rollback() method dynamically defined, i.e. have it
disabled on connections that do not support transactions. An explicit
"begin transaction" is not needed since this always happens
implicitly when you call connection.cursor() or cursor.commit()/
.rollback().

Cheers,
-- 
Marc-Andre Lemburg
______________________________________________________________________
Y2000:                                                   128 days left
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/