My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 100: Feature request: Implement context managers in pyodbc
2 people starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Sep 2010


 
Reported by gerald.b...@gmail.com, Jun 1, 2010
I would like to write python code like this:

import pyodbc
with pyodbc.connect(dsn='mydsn') as conn:
    with conn.cursor() as curs:
        for row in curs.execute('select * from mytable')
            ...

To make this possible, we will need the connect() and cursor() objects to 
implement the __enter__ and __exit__ methods.  I would think that the 
__enter__ method should just return the object:

def __enter__(self):
    return self

The __exit__ method needs to be aware of any exceptions that occurred and 
call its close method.  For the connect() object, it should also do a 
commit, unless a rollback already happened:

def __exit__(self, exc_type, exc_value, traceback):
    if exc_type == None:
        if not self.rollback:  # True if call to rollback
            self.commit()
    self.close()
    return exc_type

For cursor() objects, if autocommit=True, it should call commit() (unless 
rollback was called).  Then it should call close():

def __exit__(self, exc_type, exc_value, traceback):
    if exc_type == None:
        if self.autocommit and not self.rollback:
            self.commit()
    self.close()
    return exc_typ


Jun 1, 2010
#1 gerald.b...@gmail.com
This is not a defect, just a feature request
Jun 1, 2010
Project Member #2 mkleehammer
The reason I've never implemented this is because you can write code like this:

def f():
  conn = pyodbc.connect(dsn='mydsn')
  curs = conn.cursor()
  for row in curs.execute('select * from mytable')
      ...

Since Python uses reference counting, the cursor and connection will be closed 
immediately when you exit the function, either normally or by via an exception.

I'm not sure what benefit there would be to a context manager.  Therefore, I've been 
holding off implementing it so newbies don't think pyodbc is so complicated (not to 
mention I'd never use them myself ;).  Do you have any use cases where it would be 
useful?

Status: Investigating
Labels: -Type-Defect Type-Enhancement
Jun 1, 2010
#3 gerald.b...@gmail.com
I guess I have two observations:

1. While it is true that Python uses reference counting, that does not mean that 
connect.close() or cursor.close() are called automatically when the reference count 
drops to zero.  (In fact, I would consider it a bug if Python did that).  For 
example, this does not happen for file objects unless you use a context manager.

e.g.

def f():
   f = open('myfile')
   ...

# The file is still open, even though I have no reference to it.  OS resources are 
still in use.  Even if Python GCs its resources, it does not auto-close the file.

with open('myfile') as f:
   ...

# The file is now closed, since I exited the context

2. I would like to see the context managers call the commit() function in the 
__exit__ method before calling close().
Jun 3, 2010
Project Member #4 mkleehammer
Actually it does work the way I mentioned.  I can assure you that the file is closed 
when you leave the function f.  (I even wrote a quick test this morning, but since 
I'm familiar with the Python internals, I was sure.)  It would be a bug (a memory 
leak) if it did not work that way.

Here's one way to think about it: Once you leave f(), there are *no* references left 
to the file, not even internal ones, so there is no way it could be closed later.  
What reference would later code use to close the file?

All objects are destroyed when there reference count reaches zero.  The garbage 
collector is only needed for breaking cycles, like two objects that point to each 
other.  In that case, both would have a refcount of 1 - the cycle collector detects 
these and destroys both objects.

If you really want to test it for yourself, here is an easy way.  Instead of using a 
file, create your own class and print something in the __del__ method, which is 
called when an object is destroyed.

Similarly, I can assure you that the database cursor and connection will be closed in 
this example when f() is exited, either normally or due to an exception being 
raiesed.

def f():
  cnxn = pyodbc.connect(...)
  cursor = cnxn.cursor()
  ...
  cnxn.commit()

As for an autocommit, that would actually make coding more difficult.  In the example 
above, notice the commit call at the bottom.  This pattern ensures that database work 
is only committed if the function successfully makes it to the bottom.

If an exception is raised, the cursor and connection are still closed as the function 
is exited.  However, since the commit didn't happen, closing the connection causes 
any work done in f() to be rolled back, which is exactly what you want if there is a 
failure.  It may have written to 2 out of 3 tables and encountered an error in the 
3rd -- since they all didn't complete, you want the transaction to be automatically 
rolled back.

In summary, I considered the context manager, and would still be happy to write it, 
but:

* I'd rather people understand how it works and why they don't need it.  If I put it 
in, lots of new pyodbc programmers would be taught that they need it, causing 
confusion.

* The code without is much simpler -- there is almost nothing extra.

* The pattern is pretty foolproof as long as the commit is not forgotten.  Adding an 
automatic commit at close would be disastrous though.  (Obviously there is the 
autocommit mode, but that should be discouraged except for use in tiny utilities.)

* I haven't found a single benefit yet.

