Geek Answers Handbook

How to use "INSERT" in psycopg2 connection pool?

I am using psycopg2 to connect to PostgreSQL in Python and I want to use the connection pool.

I do not know what I should do instead of commit () and rollback () when I execute an INSERT request.

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 do not get the id of the inserted record without commit ().

+7
source share
3 answers

UPDATE I can’t check the code, but I give you some ideas: You are committing 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()

A connection pool exists because creating a new connection to db can be expensive and cannot avoid commits or rollbacks. Thus, you can fix your data without any problems, fixing the data will not lead to the destruction of the connection.

+5
source

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()
+3
source

I think it 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: cursor.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)
0
source

More articles:


All Articles