with cxn: doesn't commit on success

[GoogleCodeExporter]

trying out the context manager aspect of pyodbc connection.  My understanding 
of the "with" construct is that on __exit__ from my block of code, if no error, 
the __exit__ code should call cxn.commit().  But pyodbc simply clears the 
connection resulting in a rollback and I lose all my work.

>>> pyodbc.version
'2.1.9-beta16'

import pyodbc
import time

conn = pyodbc.connect("DRIVER={SQL 
Server};SERVER=192.168.128.163;DATABASE=MySample", timeout=5)

conn.timeout = 5
cur = conn.cursor()

with conn:
    cur.execute('CREATE TABLE TEST (GROUPNAME VARCHAR(100), NAME VARCHAR(100), VAL FLOAT, FLAGS INTEGER, TSTAMP DATETIME, UPDATED_TIME DATETIME)')

    params = ('GROUP1', 'POINT1', 123.456, 17, \
            time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime()), \
            time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())+".%03u" % 678)

    sqlStr = "INSERT INTO TEST (GROUPNAME, NAME, VAL, FLAGS, TSTAMP, UPDATED_TIME) VALUES (?, ?, ?, ?, ?, ?)"

    cur.execute(sqlStr, params)
    cur.execute("select * from TEST")
    for row in cur:
        print row

Original issue reported on code.google.com by je...@livedata.com on 15 Aug 2011 at 9:28

[GoogleCodeExporter]

Fixed in 2.1.10-beta01 and py3-3.0.1-beta03

I would recommend reconsidering whether code is clearer with our with the with 
statement.  I always recommend not using with and putting a commit at the 
bottom:

  cur = conn.cursor()
  cur.execute(sql, params)
  conn.commit()

If an error happens before the commit, everything is rolled back.  It is 
exactly the same number of lines of code (one 'with' or one 'commit'), has less 
indentation, and is more explicit.

The with does make sense when you have other references to the connection 
because you are not using pooling.

Original comment by mkleehammer on 21 Aug 2011 at 8:48

• Changed state: Complete

[GoogleCodeExporter]

I am interested in your opinion about this.  When I read about the context 
manager feature in Python, (the "with"), it seemed (abstractly) to be a good 
thing.  Why?  I pictured that I could have different exception handlers and 
logic within a unit of work, and no matter which code path traversed, my unit 
of work would end with a commit on success or a rollback on error.

But, I admit, this was all abstract reasoning.  I don't have a lot of coding 
experience with the "with", so I don't know if it works out cleaner or not.

Again, in my mind, I saw it akin to auto-cleanup code in a C++ destructor--a 
guaranteed code path through an exit handler.  

In a simple 4 line linear path of code, of course there doesn't appear to be 
any advantage.  The question is:  in a longer, more involved code-path with 
error handling, does the "with" make anything cleaner/safer/better?

Original comment by je...@livedata.com on 23 Aug 2011 at 1:43