如何在MySQL中关闭sqlalchemy连接 [英] How to close sqlalchemy connection in MySQL

问题描述

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

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()，则应完全读取从该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连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！