pyodbc.connect timeout argument is ignored for calls to SQL Server

Question

I am using pyodbc on Linux with FreeTDS to connect to SQL Server 2005. I have noticed that the timeout argument to my connection is not being honoured by my queries.

When I run the following I would expect to see timeout errors after both cursor.execute calls.

import pyodbc
import time

connString = 'SERVER=dbserver;PORT=1433;DATABASE=db;UID=dbuser;PWD=dbpwd;' + \
    'DRIVER=FreeTDS'
cnxn = pyodbc.connect(connString , timeout=3)

cursor = cnxn.cursor()

t1  = time.time()
cursor.execute("SELECT MAX(Qty) FROM big_table WHERE ID<10000005")
print cursor.fetchone()
t2 = time.time()
print t2-t1

cursor.execute("WAITFOR DELAY '00:00:30'")
print 'OK'

Instead I get this output. Indicating that the first db query is taking over 7.5 seconds and the second call is taking 30 seconds without throwing a timeout.

(808432.0, )
7.56196093559
OK

Is there a better way to force a query timeout using pyodbc and SQL Server?

Answer 1

Add Connection.timeout variable assignment to your code. Defaults to 0 (timeout disabled), expected in seconds.

import pyodbc
import time

connString = 'SERVER=dbserver;PORT=1433;DATABASE=db;UID=dbuser;PWD=dbpwd;' + \
             'DRIVER=FreeTDS'
cnxn = pyodbc.connect(connString)
cnxn.timeout = 3
cursor = cnxn.cursor()

t1  = time.time()
cursor.execute("SELECT MAX(Qty) FROM big_table WHERE ID<10000005")
print cursor.fetchone()
t2 = time.time()
print t2-t1

cursor.execute("WAITFOR DELAY '00:00:30'")
print 'OK'