Python PostgreSQL: gestión de transacciones mediante Commit y Rollback

En este artículo, vamos a ver cómo administrar transacciones usando Commit and Rollback. 

En pyscopg, la clase de conexión en psycopg se encarga de procesar las transacciones. Cuando usa un objeto de cursor para emitir su primera declaración SQL a la base de datos PostgreSQL, psycopg genera una nueva transacción. Psycopg ejecuta todas las declaraciones siguientes en la misma transacción a partir de ese momento. Psycopg detendrá la transacción si alguna declaración falla. Commit() y rollback() son dos métodos de la clase de conexión que pueden usarse para detener una transacción. La función commit() se usa para confirmar permanentemente todos los cambios en la base de datos de PostgreSQL. También puede usar la función rollback() para deshacer cualquier modificación que haya realizado. 

Comprometerse:

conexión.commit()

Retroceder:

conexión.rollback()

El siguiente es un ejemplo de cómo manejar una transacción en psycopg, la forma más básica de manejarla.

La tabla de la base de datos en la que estamos trabajando se ve así: 

Haga clic aquí para ver y descargar el archivo CSV.

Cree la tabla de ventas en PostgreSQL e importe el archivo CSV:

# import packages
import psycopg2
import pandas as pd
from sqlalchemy import create_engine
 
# establish connections
conn_string = 'postgres://postgres:pass@127.0.0.1/SuperMart'
 
db = create_engine(conn_string)
conn = db.connect()
conn1 = psycopg2.connect(
  database="SuperMart", user='postgres',
  password='pass', host='127.0.0.1', port='5432'
)
 
conn1.autocommit = True
cursor = conn1.cursor()
 
# drop table if it already exists
cursor.execute('drop table if exists sales')
 
sql = '''CREATE TABLE sales(Order_Line int,\
Order_ID char(20),Order_Date Date,Ship_Date Date,\
Ship_Mode char(20) ,Customer_ID char(20),Product_ID char(20),\
Sales decimal,Quantity int,Discount decimal,Profit decimal);'''
 
cursor.execute(sql)
 
# import the csv file to create a dataframe
data = pd.read_csv("Sales.csv")
 
# converting data to sql
data.to_sql('sales', conn, if_exists='replace')
 
# fetching all rows
sql1 = '''select * from sales;'''
cursor.execute(sql1)
for i in cursor.fetchall():
    print(i)
 
conn1.commit()
conn1.close()

Ejemplo 1: Ejemplo de una transacción exitosa

El código comienza con la importación de paquetes, estableciendo una conexión a la base de datos y debemos asegurarnos de que connection.autocommit sea falso porque por defecto es True, confirma todos los cambios de antemano y no podemos usar rollback() para volver al estado anterior . se crea un cursor con el método connection.cursor(). los datos son consultados y obtenidos. se calculan las ventas de un order_id en particular. como la instrucción SQL no tiene ningún error, la transacción finaliza con éxito.

# import packages
import psycopg2
 
try:
    # establish a connection to the database
    connection = psycopg2.connect(
        database="SuperMart", user='postgres',
        password='PASS', host='127.0.0.1', port='5432')
     
    # disable autocommit mode
    connection.autocommit = False
     
    # creating a cursor object
    cursor = connection.cursor()
 
    # querying data
    query = """select sales from sales where\
    Order_ID = 'CA-2016-152156'"""
    cursor.execute(query)
    record = cursor.fetchall()
    sum = 0
    for i in record:
        sum = sum+i[0]
    print("total sales from the order id CA-2016-152156 is : " + str(sum))
 
    # commiting changes
    connection.commit()
    print("successfully finished the transaction ")
 
except (Exception, psycopg2.DatabaseError) as error:
    print("Error in transaction, reverting all changes using rollback ", error)
    connection.rollback()
 
finally:
 
    # closing database connection.
    if connection:
       
        # closing connections
        cursor.close()
        connection.close()
        print("PostgreSQL database connection is closed")

Producción:

las ventas totales del ID de pedido CA-2016-152156 son: 993.9000000000001

terminó con éxito la transacción 

La conexión de la base de datos PostgreSQL está cerrada

Ejemplo 2: Ejemplo de una transacción fallida

El código es similar al anterior, excepto que se da un nombre de tabla incorrecto en la declaración SQL, ya que es incorrecto, todos los cambios se revierten o deshacen usando el método rollback() y la conexión se cierra.

# import packages
import psycopg2
 
try:
    # establish a connection to the database
    connection = psycopg2.connect(database="SuperMart",
                                  user='postgres',
                                  password='sherlockedisi',
                                  host='127.0.0.1',
                                  port='5432')
    # disable autocommit mode
    connection.autocommit = False
     
    # creating a cursor object
    cursor = connection.cursor()
 
    # querying data
    query = """select sales from sales1 \
    where Order_ID = 'CA-2016-1521591'"""
    cursor.execute(query)
     
    # fetching data
    record = cursor.fetchall()
    sum = 0
    for i in record:
        sum = sum+i[0]
    print("total sales from the order\
    id CA-2016-152159 is : " + str(sum))
 
    # commiting changes
    connection.commit()
    print("successfully finished the transaction ")
 
except (Exception, psycopg2.DatabaseError) as error:
    print("Error in transaction, reverting all\
    changes using rollback, error is : ", error)
    connection.rollback()
 
finally:
 
    # closing database connection.
    if connection:
       
        # closing connections
        cursor.close()
        connection.close()
        print("PostgreSQL database connection is closed")

Producción:

Error en la transacción, revirtiendo todos los cambios usando rollback, el error es: la relación «ventas1» no existe

LÍNEA 1: seleccione ventas de ventas1 donde order_id = ‘CA-2016-1521591’

                          ^