Python 如何在 SQLAlchemy 中设置连接超时

How to set connection timeout in SQLAlchemy

提问by daveoncode

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

我试图弄清楚如何在 中设置连接超时create_engine()，到目前为止我已经尝试过：

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()，使用配置 PGDialect_psycopg2/QueuePool/Engine。请检查关键字参数是否适合这种组件组合。

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.

类型错误：无效参数“connection_timeout”发送到 create_engine()，使用配置 PGDialect_psycopg2/QueuePool/Engine。请检查关键字参数是否适合这种组件组合。

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

(psycopg2.OperationalError) invalid connection option "timeout"

(psycopg2.OperationalError) 无效的连接选项“超时”

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

(psycopg2.OperationalError) invalid connection option "connection_timeout"

(psycopg2.OperationalError) 无效的连接选项“connection_timeout”

create_engine(url, pool_timeout=10)

What should I do?

我该怎么办？

采纳答案by daveoncode

The right way is this one (connect_timeoutinstead of connection_timeout):

正确的方法是这个（connect_timeout而不是connection_timeout）：

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

...and it works with both Postgres and MySQL

...它适用于 Postgres 和 MySQL

ps: (the timeout is defined in seconds)

ps：（超时以秒为单位定义）

回答by deargle

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).

为了回应@nivhanin 在下面的评论，该评论询问“connect_timeout 变量的默认值是多少（通常和特定于 MySQL 数据库？”？（我没有足够的声誉发表评论）。

Default for connect_timeoutfor Mysql5.7 is 10 seconds

connect_timeoutMysql5.7 的默认值为10 秒

Also maybe relevant:

也可能相关：

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

• wait_timeout--默认值 28800 秒（8 小时）
• interactive_timeout--默认值 28800 秒（8 小时）

回答by pbn

For sqlitebackend:

对于sqlite后端：

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

will set the connection timeout to timeout.

将连接超时设置为timeout.

回答by lorenzori

for SQL Serveruse the Remote Query Timeout:

对于SQL Server使用Remote Query Timeout：

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

default is 5 seconds.

默认为 5 秒。

回答by Anton

For SQLite3.28.0:

对于SQLite3.28.0：

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

will set the connection timeout to 1000 seconds.

将连接超时设置为 1000 秒。

回答by MarredCheese

For a db2 backend via ibm_db2_sa+ pyodbc:

对于通过ibm_db2_sa+的 db2 后端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

我查看了源代码，从 0.3.5 (2019/05/30) 版本开始，似乎没有办法控制连接超时：https: //github.com/ibmdb/python-ibmdbsa

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

我发布这个是为了省去别人看的麻烦。

回答by dappiu

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

对于使用Flask-SQLAlchemy而不是普通 SQLAlchemy 的人，您可以选择两种将值传递给 SQLAlchemy 的方法create_engine：

1. Use SQLALCHEMY_ENGINE_OPTIONSconfiguration key (Flask-SQLAlchemy>=2.4 required)

1. 使用SQLALCHEMY_ENGINE_OPTIONS配置键（需要 Flask-SQLAlchemy>=2.4）

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

1. Or, in alternative, use engine_optionwhen instantiating flask_sqlalchemy.SQLAlchemy

2. 或者，在engine_option实例化时使用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_timeoutargument 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.

编辑：示例使用的connect_timeout参数（至少）适用于 MySQL 和 PostgreSQL（值表示秒），其他 DBMS 可能需要传递不同的参数名称以影响连接超时。我建议检查您的 DBMS 手册以检查此类选项。