Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 243
martincho Avatar asked Dec 27 '11 13:12

martincho


People also ask

Does SQLAlchemy close connection automatically?

close() method is automatically invoked at the end of the block. The Connection , is a proxy object for an actual DBAPI connection. The DBAPI connection is retrieved from the connection pool at the point at which Connection is created.

Can I use SQLAlchemy with MySQL?

SQLAlchemy supports MySQL starting with version 5.0. 2 through modern releases, as well as all modern versions of MariaDB.


1 Answers

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.

like image 179
zzzeek Avatar answered Sep 21 '22 00:09

zzzeek