Hello,
So, I have been a little out of the loop on DBAPI2 for a while. I just
went back and read it and would like to change my argument a bit. (For
some bizarre reason I was thinking that .begin() was part of the spec).
It seems to me that the default behavior as it is implicit, should not
be autocommit style. It should be standard transaction style. Therefore
based on the OP:
"""
.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.
"""
At least from a PostgreSQL perspective this is invalid. If you do not
issue a begin (implicitly or not), it is going to work in an autocommit
style mode and you can't turn it off. Since we (python) issue an
implicit begin when we create a connection, PostgreSQL automatically
turns off autocommit (fun huh?).
One way to handle this would be to have a connection property that
explicitly turns on autocommit. If set to TRUE, PostgreSQL would just
act in its default behavior, if set to FALSE it would implicitly create
a transaction. Even with a long lived connection you could always send a
BEGIN; explicitly and a proper transaction would start. You would just
have to remember to call .commit() in order for your data to be commited
after the BEGIN; If you called .commit() in autocommit mode you just get
a warning that says no transaction in progress.
I think we the ability to call an explicit .begin() that can be
implemented properly up to the driver level. This:
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()
Sincerely,
Joshua D. Drake
P.S. And since we are on the topic, we really need proper prepare()
--
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
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
