My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
Issue 199: with cxn: doesn't commit on success
1 person starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Aug 2011


Sign in to add a comment
 
Reported by je...@livedata.com, Aug 15, 2011
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
Aug 21, 2011
Project Member #1 mkleehammer
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.


Status: Complete
Aug 22, 2011
#2 je...@livedata.com
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?
Sign in to add a comment

Powered by Google Project Hosting