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?

Perform

Answer 1

Refer pyodbc connection, there are two separate timeout parameters, a variable on the Connection class (this sets the timeout for queries) and a keyword param to pyodbc.connect (and this one for the actual connection process). Based on this you are setting the timeout for the connection process in your code and not for queries.

Protohistory

Documented here: github.com/mkleehammer/pyodbc/wiki/Connection#timeout – Longley

Answer 2

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'

Stidham

While I've accepted cravori's answer by precendence, you give the same solution with a more detailed explanation. Thanks for that. – Perform

@Perform Understood. The timeout variable is new information to me also, and it wasn't immediately apparent to me what needed to be done to take advantage of it. Hopefully this will help those who run across it and are dense like me :-) – Stidham

@Stidham timeout is in connection object level , means after connection object populated , is it possible to pass any timeout while trying to connect , i mean login timeout , as postgres we can pass such timeout as : psycopg2.connect(user = data['username'], password = data['password'], host = postgres_host, port = postgres_port, database = postgres_db, connect_timeout=connect_timeout) – Kristianson