Realice operaciones PostgreSQL CRUD desde Python

El DDL se compone de las operaciones Crear, Leer, Actualizar, Eliminar ( CRUD ) que forman la columna vertebral de cualquier sistema de base de datos S QL. Discutamos cómo realizar operaciones CRUD en una base de datos PostgreSQL usando python.  Pyscopg2 es el modo más preferido y se usa ampliamente para conectar la base de datos PostgreSQL usando python. Pyscopg2 es una API de base de datos que es un controlador compatible con PostgreSQL que administra grupos de conexiones. 

En este artículo, veremos cómo conectarse a la base de datos PostgreSQL usando SQLAlchemy en python y también comprenderemos el procedimiento sobre cómo realizar operaciones CRUD en la base de datos PostgreSQL.

Estableciendo una conexión con la base de datos PostgreSQL

Como primeros pasos, establezca una conexión con su base de datos existente, utilizando la función connect() de Psycopg2 .

from psycopg2 import connect
 
# declare the connection string specifying
# the host name database name
# use name and password
conn_string = "host='host_name' \
dbname='database_name' user='user_name'\
password='your_password'"
 
# use connect function to establish the connection
conn = connect(conn_string)

Explicación:

host : nombre del host en el que se aloja la base de datos

Nombre de usuario – Nombre del administrador

Contraseña – Contraseña del administrador

dbname – nombre de la base de datos

Realización de la operación Crear en la base de datos PostgreSQL usando Python

• La sintaxis para crear una tabla es similar a la sintaxis utilizada en sentencias SQL convencionales. Cree diferentes tablas con las columnas requeridas y las restricciones de columna como se muestra. Establezca una conexión con PostgreSQL usando connect() como se discutió anteriormente
• Ahora, instancia un nuevo objeto cursor(). Un cursor es un puntero de solo lectura que permite que un programa acceda al conjunto de resultados de una consulta.
• Descarte cualquier tabla con el mismo nombre si ya existe. Luego, pase la consulta para crear una tabla a la función execute() de psycopg2.
• Finalmente, llame al método commit(), indicando el objeto conector que se creó para establecer una conexión con la base de datos para confirmar todos los cambios en la base de datos. Esto asegurará que se creen las tablas.

import psycopg2
 
# Establishing the connection
conn = psycopg2.connect(
    database="databasename",
    user='username',
    password='password',
    host='hostname',
    port='5432'
)
 
# Creating a cursor object using the
# cursor() method
cursor = conn.cursor()
 
# Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS publisher")
 