* Finally, wrappers for Connection and Cursor could easily be created to add this, 
though I don't know why.  (I've created wrappers a few times, for things like 
internal logging, etc.  It's about 20 lines of code.)

Does that make sense?  Perhaps I should create a page for this topic in the docs?
Jun 3, 2010
#5 gerald.b...@gmail.com
Yes, of course the __del__ method is called when an object is destroyed, but that is 
not what you said before.  You said that the close() method was called. (FWIW, it's 
easy to verify that that is not the case.) Now, it may be that your implementation of 
__del__ calls close (as does file.  I was wrong there).  Fair enough.

If autocommit were done in the __exit__ method of a context manager, the method would 
first see if an exception had occurred (first arg is not None). Only if no exception 
occurred would commit be called.  Thus I could write:

with conn.cursor() as blah:
  # do stuff

and know that, if the suite exits normally, my changes would be committed.  If an 
exception occurs, they would be rolled back.  Simpler code!  The smarts are in the 
context manager.  I can't forget to commit my transactions and my data is safe 
(rollback) if an error occurs.  

Thus, these two would be equivalent:

def f():
   all_is_good = True
   blah = conn.cursor()
   # do stuff
   if something_bad_happens:
     all_is_good = False
   if all_is_good:
      conn.commit()

with conn.cursor() as blah:
   #do stuff
   if something_bad_happens:
       raise SomethingBadHappened

The first approach uses Python's function machinery and implicit garbage collection 
to get the desired effect.  The second approach explicitly exploits the context 
manager syntax to do the same thing, with less coding.  "Explicit is better than 
implicit" or so the Zen of Python claims.

In summary:

* One could use either approach depending on preference and experience.

* The code using a context manager is simpler and more robust.

* The pattern is foolproof AND you can forget about calling commit.  (Autocommit in 
__exit__ is not disastrous following the approach I give.)

* Benefits include simpler coding style and no need to create functions just to get 
this kind of effect.

Of course, it's easy to create wrappers to do this.  I've done that many times with 
other objects, including dbs, log files, locks and more.  I simply lobbying for 
support in the package to make that unnecessary.
Jun 3, 2010
Project Member #6 mkleehammer
I think you've convinced me due to the automatic commit by looking at the exception 
information.

One small nit to make it a fair comparison though, I never use a flag for committing 
since an early return or exception ensures commit isn't called:

def f():
   cnxn = pyodbc.connect()
   cursor = cnxn.cursor()
   # do stuff
   cnxn.commit()

You'll notice I create a new connection each time since I'm using connection pooling.  

More importantly, since commits are at the *connection* level, not at the cursor 
level, I would think we'd want the connection in the with, not the cursor.  I'd just 
let the cursor autoclose.

def f():
  with connect(...) as cnxn:
      cursor = cnxn.cursor()
      # do something

Side note: Having to allocate a separate cursor is extra work 99.9% of the time.  I 
actually added a Connection.execute() method which allocates a cursor for you.  If 
you are only executing a single statement, it is very handy:

def f():
  with connect(...) as cnxn:
      rows = cnxn.execute(...).fetchall()

Anyway, thoughts on adding it to the connection only?

Jun 3, 2010
#7 gerald.b...@gmail.com
I see far less benefit to adding it to the cursor.  That is quite arguably syntatic 
sugar (though I _like_ sugar!) and though mostly harmless (like Earth! (Douglas 
Adams)), may not be worth it.  Just to be sure though, what is the downside to not 
(maybe never) calling cursor.close()?
Jun 3, 2010
Project Member #8 mkleehammer
There is no downside because Cursor.__del__ and Connection.__del__ both close their 
resources.

Sep 4, 2010
Project Member #9 mkleehammer
In the v2unicode branch, which will become 2.1.8 as soon as I can get it tested on all the different platforms.

Sep 6, 2010
Project Member #10 mkleehammer
Fixed in 2.1.8

Status: Fixed
Nov 21, 2010
Project Member #11 mkleehammer
(No comment was entered for this change.)
Status: Complete
Aug 15, 2011
#12 je...@livedata.com
Comment 6 by project member mkleehammer, Jun 3, 2010 
I think you've convinced me due to the automatic commit by looking at the exception 
information.


but...I don't see the code.  Thus I'm confused why this enhancement is marked as "Complete".

__exit__ simply calls Connection_exit which calls Connection_clear (in Connection.cpp)...none of these routines check the exception state to decide whether to commit() or to rollback().  Shouldn't __exit__ call commit() if there have been no errors?  I thought that was the point of making the connection a context manager.

sqlite does this.  11.13.7.3. Using the connection as a context manager¶
New in version 2.6.

http://docs.python.org/library/sqlite3.html


Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:



So does cx-oracle: Connection.__exit__()
The exit point for the connection as a context manager, a feature available in Python 2.5 and higher. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed.

http://cx-oracle.sourceforge.net/html/connection.html

Powered by Google Project Hosting