How to set connection timeout in SQLAlchemy

Question

I'm trying to figure out how to set the connection timeout in create_engine(), so far I've tried:

create_engine(url, timeout=10)

TypeError: Invalid argument(s) 'timeout' sent to create_engine(), using configuration PGDialect_psycopg2/QueuePool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

create_engine(url, connection_timeout=10)

TypeError: Invalid argument(s) 'connection_timeout' sent to create_engine(), using configuration PGDialect_psycopg2/QueuePool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

create_engine(db_url, connect_args={'timeout': 10})

(psycopg2.OperationalError) invalid connection option "timeout"

create_engine(db_url, connect_args={'connection_timeout': 10})

(psycopg2.OperationalError) invalid connection option "connection_timeout"

create_engine(url, pool_timeout=10)

What should I do?

score 97 · Accepted Answer · edited Jul 08 '22 at 10:58

97

The right way is this one (connect_timeout instead of connection_timeout):

create_engine(db_url, connect_args={'connect_timeout': 10})

...and it works with both Postgres and MySQL

docs sqlalchemy connect-args

ps: (the timeout is defined in seconds)

edited Jul 08 '22 at 10:58

Brown Bear

19,655
10
58
76

answered Feb 26 '16 at 00:04

daveoncode

18,900
15
104
159

12

What is the default value for the connect_timeout variable (in general and specific to MySQL database? – nivhanin Nov 22 '16 at 12:45
3

FWIW, this syntax is documented here: http://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-args – rotten Apr 16 '18 at 11:52
Other timeouts I try are failing. For example, I would like to use net_read_timeout for mysql, but that does not seem to work. – pandasCat Jun 27 '18 at 23:02
1

and `timeout` for pymssql. – Ben Dec 02 '18 at 04:11
8

10 what? Minutes or second or milliseconds? – Ganesh Satpute Sep 16 '19 at 14:21
2

@Ganesh Satpute seconds! – daveoncode Nov 04 '19 at 17:16
The list of available options for Postgres, like `connect_timeout`, seems to be documented here: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS – Nick Chammas Mar 03 '20 at 19:02
What happens to the connection if the computer runs out of memory, stalls the DB. Will the database be able to disconnect from the connection? – jsibs Apr 30 '21 at 18:45
How to check the default value for the connect_timeout? – raaj Oct 06 '21 at 11:08
`TypeError: 'connect_timeout' is an invalid keyword argument for this function` doen't work on oracle – TomSawyer Jan 23 '22 at 18:22
I confirm it works with MS SQL Server. "This may be used for parameters that are otherwise not handled by the dialect when added to the query string, as well as when special sub-structures or objects must be passed to the DBAPI" https://docs.sqlalchemy.org/en/14/core/engines.html#use-the-connect-args-dictionary-parameter – Raúl Moreno Jan 31 '22 at 19:23

dappiu · Answer 2 · 2019-11-04T16:22:00.113

For whoever is using Flask-SQLAlchemy instead of plain SQLAlchemy, you can choose between two ways for passing values to SQLAlchemy's create_engine:

Use SQLALCHEMY_ENGINE_OPTIONS configuration key (Flask-SQLAlchemy>=2.4 required)

SQLALCHEMY_ENGINE_OPTIONS = {
    'connect_args': {
        'connect_timeout': 5
    }
}

Or, in alternative, use engine_option when instantiating flask_sqlalchemy.SQLAlchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(
    engine_options={ 'connect_args': { 'connect_timeout': 5 }}
)

db.init_app(app)

EDIT: The examples are using the connect_timeout argument that works (at least) for MySQL and PostgreSQL (value represent seconds), other DBMS may require different argument name to be passed to affect the connection timeout. I suggest to check your DBMS manual to check for such option.

deargle · Answer 3 · 2019-05-22T19:19:32.363

In response to comment below by @nivhanin which asks "What is the default value for the connect_timeout variable (in general and specific to MySQL database?"? (I don't have enough reputation to leave comments).

Default for connect_timeout for Mysql5.7 is 10 seconds

Also maybe relevant:

wait_timeout -- default value of 28800 seconds (8 hours)
interactive_timeout -- default value of 28800 seconds (8 hours)

Anton · Answer 4 · 2022-09-12T20:06:52.063

6

For SQLite 3.28.0:

create_engine(db_url, connect_args={'timeout': 1000})

will set the connection timeout to 1000 seconds.

edited Sep 12 '22 at 20:06

answered Aug 11 '19 at 18:34

Anton

174
1
11

score 2 · Answer 5 · answered Oct 31 '18 at 15:52

2

For sqlite backend:

create_engine(db_url, connect_args={'connect_timeout': timeout})

will set the connection timeout to timeout.

answered Oct 31 '18 at 15:52

pbn

2,406
2
26
39

lorenzori · Answer 6 · 2019-06-28T10:53:53.053

2

for SQL Server use the Remote Query Timeout:

create_engine(db_url, connect_args={'Remote Query Timeout': 10})

default is 5 seconds.

edited Jun 28 '19 at 10:53

answered Jun 25 '19 at 15:23

lorenzori

737
7
23

Do you have a source for the default timeout being 5 seconds? [As far as I can see in the docs](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15), the default is 600 seconds, which is more in line with the kinds of errors I'm getting in my application. – Mihai Chelaru Jan 16 '20 at 14:48
Use `'remote login timeout'` for the connection timeout – Thomasleveil Jun 21 '22 at 16:35

score 1 · Answer 7 · answered Sep 12 '19 at 02:15

For a db2 backend via ibm_db2_sa + pyodbc:

I looked through the source code, and there seems to be no way to control the connection timeout as of version 0.3.5 (2019/05/30): https://github.com/ibmdb/python-ibmdbsa

I'm posting this to save others the trouble of looking.

Blomex · Answer 8 · 2022-01-26T11:46:46.843

0

I tried to do this for binded mssql+pyodbc database and default sqlite and couldn't make any of above work.

What finally worked for me, was

SQLALCHEMY_ENGINE_OPTIONS = {
        'connect_args': {"timeout": 10}
    }

This is consistent with SQLAlchemy docs as well

edited Jan 26 '22 at 11:46

answered Jan 07 '22 at 14:59

Blomex

305
2
12

`TypeError: 'timeout' is an invalid keyword argument for this function` it doen't work on oracle – TomSawyer Jan 23 '22 at 18:26
yes, it works for microsoft sql server + sqlite only. – Blomex Jan 24 '22 at 08:45

score 0 · Answer 9 · answered Nov 08 '22 at 14:46

0

UPDATE: for SQL Server use 'remote login timeout':

create_engine(db_url, connect_args={'remote login timeout': 10})

Thomasleveil posted on Jun 21, but it was buried in the comments, and was the only version that worked for me.

answered Nov 08 '22 at 14:46

Brad Clay

1
1

How to set connection timeout in SQLAlchemy

9 Answers9

Linked