20

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?

ChrisGuest
  • 3,398
  • 4
  • 32
  • 53

2 Answers2

23

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'
Bryan
  • 17,112
  • 7
  • 57
  • 80
  • 1
    While I've accepted cravori's answer by precendence, you give the same solution with a more detailed explanation. Thanks for that. – ChrisGuest Oct 18 '12 at 22:35
  • @ChrisGuest 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 :-) – Bryan Oct 18 '12 at 23:24
  • @Bryan 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) – Asraful Apr 14 '20 at 16:56
21

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.

iruvar
  • 22,736
  • 7
  • 53
  • 82