One connection to DB for app, or a connection on every execution?

Question

I'm using psycopg2 library to connection to my postgresql database. Every time I want to execute any query, I make a make a new connection like this:

import psycopg2

def run_query(query):
    with psycopg2.connect("dbname=test user=postgres") as connection:
        cursor = connection.cursor()
        cursor.execute(query)
        cursor.close()

But I think it's faster to make one connection for whole app execution like this:

import psycopg2

connection = psycopg2.connect("dbname=test user=postgres")


def run_query(query):
    cursor = connection.cursor()
    cursor.execute(query)
    cursor.close()

So which is better way to connect my database during all execution time on my app?

I've tried both ways and both worked, but I want to know which is better and why.

Answer 1

You should strongly consider using a connection pool, as other answers have suggested, this will be less costly than creating a connection every time you query, as well as deal with workloads that one connection alone couldn't deal with.

Create a file called something like mydb.py, and include the following:

import psycopg2
import psycopg2.pool
from contextlib import contextmanager

dbpool = psycopg2.pool.ThreadedConnectionPool(host=<<YourHost>>,
                                      port=<<YourPort>>,
                                      dbname=<<YourDB>>,
                                      user=<<YourUser>>,
                                      password=<<YourPassword>>,
                                      )

@contextmanager
def db_cursor():
    conn = dbpool.getconn()
    try:
        with conn.cursor() as cur:
            yield cur
            conn.commit()
    # You can have multiple exception types here.
    # For example, if you wanted to specifically check for the
    # 23503 "FOREIGN KEY VIOLATION" error type, you could do:
    # except psycopg2.Error as e:
    #     conn.rollback()
    #     if e.pgcode = '23503':
    #         raise KeyError(e.diag.message_primary)
    #     else
    #         raise Exception(e.pgcode)
    except:
        conn.rollback()
        raise
    finally:
        dbpool.putconn(conn)

This will allow you run queries as so:

import mydb

def myfunction():
    with mydb.db_cursor() as cur:
        cur.execute("""Select * from blahblahblah...""")