11

I've been unable to find a documented way to set a timeout for the initial connection that actually works. I'm not asking about a "query timeout", but rather a timeout on an initial connection attempt in the case that the DB server is completely down or unreachable, and there's no response at all. By default, such connections appear to timeout after 255 seconds - is there a way to set a shorter timeout?

Edit: for clarity, I should reiterate the stack here:

  • python
  • pyodbc
  • unixODBC (not iODBC)
  • MS ODBC Driver 11 for SQL Server (not FreeTDS)
  • Linux
Improve this question
4
  • According to this page no, there is no way to specify connection timeout in ODBC. You might try putting a connect timeout = X in your odbc.ini file - it shouldn't work but it might be an undocumented workaround. If so, I wouldn't trust it in a production environment.
    – Benny Hill
    Commented Sep 13, 2013 at 13:11
  • You could try implementing your own timeout for the connect function using the answer here.
    – Bryan
    Commented Sep 14, 2013 at 2:35
  • Alas, that timeout decorator or similar wouldn't work because the connection is being opened within the binary MS ODBC driver, outside control of the Python interpreter.
    – mikenerone
    Commented Sep 18, 2013 at 9:05
  • Solved here. stackoverflow.com/a/35640876/2906290 I've confirmed it works for pyodbc with MS SQL Server: docs.sqlalchemy.org/en/14/core/…
    – Raúl Moreno
    Commented Jan 31, 2022 at 19:26

2 Answers 2

Reset to default
7

https://stackoverflow.com/a/12946908/1552953

This answer refers to being able to set a timeout on the connection:

Timeout

An optional integer query timeout, in seconds. Use zero, the default, to disable.

The timeout is applied to all cursors created by the connection, so it cannot be changed for a given connection.

If a query timeout occurs, the database should raise an OperationalError with SQLSTATE HYT00 or HYT01.

Note: This attribute only affects queries. To set the timeout for the actual connection process, use the timeout keyword of the pyodbc.connect function.

result = None
with pyodbc.connect('DRIVER={SQL Server};SERVER=mydb;DATABASE=solarwinds;Trusted_Connection=True', timeout=1) as cnxn:
    cursor = cnxn.cursor()
    result = cursor.execute(query).fetchall()

So using the above code it didn't timeout within 1 second, or at least it didn't return a result within 1 second. But it did return a result much faster than without a timeout set.

Improve this answer
1
  • 2
    I'm no longer using the environment to test this, and despite the mention in that note, this feature is undocumented in the section on the pyodbc.connect function; but looking at the code, it looks like at the very least an attempt was made to honor this keyword. I'm accepting this answer as it seems like it should be the prescribed way to do it, but fair warning to everyone: YMMV, as the available underlying binary drivers simply have never been well-behaved.
    – mikenerone
    Commented Aug 11, 2015 at 17:29
0

A thinned answer for faster copy/paste:

connstring = "<your_connection_string>"
pyodbc.connect(connstring, timeout=3)

The GitHub pyodbc.connection function and pyodbc.Connection attributes documentation.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.