Pyodbc connection String additional requirements

[Shiva Kumar]

Hello Team, I have a python script that runs in a loop in a docker container fetching the records from AS400 systems. I have noticed that execution time was taking 15 seconds for getting data from 4 systems. However when one of the system is under Maintenance, the execution time is taking around 250 seconds. I’m handling exception for System Maintenance. I believe the connection statement tries to connect to Maintenance system for sometime and then moves to next system. Do we have any option that we can pass in the connection string to try connecting for just 10 seconds for any System that is under Maintenance and move on.

[Mark Irish]

Hi Shiva,

I think what you are looking for is a connection timeout. In ODBC, this is set with the SQL_ATTR_CONNECTION_TIMEOUT connection attribute (https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function?view=sql-server-ver16)

It looks like pyodbc has to ability to set this on the connect function through the timeout parameter: https://github.com/mkleehammer/pyodbc/wiki/The-pyodbc-Module#connect (scroll down a bit to the “other parameters” table). I will note that it looks like it is using SQL_ATTR_LOGIN_TIMEOUT, but I think that it should still work for you to timeout after x number of seconds.

[Shiva Kumar]

Thanks Mark for the inputs. I’ll try this out in our next Maintenance. So when I specify timeout = 15 secs, Is that mean connection statement keeps trying to connect Maintenance DB for 15 sec and then move to the next one in the loop?

[Mark Irish]

It depends on how you have structured your code, but I would imagine the connection will generate an error that you will have to handle (probably just log and continue, or simply ignore). After that, the code will continue executing.