如何关闭 MySQL 中的 sqlalchemy 连接

How to close sqlalchemy connection in MySQL

提问by martincho

This is a sample code I'd like to run:

这是我想运行的示例代码：

for i in range(1,2000):
    db = create_engine('mysql://root@localhost/test_database')
    conn = db.connect()
    #some simple data operations
    conn.close()
    db.dispose()

Is there a way of running this without getting "Too many connections" errors from MySQL? I already know I can handle the connection otherwise or have a connection pool. I'd just like to understand how to properly close a connection from sqlalchemy. Thanks in advance!

有没有办法运行它而不会从 MySQL 收到“连接过多”的错误？我已经知道我可以以其他方式处理连接或拥有连接池。我只想了解如何正确关闭来自 sqlalchemy 的连接。提前致谢！

回答by zzzeek

Here's how to write that code correctly:

以下是正确编写该代码的方法：

db = create_engine('mysql://root@localhost/test_database')
for i in range(1,2000):
    conn = db.connect()
    #some simple data operations
    conn.close()
db.dispose()

That is, the Engineis a factoryfor connections as well as a poolof connections, not the connection itself. When you say conn.close(), the connection is returned to the connection pool within the Engine, not actually closed.

也就是说，它Engine是连接工厂和连接池，而不是连接本身。当您说 时conn.close()，连接返回到 Engine 内的连接池，而不是实际关闭。

If you do want the connection to be actually closed, that is, not pooled, disable pooling via NullPool:

如果您确实希望连接实际关闭，即不池化，请通过NullPool以下方式禁用池化：

from sqlalchemy.pool import NullPool
db = create_engine('mysql://root@localhost/test_database', poolclass=NullPool)

With the above Engineconfiguration, each call to conn.close()will close the underlying DBAPI connection.

通过上面的Engine配置，每次调用conn.close()都会关闭底层的 DBAPI 连接。

If OTOH you actually want to connect to differentdatabases on each call, that is, your hardcoded "localhost/test_database"is just an example and you actually have lots of different databases, then the approach using dispose()is fine; it will close out every connection that is not checked out from the pool.

如果 OTOH 你真的想在每次调用时连接到不同的数据库，也就是说，你的硬编码"localhost/test_database"只是一个例子，你实际上有很多不同的数据库，那么使用的方法dispose()很好；它将关闭所有未从池中检出的连接。

In all of the above cases, the important thing is that the Connectionobject is closed via close(). If you're using any kind of "connectionless" execution, that is engine.execute()or statement.execute(), the ResultProxyobject returned from that execute call should be fully read, or otherwise explicitly closed via close(). A Connectionor ResultProxythat's still open will prohibit the NullPoolor dispose()approaches from closing every last connection.

在上述所有情况下，重要的是Connection对象是通过close(). 如果您使用任何类型的“无连接”执行，即engine.execute()或statement.execute()，ResultProxy则应完全读取从该执行调用返回的对象，或者通过close(). 一个Connection或ResultProxy这仍然开放将禁止NullPool或dispose()从关闭每一个最后的连接方法。

回答by Romuald Brunet

Tried to figure out a solution to disconnect from database for an unrelated problem (must disconnect before forking).

试图找出一个解决方案，以断开与数据库无关的问题（必须在分叉之前断开连接）。

You need to invalidatethe connection from the connection Pool too.

您也需要使连接池中的连接无效。

In your example:

在你的例子中：

for i in range(1,2000):
    db = create_engine('mysql://root@localhost/test_database')
    conn = db.connect()
    # some simple data operations
    # session.close() if needed
    conn.invalidate()
    db.dispose()