Database Access Strategy for Python Web Application

Question

I have been writing a Python web application (in Flask) for a while, and I do not believe that I fully understand how database access should work for several request / response cycles. Prior to Python, my web programming experience was in PHP (worth several years), and I am afraid that my PHP experience will mislead some of my Python work.

In PHP, each new query creates a new database connection because nothing is shared between the queries. The more queries you have, the more connections you need to support. However, in a Python web application, where there is a common state between requests, database connections can persist.

Therefore, I need to manage these connections and ensure that I close them. In addition, I need to have some kind of connection pool, because if I have only one connection, divided by all requests, the requests can block waiting for access to the database if I do not have enough available connections.

Is this the correct understanding? Or am I well defined differences? In a Python web application, do I need to have a database connection pool that shares its connections across many requests? And the number of connections in the pool will depend on the loading of my application?

I am using Psycopg2.

Answer 1

Have you looked at SQLAlchemy at all? He takes care of a lot of dirty parts - he maintains a pool of connections and reuses / closes them as needed.

Answer 2

to avoid connection costs, it seems that the best way is to use a join.
either directly from psycopg2.pool from the documents , or using a higher level abstraction layer like SQL Alchemy, as emphasized here.