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, Apr 14 '15 at 09:39

valentin · Accepted Answer · 2015-04-14T11:47:58.780

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 '15 at 11:47

answered Apr 14 '15 at 11:28

valentin

3,498
15
23

thank you. but how to commit data? db.commit() does not exist! – Foad Tahmasebi Apr 14 '15 at 11:41
or may be I should use "con.commit()" and "con.rollback()", yes? – Foad Tahmasebi Apr 14 '15 at 11:47
1

yes, "con.commit()" and "con.rollback()" mut be used – valentin Apr 14 '15 at 11:50
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! – Foad Tahmasebi Apr 14 '15 at 12:10
1

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. – valentin Apr 14 '15 at 12:19
If you will not close the connection, it will remain open until the program is closed or killed. – valentin Apr 14 '15 at 12:21

score 6 · Answer 2 · answered Jun 02 '18 at 20:31

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

ramazan polat · Answer 3 · 2021-06-17T08:16:55.070

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)

How to use "INSERT" in psycopg2 connection pooling?

3 Answers3

Linked