How to preserve database connection in a python web server

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.

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

In my experience, it's often a good idea to close connections frequently. In particular, MySQL likes to close connections that have been idle for a while, and sometimes that can leave the persistent connection in a stale state that can make the application unresponsive.

What you really want to do is optimize the "dead connection time", the fraction of the time a connection is up but isn't doing any work. In the case of creating a new connection with every request, that dead time is really just the setup and teardown time. If only make a connection once (per thread), and it never goes bad, then the dead time is the idle time.

When your application is serving only a few requests, the number of connections that occur will also be small, and so there's not much advantage of keeping a connection open, but idle. On the other extreme, when the application is very busy, connections are almost never idle, and closing a connection that will just be reopened immediately is also wasted. In the middle, when new requests sometimes follow in flight requests, but sometimes not, you'll have to do some performance tuning on things like pool size, request timeout, and so on.

A very busy app, which uses a connection pool to keep connections open will only ever see one kind of dead time; waiting for requests that will never return because the connection has gone bad. A simple solution to this problem is to execute a known, good query (which in MySQL is spelled SELECT 1) before providing a connection from the pool to a request and recycle the connection if it doesn't return quickly.