[DB-SIG] autocommit support in pep-249

[Joshua D. Drake]

On 09/13/2011 09:12 PM, Michael Bayer wrote:
>
>
> 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()

Nothing, that doesn't actually do anything I didn't suggest above.

>
> ?   if one wants to work with transactions, DBAPI in it's default mode provides that.

Correct.

>     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.

Yes.

>
> 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.

No. What I am saying is, the begin() is optional. If you look at my 
example, if PostgreSQL is in autocommit mode, the only way to start a 
transaction is to execute a query to start the transaction (BEGIN or 
START TRANSACTION). From a, "it would be nice if" perspective, I would 
like to have the option of using .begin() to start a transaction. That 
seems to jive better with proper coding.

Sincerely,

Joshua D. Drake


-- 
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579