[DB-SIG] autocommit support in pep-249

[Michael Bayer]

On Sep 13, 2011, at 9:15 PM, Joshua D. Drake wrote:

> 
> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
> cur = conn.cursor()
> cur.execute("""INSERT""")
> cur.execute("""SELECT * from bar""")
> cur.begin()
> cur.execute("""INSERT""")
> cur.commit()
> cur.close()
> 
> Is much better than:
> 
> conn=psycopg2.connect("dbname='foo' user='dbuser', AUTOCOMMIT=TRUE)
> cur = conn.cursor()
> cur.execute("""INSERT""")
> cur.execute("""SELECT * from bar""")
> cur.execute("""BEGIN""")
> cur.execute("""INSERT""")
> cur.commit()
> cur.close()
> 

what's wrong with:

conn = psycopg2.connect(....)
cur = conn.cursor()
cur.execute(" ... sql ...")
conn.commit()

?   if one wants to work with transactions, DBAPI in it's default mode provides that.     If you want all statements to be ad-hoc such that they are "committed" immediately, whether that means COMMIT after every statement as it does on Oracle or just no BEGIN emitted as it does on Postgresql, turn on "autocommit".   "autocommit" is a flag you should only be able to change when no transactional state has otherwise accumulated, but otherwise, switch it any time.  

Adding explicit begin() means the DBAPI starts to look confused as to how it should be used - DBAPI authors also have to support two different transactional styles.

When I first came to use DBAPI, I was taken aback by how it has no begin(), even though I had spent many years using Oracle, but I came to be used to it.    I'd be curious to know the original rationale for it to be that way.   If DBAPI only allowed transactions via begin() and commit(), that would be fine also, but its doubtful the current mode of operation could ever be removed.