How to use "INSERT" in psycopg2 connection pooling?

Question

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.

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


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


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

I don't get id of inserted record without commit().

Answer 1

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)


@contextmanager
def db():
    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)

Answer 2

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)

with get_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)


with get_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.

Answer 3

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():
    with get_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()