Question 113682 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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...""")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search