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 82: when using contextlib wrapper, cursor.execute fails when cursor is assigned to a variable before usage
1 person starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Dec 2009


 
Reported by ivan.mel...@gmail.com, Dec 21, 2009
What steps will reproduce the problem?
> run the attached script

What is the expected output? What do you see instead?
> expect to see the output as documented in attached module. Actually the
output is as expected, but an unhandled win32 error is thrown as well,
which kills the process.


What version of the product are you using? On what operating system?
> python 2.6.4 on WinXP-SP2, pyodbc-2.1.6

Please provide any additional information below.
> Basically I have a little context-aware wrapper around pyodbc.connect so
that it can be used with with_statement as described at
http://docs.python.org/library/contextlib.html.

The problem using it does not work in a very strange situation where the
difference between works/does-not-work is narrowed down to the difference
between following lines:

1-works:
...
db_conn.cursor().execute(Test_pyodbc_connect._SQL_INSERT, (1,))
...

2-does not work:
...
curs = db_conn.cursor()
curs.execute(Test_pyodbc_connect._SQL_INSERT, (1,))
...

test_with_pyodbc.py
11.6 KB   View   Download
Dec 21, 2009
#1 ivan.mel...@gmail.com
Another finding.
> It only fails when the insert statement is executed with the parameter. If no
parameters are passed to cursor.execute(). Therefore following works as expected:
    def test_with_works_noparam(self):
        """ Use contextmanager and immediatelly execute sql on cursor.  """
        logger = logging.getLogger('test_with_works_noparam')
        with pyodbc_connect(self._db_conn_str) as db_conn:
            for row in range(2):
                logger.info("Inserting row %d ...", row)
                curs = db_conn.cursor()
                curs.execute("INSERT INTO test_pyodbc_table (SomeUniqueValue) VALUES
(1)")
                logger.info("Inserted  row %d ...", row)
        return True

Dec 22, 2009
Project Member #2 mkleehammer
There is definitely a bug and I will fix it, but I can also provide a work around.  The crash only occurs when you 
are creating multiple cursors, which you are doing by putting the cursor() call inside the loop.

When I take it out of the loop (and change "(1,)" to "(row,"), the crash does not occur:

    def test2(self):
        logger = logging.getLogger('test_with_fails')
        with pyodbc_connect(self._db_conn_str) as db_conn:
            curs = db_conn.cursor()
            for row in range(2):
                logger.info("Inserting row %d ...", row)
                curs.execute(Test_pyodbc_connect._SQL_INSERT, (row,))
                logger.info("Inserted  row %d ...", row)
        return True

As an FYI, you can pass parameters without putting them into a tuple if you want:

  curs.execute("insert ...", (1,))  --> curs.execute("insert ...", 1)

Status: Investigating
Labels: -Priority-Medium Priority-High
Dec 22, 2009
#3 ivan.mel...@gmail.com
RE: bug > great, thanks
RE: parameters > I know and it is cool, but it is not part of DB-API so I try not to
use it for (appearance of) portability.

Dec 31, 2009
Project Member #4 mkleehammer
Fixed in e998b116d8341c552cc8a2d2dea02026d00504f3, which will go into 2.1.7

During parameter cleanup, the cursor's HSTMT was being used after the connection's HDBC was already freed.

It is strange that I was unable to create a test case for this other than using the context.

As a side note, I never use contexts since Python objects are deleted at the end of scope unlike garbage 
collection in Java or C#.  This means you can use something simple like:

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

The cursor and connection are automatically closed at the end of the function.  (Note that I create a new 
connection every time.  ODBC connection pooling is very fast on Windows.)

Connections perform an automatic rollback when they are closed, so if  the function doesn't make it to the 
commit due to an exception or early exit, everything is cleaned up appropriately.
Status: Verified
Nov 21, 2010
Project Member #5 mkleehammer
(No comment was entered for this change.)
Status: Complete

Powered by Google Project Hosting