如何恰当的关闭sqlalchemy数据库连接

我会带着你远行 2023-03-02 05:28 17阅读 0赞

## 1.疑问来源 ##

时常使用 sqlalchemy 建立 engine 连接 mysql 或者 postgres ，然后利用 pandas 与数据库进行交互。一般情况下，使用结束后，就随手将 jupyter notebook 关闭了，从来没有刻意的去管 sqlalchemy engine 没有关闭后，是否会造成数据库资源浪费的问题，于是就有了这个疑问。

## 2.疑问解决 ##

to be continued

前期结论：

1.  如果不是疯狂的建立连接，MySQL/Postgreps 会自动关闭连接。

## 3.资料汇总 ##

### 3.1 con.close() vs con.dispose() ###

Q1:

*  pandas.read\_sql 函数中，执行完毕后，con参数（SQLAlchemy connectable）是否会释放资源——con.close()
 *  整体 python 脚本执行结束后，有无必要释放 con —— con.dispose()
 *  SQLAlchemy create\_engine 的 engine 是什么？
    
     *  [Engine Configuration][], 中文版：[引擎配置][Link 1]
     *

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
        
    con : SQLAlchemy connectable (engine/connection) or database string URI
    or DBAPI2 connection (fallback mode)
    Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

3.1.1 引子——他山之石

> 来自 [python如何正确关闭数据库的链接，尤其sqlalchemy connection in MySQL][python_sqlalchemy connection in MySQL]  
> `from sqlalchemy import create_engine`  
> `dataIn2File.to_sql`中`dataIn2File`为 pandas 的 DataFrame 数据类型

现在，基本上大家都采用python3。而python3中，用的较多的模块是sqlalchemy ，来链接mysql。然而，一般的例子中都没有涉及关闭链接。

例如：

conn = create_engine('mysql+pymysql:user:passwd@host:port/db?charset=etf-8')
    try:
        dataIn2File.to_sql(table_name,con=conn,if_exists='append',index=False)
    except Exception as ee:
        logger.error('fileToMysql fialed',ee)
        traceback.print_exc()

以上代码就没有关闭过conn，导致数据库的链接越来越多，最后无法新建链接。  
查询过相关知识，发现了以下两种方式：

conn = create_engine('mysql+pymysql:user:passwd@host:port/db?charset=etf-8')
    con = conn.connect()
    try:
        dataIn2File.to_sql(table_name,con=con,if_exists='append',index=False)
    except Exception as ee:
        logger.error('fileToMysql fialed',ee)
        traceback.print_exc()
    finally:
        con.close()

或者

然而，事实上，链接依旧没有关闭。若要真正关闭链接，则需要采用：`conn.dispose()`

经过1个小时多的探索，终于大功告成。

### 3.2 Google 探索 ###

Search word:

1.  how long postgres auto close connection
2.  how long mysql auto close connection

3.2.1 MySQL自动关闭空闲连接时间

> MySQL默认自动关闭连接时间（interactive\_timeout, wait\_timeout ）为8小时  
> [How long is “too long” for MySQL Connections to sleep?][How long is _too long_ for MySQL Connections to sleep]

`mysqld` will timeout database connections based on two server options:

*  [interactive\_timeout][interactive_timeout]
 *  [wait\_timeout][wait_timeout]

1. interactive_timeout
    The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
    2. wait_timeout
    The number of seconds the server waits for activity on a noninteractive connection before closing it.
    On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

Both are 28,800 seconds (8 hours) by default.

You can set these options in `/etc/my.cnf`

If your connections are persistent (opened via `mysql_pconnect`) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in `my.cnf` (takes effect after `mysqld` is restarted):

[mysqld]
    interactive_timeout=180
    wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
    SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.

## 4.资料链接 ##

*  [python如何正确关闭数据库的链接，尤其sqlalchemy connection in MySQL][python_sqlalchemy connection in MySQL]
 *  [What does IDLE state denotes in a row of pg\_stat\_activity?][What does IDLE state denotes in a row of pg_stat_activity] `select * from pg_stat_activity;`
 *  [How to close idle connections in PostgreSQL automatically?][How to close idle connections in PostgreSQL automatically]
 *  [连接池 — SQLAlchemy 1.4 Documentation][_ SQLAlchemy 1.4 Documentation]

[Engine Configuration]: https://www.lizenghai.com/archives/https%3A%2F%2Fdocs.sqlalchemy.org%2Fen%2F13%2Fcore%2Fengines.html
[Link 1]: https://www.lizenghai.com/archives/https%3A%2F%2Fwww.osgeo.cn%2Fsqlalchemy%2Fcore%2Fengines.html
[python_sqlalchemy connection in MySQL]: https://www.lizenghai.com/archives/http%3A%2F%2Fblog.sina.cn%2Fdpool%2Fblog%2Fs%2Fblog_751bd9440102xq11.html
[How long is _too long_ for MySQL Connections to sleep]: https://www.lizenghai.com/archives/https%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F1558%2Fhow-long-is-too-long-for-mysql-connections-to-sleep
[interactive_timeout]: https://www.lizenghai.com/archives/http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Fserver-system-variables.html%23sysvar_interactive_timeout
[wait_timeout]: https://www.lizenghai.com/archives/http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Fserver-system-variables.html%23sysvar_wait_timeout
[What does IDLE state denotes in a row of pg_stat_activity]: https://www.lizenghai.com/archives/https%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F202006%2Fwhat-does-idle-state-denotes-in-a-row-of-pg-stat-activity
[How to close idle connections in PostgreSQL automatically]: https://www.lizenghai.com/archives/https%3A%2F%2Fstackoverflow.com%2Fquestions%2F12391174%2Fhow-to-close-idle-connections-in-postgresql-automatically
[_ SQLAlchemy 1.4 Documentation]: https://www.lizenghai.com/archives/https%3A%2F%2Fwww.osgeo.cn%2Fsqlalchemy%2Fcore%2Fpooling.html