PostgresDB与psycopg2查询执行成功，但有时更新记录，有时不

Question

我试图用psycopg2与下面的查询更新PostgresDB，它执行成功，但有时它不更新记录(有时更新，有时不)。有人能帮帮我吗?

下面的代码显示"订单状态已更新"，但返回的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)

Answer 1

将cur.close()移动到最后一个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)