fully close a connection

4,279 views
Skip to first unread message

Smoke

unread,
Jan 22, 2009, 5:17:44 AM1/22/09
to pyodbc
Hi,

I have a SqlAlchemy application using pyodbc that's causing me some
problems... It's a monitoring
application that connects to a MS Sql Server, so it's always on.
Sometimes happens that casualy I have a DBAPIError with pyodbc. The
error is something like [Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_DBC failed .... After the first time I
have this error every other DB operation generates this Error.
So.. what I would like to do is completely close ( kill ) che active
connection pool and recreate it. I've checked and doing everything
should be done to close the connection it remains alive in the SQL
Server Profiler..

So, I've checked directly with pyodbc and the problem remains....
Here's a code sample:

>>> import pyodbc
>>> n = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=test_DB;UID=u;PWD=pass')
>>> n.close()
>>> del n

after that the connection is still alive in the sql server profiler.
The only way i've found to close it is by killing the process... but,
to me, this solutions seems a little bit TOO ugly. Any help?

thanks

Fabio

mkleehammer

unread,
Jan 23, 2009, 11:09:44 AM1/23/09
to pyodbc
It does sound like a leak of some kind, but this doesn't actually tell
us anything yet. pyodbc uses ODBC connection pooling by default,
which causes the driver manager to keep the connection alive for a
while.

The quickest way to test this is to turn off pooling in your test
program:

import pyodbc
pyodbc.pooling = False
n = pyodbc.connect('DRIVER={SQL
Server};SERVER=localhost;DATABASE=test_DB;UID=u;PWD=pass')
n.close()
del n

(The pooling variable is documented here: http://code.google.com/p/pyodbc/wiki/Module)

Then test again. I think the connection will go away. If not, I'll
fix it immediately.

If it does go away, it just means we haven't found the cause of your
trouble yet.

Here's something I've done before: I created ConnectionWrapper and
StatementWrapper classes. I kept a class-level counter that I used as
a unique id, incremented in the constructor and logged in both the
constructor and destructor. (Don't decrement in the destructor or it
won't be unique.) To identify where you are in the code when you
create connections and/or statements you can also log a couple of
lines from the stack (using the traceback module) or you can trace out
the first 100 or so characters of the SQL. I then wrote a script to
see which connections weren't getting closed.

(Perhaps I should write a generic leak finder and keep it in a utils
directory.)

In the meantime, I'll perform some leak testing myself. There was a
leak in pyodbc about 3 years ago which caused serious problems for
me. I use it in 24x7 servers myself. (Windows services)

Smoke

unread,
Jan 23, 2009, 12:03:52 PM1/23/09
to pyodbc
Thanks very much for the answer.
Yeap, setting pyodbc.pooling = False, does the tick and the connection
is closed immediatly when i do con.close()

Now... i'll try and play a little with SQLAlchemy to make things work
because even if i do:
import pyodbc
pyodbc.pooling = False

before importing SQLAlchemy the problem still occurs... :(


>
> Then test again. I think the connection will go away. If not, I'll
> fix it immediately.
>
> If it does go away, it just means we haven't found the cause of your
> trouble yet.
>
> Here's something I've done before: I created ConnectionWrapper and
> StatementWrapper classes. I kept a class-level counter that I used as
> a unique id, incremented in the constructor and logged in both the
> constructor and destructor. (Don't decrement in the destructor or it
> won't be unique.) To identify where you are in the code when you
> create connections and/or statements you can also log a couple of
> lines from the stack (using the traceback module) or you can trace out
> the first 100 or so characters of the SQL. I then wrote a script to
> see which connections weren't getting closed.
>
> (Perhaps I should write a generic leak finder and keep it in a utils
> directory.)
>
> In the meantime, I'll perform some leak testing myself. There was a
> leak in pyodbc about 3 years ago which caused serious problems for
> me. I use it in 24x7 servers myself. (Windows services)

That's much like my app... I'll try to dig into...

Thanks again

Fabio
Reply all
Reply to author
Forward
0 new messages