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

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.

CodePudding user response：

Both ways are bad. The fist one is particularly bad, because opening a database connection is quite expensive. The second is bad, because you will end up with a single connection (which is too few) one connection per process or thread (which is usually too many).

Use a connection pool.

CodePudding user response：

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...""")