How to close sqlalchemy connection in MySQL

by

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.

More Related Contents:

  1. Database returned an invalid value in QuerySet.dates()
  2. MySQL DROP all tables, ignoring foreign keys
  3. How to start MySQL server from command line on Mac OS Lion?
  4. MySQL: Check if the user exists and drop it
  5. Autostart MySQL Server on Mac OS X Yosemite/El Capitan
  6. MySQL LIMIT on DELETE statement
  7. Select all where [first letter starts with B]
  8. CakePHP – get last query run
  9. Get the second highest value in a MySQL table
  10. Call a stored procedure for each row returned by a query in MySQL
  11. MySQL ‘select for update’ behaviour
  12. How to select non “unique” rows
  13. Why is connecting to MySQL server so slow?
  14. MySQL disable all triggers
  15. Retrieving only a fixed number of rows in MySQL
  16. How do I fix the ‘Out of range value adjusted for column’ error?
  17. MySQL Closure Table hierarchical database – How to pull information out in the correct order
  18. Fetching a Single Row from Join Table
  19. why this mysql event can’t get run?
  20. Is it unreasonable to assign a MySQL database to each user on my site?
  21. Mysql Counting the consecutive rows that match
  22. Specific Date Format in mysql
  23. MYSQL Truncated incorrect DOUBLE value
  24. Last index of a given substring in MySQL
  25. Create a new database with MySQL Workbench
  26. Connect to mysql server without sudo
  27. Generate serial number in mysql query
  28. What are the benefits of only_full_group_by mode?
  29. How to search and replace all instances of a string within a database?
  30. mySQL SELECT upcoming birthdays

Leave a Comment