105

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.

starball
  • 20,030
  • 7
  • 43
  • 238
martincho
  • 4,517
  • 7
  • 32
  • 42

4 Answers4

194

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.

zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • 10
    `dispose()`!! I needed it to make a session close, as `session.close()` did not suffice. I recommend adding this into the SQLAlchemy documentation, based on my experience it is not clear how to properly close a session. – fedorqui Feb 12 '14 at 13:09
  • 3
    you call close(), as documented. dispose() is not needed and in fact calling dispose() explicitly is virtually never needed for normal SQLAlchemy usage. Feel free to email the [mailing list](http://groups.google.com/group/sqlalchemy) with information about your specific issue, perhaps you executed some special directives like "SET" on your Session which are not cleared out when using pooling (there's a different way to handle that). – zzzeek Feb 12 '14 at 18:03
  • I just posted the info in a new question: [How can I properly close a SQLAlchemy session?](http://stackoverflow.com/questions/21738944/how-can-i-properly-close-a-sqlalchemy-session) If it is OK for you to answer here. Otherwise I will contact through the mailing list. Thank you very much! – fedorqui Feb 12 '14 at 20:37
  • @zzzeek a little late to the party, but just wanted to thank you for your incredible work. I've become a patron! (anyone else reading this should, too: https://www.patreon.com/join/zzzeek) – aaron Jun 17 '20 at 19:43
10

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()
Romuald Brunet
  • 5,595
  • 4
  • 38
  • 34
5

I use this one

engine = create_engine('...')
with engine.connect() as conn:
    conn.execute(text(f"CREATE SCHEMA IF NOT EXISTS...")
engine.dispose()
la_femme_it
  • 632
  • 10
  • 24
-1

In my case these always works and I am able to close! So using invalidate() before close() makes the trick. Otherwise close() sucks.

conn = engine.raw_connection()  
conn.get_warnings  = True
curSql = xx_tmpsql
myresults = cur.execute(curSql, multi=True)
print("Warnings: #####")
print(cur.fetchwarnings())
for curresult in myresults:
    print(curresult)
    if curresult.with_rows:
        print(curresult.column_names)
        print(curresult.fetchall())
    else:
        print("no rows returned")
cur.close()
conn.invalidate()
conn.close()     
engine.dispose()
wooper
  • 11
  • 2