如何在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()

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获得“Too many connections”错误？我已经知道我可以处理连接，或者有一个连接池。我只想了解如何正确关闭sqlalchemy的连接。提前致谢！

2 个解决方案

#1

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

也就是说，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 Engine configuration, each call to conn.close() will close the underlying DBAPI connection.

使用上面的Engine配置，每次调用conn.close（）都将关闭底层的DBAPI连接。

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.

如果OTOH你真的想在每次调用时连接到不同的数据库，也就是说，你的硬编码“localhost / test_database”只是一个例子而你实际上有很多不同的数据库，那么使用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.

在所有上述情况中，重要的是Connection对象通过close（）关闭。如果您正在使用任何类型的“无连接”执行，即engine.execute（）或statement.execute（），则应完全读取从该执行调用返回的ResultProxy对象，或通过close（）显式关闭。仍处于打开状态的Connection或ResultProxy将禁止NullPool或dispose（）方法关闭每个最后一个连接。

#2

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

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

You need to invalidate the 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()

#1