pyodbc: Timeout keyword ignored in pyodbc.connect()

Hi all, This is linked to an old closed issue but I wanted to re-raise. Simply if a connection cannot be made to a DB, for example a firewall is blocking the attempt my script will just hang forever. I’ve looked around at the docs and stack overflow and a lot of people seem to be reporting the keyword doesn’t work / is ignored.

For example:

try:
        conn = pyodbc.connect(CONN_STRING,timeout=5)
except:
        print "Connetion to Database timed out"
        sys.exit(1)

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 45 (13 by maintainers)

Most upvoted comments

Hi,

What worked for me was getting the latest version of pyodbc from github(not through pip install pyodbc) and using attrs_before dictionary argument for the connect function, just like this:

SQL_ATTR_CONNECTION_TIMEOUT = 113
login_timeout = 1
connection_timeout = 3
connection = pyodbc.connect(connection_string, timeout=login_timeout, attrs_before={SQL_ATTR_CONNECTION_TIMEOUT : connection_timeout})

That 113 can be found in sqlext.h file(my path: /usr/include/sqlext.h). Note that by using this dictionary you are able to change many more settings.

If you encounter a problem with “PyInt_AsInt” you can change it to “PyInt_AsLong”, like i did here: https://github.com/andreiroman/pyodbc/commit/3a5c1add5515da3a2fd5e178079fd1b42ab81380

andreiroman on Sep 9, 2016

Im looking forward to the resolution! 😃 In the meantime, as an alternative to the SQL_ATTR_CONNECTION_TIMEOUT solution kindly proposed by @allen-munsch and @andreiroman, it is also possible to modify the connection via SQL after you open it SET statement_timeout = 10000

Hi ernesto, I believe the timeout being discussed here is the login timeout and not the statement execution timeout. The first is the time the driver waits while it’s trying to connect to eht DB, the second is the time it waits for a given command to finish executing.

Thanks Alex,

My understanding from the above was that indeed the original issue was reported for login timeouts but that the ticket is now about applying SQL_ATTR_CONNECTION_TIMEOUT through pyodbc’s interface. I also understood that SQL_ATTR_CONNECTION_TIMEOUT is intended to be a timeout for the connection (in any query), which if true would solve the issue that I have of not being able to set connection timeouts for the psqlodbc driver. This is why I suggested a workaround.

SQL_ATTR_CONNECTION_TIMEOUT affects all queries, not just connection attempts, so I was trying to keep them separate.

Please do excuse if I have misunderstood the intent of the thread and ignore my above comment. I was just trying to contribute.

ernestomi on Nov 13, 2019

Actually - there’s not much information in this thread about what drivers are being used. Recently I had a patch accepted at FreeTDS which fixes a bug with the SQL_ATTR_LOGIN_TIMEOUT value. The driver was storing it, but not using it.

So if (1) you’re using FreeTDS, and (2) you install the latest ‘master’, this shouldn’t be a problem anymore.

jerrycl on Apr 5, 2018

Thanks for the update. I’m going to reopen this and apply the timeout to CONNECTION in connect automatically. It would make sense for the connection timeout to be applied while connecting and the login only after connecting and sending login credentials.

Be aware that the timeout is, I believe, also applied to getting results from queries. If you set it to 2 seconds and have a query that takes 4, you’ll get an error instead of results. You might want to reset the timeout using something like cnxn.timeout=4 afterwards.

I think I’ll set the attribute if passed to the connect function, but then revert it to its previous value when the connect completes. I think people would expect it to apply to the login, but not to queries afterwards.

mkleehammer on Mar 30, 2018

Hung process using pyodbc.

info :

python2.7/site-packages/pyodbc-4.0.22-py2.7-macosx-10.13-intel.egg

before :

pyodbc.connect(cnxn_string, timeout=2)

debug :

sudo bash -c 'source /Users/stuff-intern1/.zshrc && dtruss -a python pystuff/loaders/sql.py'

...

12908/0x7a9cb:     39548       4      1 close_nocancel(0x3)      = 0 0
12908/0x7a9cb:     39591      16     12 socketpair(0x1, 0x1, 0x0)        = 0 0
12908/0x7a9cb:     39650      32     27 socket(0x2, 0x1, 0x0)        = 5 0
12908/0x7a9cb:     39664       8      3 setsockopt(0x5, 0xFFFF, 0x8)         = 0 0
12908/0x7a9cb:     39666       4      0 setsockopt(0x5, 0xFFFF, 0x1022)      = 0 0
12908/0x7a9cb:     39667       3      0 setsockopt(0x5, 0x6, 0x1)        = 0 0
12908/0x7a9cb:     39690       4      1 ioctl(0x5, 0x8004667E, 0x7FFEE561FCA4)       = 0 0
12908/0x7a9cb:     39791      97     93 connect(0x5, 0x7F902F425670, 0x10)       = -1 Err#36

with info from this thread:

    SQL_ATTR_CONNECTION_TIMEOUT = 113
    login_timeout = 1
    connection_timeout = 1
    connection = pyodbc.connect(cnxn_string, 
                                timeout=login_timeout, 
                                attrs_before={
                                SQL_ATTR_CONNECTION_TIMEOUT: connection_timeout})

After:

python pystuff/loaders/sql.py
('08S01', u'[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

This was through pip install. Looks like the attrs_before stuff works great.

Thanks.

allen-munsch on Mar 29, 2018

“ODBC Driver for SQL Server” (both 13 and latest 17) doesn’t have any timeout capabilities built in. So if you’re using e.g. turbodbc, you’re completely out of luck.

However, if you use pyodbc, you can use “timeout” param:

pyodbc.connect(‘conn_str’, timeout=X)

and it will “simulate” mssql LoginTimeout behavior (same built into e.g. PHP driver and described here: https://docs.microsoft.com/en-us/sql/connect/php/connection-options)

rayrapetyan on Jun 17, 2020

Im looking forward to the resolution! 😃

In the meantime, as an alternative to the SQL_ATTR_CONNECTION_TIMEOUT solution kindly proposed by @allen-munsch and @andreiroman, it is also possible to modify the connection via SQL after you open it

SET statement_timeout = 10000

ernestomi on Nov 13, 2019