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

I found a configuration for commit: "con.autocommit = True", what can I do for rollback() — Foad Tahmasebi
– Foad Tahmasebi, Commented Apr 14, 2015 at 9:39

valentin · Accepted Answer · 2015-04-14 11:47:58Z

14

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.

edited Apr 14, 2015 at 11:47

answered Apr 14, 2015 at 11:28

valentin

3,62817 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Foad Tahmasebi Over a year ago

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

Foad Tahmasebi Over a year ago

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

valentin Over a year ago

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

Foad Tahmasebi Over a year ago

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!

valentin Over a year ago

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.

|

David Dehghan · Accepted Answer · 2018-06-02 20:31:02Z

6

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

answered Jun 2, 2018 at 20:31

David Dehghan

25.3k11 gold badges113 silver badges101 bronze badges

Comments

ramazan polat · Accepted Answer · 2021-06-17 08:16:55Z

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)

Collectives™ on Stack Overflow

How to use "INSERT" in psycopg2 connection pooling?

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related