如何关闭 MySQL 中的 sqlalchemy 连接

问题描述

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

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

有没有一种方法可以在不从 MySQL 中收到连接过多"错误的情况下运行它?我已经知道我可以以其他方式处理连接或拥有连接池.我只想了解如何正确关闭来自 sqlalchemy 的连接.提前致谢！

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!

推荐答案

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

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

也就是说，Engine 是连接的工厂以及连接的池，而不是连接本身.当你说conn.close()时，连接被返回到引擎内的连接池，而不是实际关闭.

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

如果您确实希望连接实际关闭，即不池化，请通过 NullPool 禁用池化:

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

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

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

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

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

If OTOH you actually want to connect to different databases 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.

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

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

这篇关于如何关闭 MySQL 中的 sqlalchemy 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！