PostgresDB with psycopg2 query executing successfully but sometimes updating the record and sometimes not

Question

I am trying to update PostgresDB with psycopg2 with below query, it is executing successfully but sometimes it is not updating the record (sometimes updating and sometimes not). Can someone help me?

Below code printed 'Order status updated' but returned rowcount =0.

def update_order_status(order_id, executed_at, order_executed, exec_completed, conn_prod):
    
    rowcount = 0
    sql = """UPDATE orders SET status = %s, executed_type = %s, executed_at= %s 
            WHERE order_id = %s"""
    try:    
        cur = conn_prod.cursor()
        cur.execute(sql, (order_executed, exec_completed, executed_at, order_id))
        conn_prod.commit()
        rowcount = cur.rowcount
        cur.close()
        print('Order status updated')
        return rowcount
    except Exception as e:
        print(e)
        conn_prod.rollback()
        return rowcount
        
count = update_order_status('1234', '2021-10-26 13:30:18+00', 'COMPLETED', 'FULL', conn_prod)

Are you hitting the `Exception` portion of the code? – Adrian Klaver Oct 26 '21 at 14:47 — Adrian Klaver, Oct 26 '21 at 14:47

Anand Tripathi · Answer 1 · 2021-10-26T15:10:46.640

0

Just move the cur.close() to finally block

def update_order_status(order_id, executed_at, order_executed, exec_completed, conn_prod):
    
    rowcount = 0
    sql = """UPDATE orders SET status = %s, executed_type = %s, executed_at= %s 
            WHERE order_id = %s"""
    cur = conn_prod.cursor()
    try:    
        cur.execute(sql, (order_executed, exec_completed, executed_at, order_id))
        conn_prod.commit()
        rowcount = cur.rowcount
        print('Order status updated')
    except Exception as e:
        print(e)
        conn_prod.rollback()
    finally:
        cur.close()
    return rowcount 
        
count = update_order_status('1234', '2021-10-26 13:30:18+00', 'COMPLETED', 'FULL', conn_prod)

edited Oct 26 '21 at 15:10

answered Oct 26 '21 at 12:48

Anand Tripathi

14,556
1
47
52

1

Thanks for the answer Anand. How does it make difference if I move cur.close() to finally block? (just wanted to understand the reason) – anilraj Oct 26 '21 at 13:58
Cursor is not being closed in the exception block it is only closed in try block. So if you encountered any error it will not be closed. – Anand Tripathi Oct 26 '21 at 14:08
Any chance it has helped you? – Anand Tripathi Oct 26 '21 at 14:41
Doubt that this has anything to do with it, the `conn_prod.commit()` is the important part. – Adrian Klaver Oct 26 '21 at 14:49
Code executed without any errors but not updated in the table (if i consider closing curser might cause problem). My application will be available between 9am-3pm (IST), will test and update the results. – anilraj Oct 26 '21 at 15:08
Above code printed 'Order status updated' also but returned rowcount =0). – anilraj Oct 26 '21 at 15:09
My bad updated the code – Anand Tripathi Oct 26 '21 at 15:11

PostgresDB with psycopg2 query executing successfully but sometimes updating the record and sometimes not

1 Answers1