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

Funderburk

I found a configuration for commit: "con.autocommit = True", what can I do for rollback() – Funderburk

Answer 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)

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.

Angle

thank you. but how to commit data? db.commit() does not exist! – Funderburk

or may be I should use "con.commit()" and "con.rollback()", yes? – Funderburk

yes, "con.commit()" and "con.rollback()" mut be used – Angle

thank you for answer! I have another question about connection pooling. "what happen if I open a connection in my program and don't close it?instead connection pooling". my program runs as a service and listens to a socket for packets. I want to save more than 1000 record per second! – Funderburk

Connection pooling is for sharing the same connection, instead of opening 100 connections to db I open only one and I use 100 times. Doesn't listen to any socket, is not a service, it is only a client. – Angle

If you will not close the connection, it will remain open until the program is closed or killed. – Angle

Answer 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():
    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()

Semiporcelain

Answer 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)


@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)

Inspirit