'How to set connection timeout in SQLAlchemy

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?

pythonpostgresqlsqlalchemypsycopg2


Solution 1:[1]

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

  1. Use SQLALCHEMY_ENGINE_OPTIONS configuration key (Flask-SQLAlchemy>=2.4 required)
SQLALCHEMY_ENGINE_OPTIONS = {
    'connect_args': {
        'connect_timeout': 5
    }
}
  1. 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.

Solution 2:[2]

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:

Solution 3:[3]

For SQLite 3.28.0:

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

will set the connection timeout to 1000 seconds.

Solution 4:[4]

For sqlite backend:

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

will set the connection timeout to timeout.

Solution 5:[5]

for SQL Server use the Remote Query Timeout:

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

default is 5 seconds.

Solution 6:[6]

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.

Solution 7:[7]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Anton
Solution 4 pbn
Solution 5
Solution 6 MarredCheese
Solution 7

Related Questions