Check if pyodbc connection is open or closed

Question

I often get this error: ProgrammingError: The cursor's connection has been closed.

Is there a way to check whether the connection I am using has been closed before I attempt to execute a query?

I'm thinking of writing a wrapper to execute queries. First it would check whether the connection is closed, if it is, it would reconnect. Is this an advisable way of doing this?

score 7 · Accepted Answer · answered Jun 01 '12 at 10:08

The wrapper is a good idea but I don't know any API to reliably check whether the connection is closed or not.

So the solution would be something along these lines:

for retry in range(3):
    try:
        ... execute query ...
        return # Stop on success
    except e:
        if is_connection_broken_error(e):
             reconnect()
             continue
        raise

raise # throw if the retry fails too often

score 5 · Answer 2 · answered Apr 07 '15 at 10:34

5

Another option would be to check if you can get a cursor (many times that's what you want from a connection anyway) and re-initialize the connection otherwise. It will look something like this:

try:
    cursor = conn.cursor()
except e:
    if e.__class__ == pyodbc.ProgrammingError:        
        conn == reinit()
        cursor = conn.cursor()

answered Apr 07 '15 at 10:34

lribinik

169
2
10

2

this is not a good idea. pyodbc.ProgrammingError is raised in a lot of situation. Using this you will eat lots of important error messages. – Jamie Marshall Jan 21 '19 at 23:48

score 2 · Answer 3 · edited Apr 30 '22 at 13:31

2

You can also do duck typing. Use what you need and catch the exception. 99% of the time it is likely to be OK.

edited Apr 30 '22 at 13:31

Suat Atan PhD

1,152
13
27

answered Jun 01 '12 at 09:39

Diego Navarro

9,316
3
26
33

Check if pyodbc connection is open or closed

3 Answers3