# Creating table as per requirement
sql = '''CREATE TABLE PUBLISHER(
                publisher_id SERIAL PRIMARY KEY,
                publisher_name VARCHAR(255) NOT NULL,
                publisher_estd INT,
                publsiher_location VARCHAR(255),
                publsiher_type VARCHAR(255)
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
 
# Closing the connection
conn.close()

Producción:

Table created successfully........

Creando una tabla en PostgreSQL usando Python

Realización de la operación Insertar en la base de datos PostgreSQL usando Python

• La sintaxis para insertar registros en una tabla es similar a la sintaxis utilizada en sentencias SQL convencionales. Establezca una conexión con la base de datos PostgreSQL como se mencionó anteriormente.
• Ahora, instancia un nuevo objeto cursor(). Un cursor es un puntero de solo lectura que permite que un programa acceda al conjunto de resultados de una consulta.
• Luego, pase la consulta para insertar registros de tabla a la función execute() de psycopg2.
• Finalmente, llame al método commit(), indicando el objeto conector que se creó para establecer una conexión con la base de datos para confirmar todos los cambios en la base de datos. Esto asegurará que se inserten los registros.

import psycopg2
 
 
try:
    connection = psycopg2.connect(user="username",
                                  password="password",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="databasename")
    cursor = connection.cursor()
 
    postgres_insert_query = """ INSERT INTO publisher(publisher_id,
    publisher_name, publisher_estd, publsiher_location, publsiher_type)
    VALUES (%s,%s,%s,%s,%s)"""
    record_to_insert = [(1, 'Packt', 1950,
                         'chennai', 'books'),
                        (2, 'Springer', 1950,
                         'chennai', 'books'),
                        (3, 'Springer', 1950,
                         'chennai', 'articles'),
                        (4, 'Oxford', 1950,
                         'chennai', 'all'),
                        (5, 'MIT', 1950,
                         'chennai', 'books')]
    for i in record_to_insert:
        cursor.execute(postgres_insert_query, i)
 
        connection.commit()
        count = cursor.rowcount
    print(count, "Record inserted successfully \
    into publisher table")
 
except (Exception, psycopg2.Error) as error:
    print("Failed to insert record into publisher table", error)
 
finally:
    # closing database connection.
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Producción:

1 registro insertado con éxito en la tabla del editor

La conexión de PostgreSQL está cerrada

Insertar registros en una tabla en PostgreSQL usando Python

Realización de la operación de lectura en la base de datos PostgreSQL usando Python

• Establezca una conexión con la base de datos PostgreSQL como se mencionó anteriormente.
• Ahora, instancia un nuevo objeto cursor(). Un cursor es un puntero de solo lectura que permite que un programa acceda al conjunto de resultados de una consulta.
• Luego, pase la consulta para crear una tabla a la función execute() de psycopg2.
• El resultado de la consulta se almacenará en el objeto de cursor llamado motor. Utilice el método fetchall() para obtener todas las filas de la consulta resultante.
• Ahora, itere a través de cada fila para ver el resultado de la consulta como se muestra. Para capturar cualquier error al seleccionar los registros en una base de datos y cerrar la conexión sin problemas después de realizar todos los cambios, use probar, esperar y finalmente bloquear como se muestra en el código a continuación.

import psycopg2
 
try:
    connection = psycopg2.connect(user="username",
                                  password="password",
                                  host="hostname",
                                  port="5432",
                                  database="databasename")
    cursor = connection.cursor()
    postgreSQL_select_Query = "select * from publisher"
 
    cursor.execute(postgreSQL_select_Query)
    print("Selecting rows from publisher table using cursor.fetchall")
    publisher_records = cursor.fetchall()
 
    print("Print each row and it's columns values")
    for row in publisher_records:
        print("publisher_Id = ", row[0], )
        print("publisher_name = ", row[1])
        print("publisher_estd  = ", row[2])
        print("publisher_location  = ", row[3])
        print("publisher_type  = ", row[4], "\n")
except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)
 
finally:
    # closing database connection.
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Producción:

Lectura de registros en una tabla en PostgreSQL usando Python

Realización de la operación de actualización en la base de datos PostgreSQL usando Python

La sintaxis para actualizar una tabla es similar a la sintaxis utilizada en sentencias SQL convencionales. Aquí escribimos una consulta de actualización que actualiza el nombre del editor por ID del editor como se muestra.

• Establezca una conexión con la base de datos PostgreSQL como se mencionó anteriormente.
• Ahora, instancia un nuevo objeto cursor(). Un cursor es un puntero de solo lectura que permite que un programa acceda al conjunto de resultados de una consulta.
• Luego, pase la consulta para actualizar una tabla a la función execute() de psycopg2.
• Utilice la función de recuento de filas para contar el número de filas actualizadas.
• Finalmente, llame al método commit(), indicando el objeto conector que se creó para establecer una conexión con la base de datos para confirmar todos los cambios en la base de datos. Esto asegurará que las tablas estén actualizadas.
• Para capturar cualquier error al actualizar las tablas en una base de datos y cerrar la conexión sin problemas después de realizar todos los cambios, use probar, esperar y finalmente bloquear como se muestra en el código a continuación.

Pruebe la función update_publisher() llamando a la función y comprobando las actualizaciones.

import psycopg2
 
 
def updateTable(publisherId, establishedYear):
    try:
        connection = psycopg2.connect(user="username",
                                      password="password",
                                      host="hostname",
                                      port="5432",
                                      database="databasename")
 
        cursor = connection.cursor()
        # Update single record now
        sql_update_query = """Update publisher set \
        publisher_estd = %s where publisher_id = %s"""
        cursor.execute(sql_update_query,
                       (establishedYear,
                        publisherId))
        connection.commit()
        count = cursor.rowcount
        print(count, "Record Updated successfully ")
 
    except (Exception, psycopg2.Error) as error:
        print("Error in update operation", error)
 
    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
 
 
# call the update function
publisherId = 3
establishedYear = 2000
updateTable(publisherId, establishedYear)

Producción:

1 Record Updated successfully 
PostgreSQL connection is closed

Aquí, la fila correspondiente a id = 3 se actualiza con un nuevo valor para publsher_estd.

Actualización de registros en una tabla en PostgreSQL usando Python

Realización de la operación Eliminar en la base de datos PostgreSQL usando Python

La sintaxis para eliminar una tabla es similar a la sintaxis utilizada en las sentencias SQL convencionales. Aquí escribiremos una consulta de eliminación que elimina el registro por ID de editor como se muestra.

• Establezca una conexión con la base de datos PostgreSQL como se mencionó anteriormente.
• Ahora, instancia un nuevo objeto cursor(). Un cursor es un puntero de solo lectura que permite que un programa acceda al conjunto de resultados de una consulta.
• Luego, pase la consulta para eliminar un registro en la tabla a la función execute() de psycopg2.
• Utilice la función de recuento de filas para contar el número de filas eliminadas.
• Finalmente, llame al método commit(), indicando el objeto conector que se creó para establecer una conexión con la base de datos para confirmar todos los cambios en la base de datos. Esto asegurará que los registros se eliminen.
• Para capturar cualquier error al eliminar registros 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 delete_publisher() llamando a la función y verifique las actualizaciones.

import psycopg2
 
def deleteData(publisherId):
    try:
        connection = psycopg2.connect(user="username",
                                      password="password",
                                      host="hostname",
                                      port="5432",
                                      database="databasename")
        cursor = connection.cursor()
 
        # Update single record now
        sql_delete_query = """Delete from publisher\
        where publisher_id = %s"""
        cursor.execute(sql_delete_query, (publisherId,))
        connection.commit()
        count = cursor.rowcount
        print(count, "Record deleted successfully ")
 
    except (Exception, psycopg2.Error) as error:
        print("Error in Delete operation", error)
 
    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
 
 
publisherId = 4
deleteData(publisherId)

Producción:

1 Record deleted successfully 
PostgreSQL connection is closed

Aquí, la fila con id = 4 ha sido eliminada

Eliminar registros en una tabla en PostgreSQL usando Python