How To Use "insert" In Psycopg2 Connection Pooling?

I use psycopg2 to connect to PostgreSQL on Python and I want to use connection pooling. I don't know what should I do instead commit() and rollback() when I execute INSERT query. d

Solution 1:

UPDATE I can not test the code but I give you some ideas: You do the commit in connection not in db

# Get Cursor
@contextmanager
def get_cursor():
    con = db.getconn()
    try:
        yield con
    finally:
        db.putconn(con)

withget_cursor() as cursor:
    con.cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
    con.commit()
    id = cursor.fetchone()

or

# Get Cursor
@contextmanager
def get_cursor():
    con = db.getconn()
    try:
        yield con.cursor()
        con.commit()
    finally:
        db.putconn(con)


withget_cursor() as cursor:
    con.cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
    id = cursor.fetchone()

---

Connection pooling exist because creating a new connection to a db can be expensive and not to avoid commits or rollbacks. So you can commit your data without any issue, committing data will not destroy the connection.

Solution 2:

here is my working example:

db = pool.SimpleConnectionPool(1, 10,host=conf_hostname,database=conf_dbname,user=conf_dbuser,password=conf_dbpass,port=conf_dbport)


@contextmanager
def get_connection():
    con = db.getconn()
    try:
        yield con
    finally:
        db.putconn(con)

def write_to_db():
    withget_connection() as conn:
        try:
            cursor = conn.cursor()
            cursor.execute("INSERT INTO table (fields) VALUES (values) RETURNING id") 
            id = cursor.fetchone()
            cursor.close()
            conn.commit()
        except:
            conn.rollback()

Solution 3:

I think this will be a little more pythonic:

db_pool = pool.SimpleConnectionPool(1, 10,
                                    host=CONF.db_host,
                                    database=CONF.db_name, 
                                    user=CONF.db_user, 
                                    password=CONF.db_user,
                                    port=CONF.db_port)


@contextmanagerdefdb():
    con = db_pool.getconn()
    cur = con.cursor()
    try:
        yield con, cur
    finally:
        cur.close()
        db_pool.putconn(con)


if __name__ == '__main__':
    with db() as (connection, cursor):
        try:
            cursor.execute("""INSERT INTO table (fields)
VALUES (values) RETURNING id""")
            my_id = cursor.fetchone()
            rowcount = cursor.rowcount
            if rowcount == 1:
                connection.commit()
            else:
                connection.rollback()
        except psycopg2.Error as error:
            print('Database error:', error)
        except Exception as ex:
            print('General error:', ex)