How to preserve database connection in a python web server

Question

I am looking at the Flask tutorial, and it suggests to create a new database connection for each web request. Is it the right way to do things ? I always thought that the database connection should only once be created once for each thread. Can that be done, while maintaining the application as thread-safe, with flask, or other python web servers.

Answer 1

For learning purposes maybe yes. But in a real application running on a production environment that is not an ideal situation at all.

Normally, you would always want to have a connection pool between your application and the database. No matter what language/database you are using this is a common solution.

A database connection pool maintains open a number of connections. The application layer simply takes a connection that is not being used, the connections get released when the application layer doesn't need them anymore. By "released" I mean that they get returned to the pool in order to be used again.

Bottom line, connections are not open/close per request. They get acquired/released from/to the database connection pool.

For instance, with Python and mysql you could go for PySQLPool.

Answer 2

Is creating a new connection each request the way to go? No. For large applications we strongly recommend using SQLAlchemy (which can have a connection pool configured), even if you are not interested in the ORM. The docs have a section on that actually: http://flask.pocoo.org/docs/patterns/sqlalchemy/#sql-abstraction-layer