Ejecute el procedimiento almacenado y la función de PostgreSQL en Python

Un procedimiento almacenado es una secuencia de consultas de lenguaje procesal estructurado almacenadas en un sistema de administración de base de datos relacional como un diccionario de datos, que se puede compartir y reutilizar varias veces. Todas las operaciones CRUD, las operaciones de consulta se pueden realizar llamando al procedimiento almacenado. El uso de procedimientos almacenados reduce la repetición de código que se usa una y otra vez. En este artículo, analicemos cómo ejecutar el procedimiento almacenado y la función de PostgreSQL en python.

Creación de un procedimiento almacenado

• El primer paso es escribir un procedimiento almacenado, la sintaxis es similar a la de las sentencias SQL convencionales.
• El siguiente fragmento de código muestra un procedimiento almacenado que extrae los nombres de los libros y la identificación del libro, cuyas ventas han superado una cierta cantidad de referencia.
• Este procedimiento almacenado se llama get_book_sales(sale_amount), que toma sale_amount como un parámetro donde se debe pasar el monto de las ventas de referencia.
• El procedimiento almacenado contiene una consulta anidada que extrae los nombres de los libros y los identificadores de los libros que cruzaron la cantidad de ventas de referencia.
• Este procedimiento almacenado se puede ejecutar cuando sea necesario.
• Guarde el siguiente fragmento en su base de datos PostgreSQL como un procedimiento almacenado.

CREAR O REEMPLAZAR LA FUNCIÓN get_book_sales(sale_amount integer)

TABLA DE DEVOLUCIONES (nombre_libro VARCHAR, id_libro INTEGER, tienda VARCHAR) COMO

ps

EMPEZAR

DEVOLVER CONSULTA

SELECCIONE libros.id, libros.nombre_libro,

DESDE libros donde libros.book_id IN

(SELECCIONE book_id FROM book_sales donde book_sales.sales > sale_amount)

FINAL

ps

IDIOMA plpgsql

Ejecución de funciones y procedimientos almacenados de PostgreSQL en Python

• Una vez que se crea el procedimiento almacenado, podemos definir una función que ejecute este procedimiento almacenado cuando se invoque esta función. Llamemos a esta función get_sale_benchmark(sale_amount) que toma el monto de las ventas como punto de referencia.
• Establezca la conexión a la base de datos PostgreSQL desde python pasando la string de conexión a la función psycopg2.connect().
• Una vez que se establece la conexión, cree una instancia del objeto cursor() en una variable denominada motor
• Luego llame a la función psycopg2 callproc(), que toma dos parámetros, el nombre del procedimiento almacenado y los parámetros del procedimiento almacenado.

Sintaxis:

cursor.callproc(procname[, parameters])

• El resultado de este procedimiento almacenado se almacena en el objeto del motor. Se puede acceder a esto usando la función fetchall().
• Más tarde, use esta función fetchall() para iterar a través de todas las filas devueltas por el procedimiento almacenado como se muestra a continuación.
• Para capturar cualquier error mientras se ejecuta el procedimiento almacenado en una base de datos y cerrar la conexión sin problemas después de confirmar todos los cambios, use probar, esperar y finalmente bloquear como se muestra en el código a continuación.
• Pruebe la función get_sales_benchmark() llamando a la función con un sales_amount apropiado.

Explicación:

Obtiene todas las filas del resultado de una consulta y las devuelve como una lista de tuplas. Se devuelve una lista vacía si no hay registros.

import psycopg2
  
def get_sale_benchmark(sale_amount):
    connector = None
    try:
        conn_string = "host='host_name' dbname='database_name'\
                        user='user_name' password='your_password'"
        connector = psycopg2.connect(conn_string)
        engine = connector.cursor()
  
        # call stored procedure
        engine.callproc('get_book_sales', [sale_amount, ])
  
        print("fechting Book list that has crosssed sales benchmark")
        result = cursor.fetchall()
        for row in result:
            print("Book Id = ", row[0], )
            print("Book Name = ", row[1])
  
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error while connecting to PostgreSQL", error)
  
    finally:
        
        # closing database connection.
        if connector:
            engine.close()
            connector.close()
            print("PostgreSQL connection is closed")
  
  
get_sale_benchmark(500)

Producción: