Psycopg2 And Infinite Python Script

I have an infinite script written in Python which connects to Postgresql and inserts there a record when the person appears in front of the camera connected to my computer. I would

Solution 1:

A connection pool works well for this kind of thing. I have not worked with it in production (using mainly Django or SQLAlchemy), but psycopg2.pool includes a few different implementations (SimpleConnectionPool or PersistentConnectionPool) that would probably fit your need. Generally speaking, a pool not only helps with managing connections as a shared resource, but also testing and re-initializing the connection when it's needed.

from psycopg2 import pool
conn_pool = pool.PersistentConnectionPool(minconn, maxconn, **dbopts)

def work_method():
    conn = conn_pool.getconn()
    with conn.cursor() as stmt:
        stmt.execute(sql)
    conn_pool.putconn(conn)

The putconn is extremely important, so that an exception doesn't leave the pool thinking the connection is still in use. Would be good to handle it as a context manager:

import contextlib

@contextlib.contextmanager
def get_db_connection():
    conn = conn_pool.getconn()
    yield conn
    conn_pool.putconn(conn)

def work_method():
    withget_db_connection() as conn:
        with conn.cursor() as stmt:
            stmt.execute(sql)

Hope that helps.