pyodbc's timeout doesn't work on Windows

Question

My code:

import pyodbc

connection = pyodbc.connect("DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=mydatabase;Trusted_Connection=yes")
connection.timeout = 3
cursor = connection.cursor()
results = cursor.execute("SELECT * FROM someLongTable").fetchall()

The timeout argument is ignored and the query runs for as long as it takes to complete (several minutes).

It's the same problem that this guy had, but the solution that worked for him didn't work for me. Now, unlike him I'm on Windows (both the client and the server are Windows machines). I know that signal doesn't work well on Windows and that sometimes people use signal to write timeout functions, so maybe Windows is the problem here? Could that be it? If so, is there any workaround?

(Python 2.7.9, pyodbc 3.0.7, Windows Server 2012, MSSQL 2012)

Answer 1

I found a workaround: retrieve the results one by one, counting the total elapsed time, and give up if more than ~30 seconds elapse.

import time
import pyodbc

def getMSSQLdata(cursor):
    connection = pyodbc.connect("DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=mydatabase;Trusted_Connection=yes")
    cursor = connection.cursor()
    results = []
    t0 = time.time()
    while True:
        if (time.time() - t0) < 30:
            next = cursor.fetchone()
            if next:
                results.append(next)
            else:
                print "query executed in {} seconds".format(time.time() - t0)
                return results
        else:
            print "aborted - query took longer than 30 seconds"
            return None # or raise some exception

Of course you can modify it to abort after a certain number of rows is exceed (instead of a certain time). Or to return only whatever rows get retrieved in the first ~30 seconds. And so on.

(It's not pretty though - and it's not precise. I wish I could figure out why pyodbc's timeout argument is ignored in Windows.)

Answer 2

I know this is an old question but I just had the same problem but in pypyodbc and have worked out why it does not work.

Basically the Query Timeout works but does not work for .fetchall()

t = time()

sqlStatement = " SELECT TOP (20000) * FROM BigTable"
DSN="Driver={SQL Server};Server=Server;Database=Database;Trusted_Connection=yes" 

cnxn  = pypyodbc.connect(DSN)
cnxn.timeout = 1

cursor = cnxn.cursor()
print (time()- t)

cursor.execute(sqlStatement)
print (time()- t)

results = cursor.fetchall()
print (time()- t)

which gives the following result:

0.09884047508239746
0.2534453868865967
3.447007894515991