Re: [Webware-discuss] Transactions with MiscUtils.DBPool

[andrea <and...@in...>]

Uzo Uzo wrote:
> I am using DBPool with pyPgSQL.  I am curious as to how transactions 
> work with DBPool.
>  
> Suppose I have the following.
>  
> conn = dbpool.getConnection()
> c = conn.cursor()
> try:
>   c.execute(foo1...) # inserts and updates
>   c.execute(foo2...)
>   c.execute(foo3...)
>   c.close(); conn.commit(); conn.close()
> except:
>   #log error here
>   c.close()
>   conn.commit()  # is this needed here?
>   conn.close()

are you sure you want to commit changes made until
exception is raised? if, an error is raised by postgress
on the second execute() committing your changes
will leave your db in a unknown state.

i dont' known your application logic but on exception
i would rollback() changes, not commit them.

> If one of the execute should throw an exception, will the 
> prceeding execute states before it commit or discard?   
> is that conn.commit() in my except clause needed?  thanks.

check the db-api spec: <http://www.python.org/peps/pep-0249.html>

.commit()
''Commit any pending transaction to the database. Note that
if the database supports an auto-commit feature, this must
be initially off. An interface method may be provided to
turn it back on.''

i dunno  about pyPgSQL but on recent versions of mysqldb module
there's such interface method and it's called
autocommit(True|False).

cheers,
deelan.