Re: Python DB-API problem with PostgresSQL pgdb.

Carwyn Edwards <cedward1@inf.ed.ac.uk> wrote:
> The Python DB-API (2.0) specifies that on creation a connection object 
> is to start a transaction if the database supports them. The pgdb 
> implementation of the DB-API does this:
> 
> >>> Begin Listing 1
> class pgdbCnx:
> 
>          def __init__(self, cnx):
>                  self.__cnx = cnx
>                  self.__cache = pgdbTypeCache(cnx)
>                  try:
>                          src = self.__cnx.source()
>                          src.execute("BEGIN")
>                  except:
>                          raise OperationalError, "invalid connection."
> 
> >>> End Listing 1

pyPgSQL (and psycopg, IIRC) solves this problem by having a .autocommit
connection level setting. Just do con.autocommit = 1 before issuing any
statements that PostgreSQL cannot execute within a transaction.

I'd suggest you simply switch to one of these Python DB-API modules. Apart from
the .connect and import call you very likely won't have to change *anything* in
your code.

Btw. if anybody wants to patch PyGreSQL accordingly, they're welcome to help
themselves with the pyPgSQL source, as it's under a MIT-like license as well.

Btw. IIRC there are a few other DB-API modules that implement .autocommit as
well.  Maybe one day this will make it into the DB-API optional extensions.

-- Gerhard