How to close sqlalchemy connection in MySQL
mysql, sqlalchemy
Solution
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.
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)
With the above `Engine` configuration, each call to `conn.close()` will close the underlying DBAPI connection.
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.
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.
Problem
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.