Is there any way to put timeout in pandas read_sql function?

Question

I connect to a DB2 server through ODBC connection in my python code. The DB2 server gets reboot for maintainence or disconnects me while running specific server side tasks, happens 1 or 2 times in a day. At that time if my code has started executing the pandas read_sql function to fetch result of query, it goes into a infinite wait even when the server is up after lets say 1 hour.

I want to put a timeout in the execution of read_sql and whenever that timeout occurs I want to refresh the connection with DB2 server so that a fresh connection is made again before continuing the query.

I have tried making a while loop and picking chunks of data from DB2 instead of pulling whole result at once, but problem is if DB2 disconnects in pulling chunk python code still goes into infinite wait.

chunk_size = 1000    
offset = 0
while True:
        sql = "SELECT * FROM table_name limit %d offset %d" % (chunk_size,offset)
        df = pd.read_sql(sql, conn)
        df.index += (offset+1)
        offset += chunk_size
        sys.stdout.write('.')
        sys.stdout.flush()
        if df.shape[0] < chunk_size:
            break

I need the read_sql to throw some exception or return a value if the sql execution takes more than 3 minutes. If that happenes I need the connection to DB2 to refresh.

Answer 1

You could use the package func-timeout. You can install via pip as below:

pip install func-timeout

So, for example, if you have a function “doit(‘arg1’, ‘arg2’)” that you want to limit to running for 5 seconds, with func_timeout you can call it like this:

from func_timeout import func_timeout, FunctionTimedOut

try:
  doitReturnValue = func_timeout(5, doit, args=(‘arg1’, ‘arg2’))
except FunctionTimedOut:
  print ( “doit(‘arg1’, ‘arg2’) could not complete within 5 seconds, hence terminated.\n”)
except Exception as e:
  # Handle any exceptions that doit might raise here