Última actualización: 15 de diciembre de 2023

2.6. PostgreSQL

Nota

Propósito: Es un Adaptador Python de base de datos PostgreSQL.

psycopg, es el adaptador de base de datos PostgreSQL más popular para el lenguaje de programación Python. Sus principales características son la implementación completa de la especificación Python DB-API 2.0 y la seguridad de subprocesos (varios subprocesos pueden compartir la misma conexión).

Fue diseñado para aplicaciones con múltiples subprocesos que crean y destruyen muchos cursores y hacen una gran cantidad de «INSERT» o «UPDATE» simultáneos.

psycopg se implementa principalmente en C como un envoltorio de libpq, lo que resulta en que sea eficiente y seguro. Cuenta con cursores del lado del cliente y del lado del servidor, comunicación asíncrona y notificaciones, compatibilidad con sentencias COPY. Muchos tipos de Python son compatibles de forma inmediata y están adaptados para coincidir con los tipos de datos de PostgreSQL; la adaptación se puede ampliar y personalizar gracias a un sistema flexible de adaptación de objetos.

Nota

psycopg es compatible tanto con Unicode como con Python 3.

2.6.1. Instalación

Para conectarte al servidor PostgreSQL necesita el paquete psycopg2. Esto significa que debe instalar psycopg2 ejecutando los siguientes comandos, el cual a continuación se presentan el correspondiente comando de tu sistema operativo:

$ sudo apt install build-essential libpq-dev python3-dev
$ pip install psycopg2

Puede probar si la instalación se realizo correctamente, ejecutando el siguiente comando correspondiente a tu sistema operativo:

$ python -c "import psycopg2 ; print(psycopg2.__version__)"

Si muestra el numero de la versión instalada de psycopg2, tiene correctamente instalada la paquete. Con esto, ya tiene todo listo para continuar.

2.6.2. Cadenas de conexión

Para definir el método connect debe definir las cadenas de conexión con PostgreSQL como se describe a continuación:

USER

Usuario de conexión a la base de datos.

PASSW

Contraseña del usuario de conexión a la base de datos.

HOST

IP o dirección DNS de conexión al servidor de la base de datos.

PORT

Puerto de conexión al servidor de la base de datos, por defecto es 5492.

DB

Nombre de la base de datos a cual conectar.

A continuación presento un ejemplo en Python implementando una cadena de conexión para una base de datos PostgreSQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
import psycopg2

USER = "root"
PASSW = "root"
HOST = "localhost"
PORT = 5492
DB = "sistema"

conexion_bd = psycopg2.connect(
    user=USER, password=PASSW, host=HOST, port=PORT, database=DB
)

El ejemplo anterior se describe a continuación:

  • En la linea 1, se importa la librería psycopg2.

  • En la linea 3, se define en la constante USER, del usuario de conexión a la base de datos.

  • En la linea 4, se define en la constante PASSW, de la contraseña del usuario de conexión a la base de datos.

  • En la linea 5, se define en la constante HOST, la IP o dirección DNS de conexión al servidor de la base de datos.

  • En la linea 6, se define en la constante PORT, el puerto de conexión al servidor de la base de datos.

  • En la linea 7, se define en la constante DB, el nombre de la base de datos a cual conectar.

  • En la linea 8, se define en el método connect, el cual establece la conexión a la base de datos.

De esta forma se crea una cadena de conexión para PostgreSQL para ser usada por el método connect.

2.6.3. Insertar registros

Si requiere insertar registro en una tabla, a continuación tiene un ejemplo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
"""Programa para la inserción de registro(s) de la tabla"""

import logging
import psycopg2
import os

logging.basicConfig(level=logging.INFO)

# Creando una lista de filas a ingresar
MULTIPLE_COLUMNS = [
    (1, "Leonardo", "Caballero", "5001", "+58-412-4734567"),
    (2, "Ana", "Poleo", "6302", "+58-426-5831297"),
    (3, "Pedro", "Lopez", "4001", "+58-414-2360943"),
]

CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS clientes (
    id int unique not null,
    nombre varchar(25) not null,
    apellido varchar(25) not null,
    codigo_postal int not null,
    telefono varchar(11) not null,
    primary key(id)
);"""
INSERT_SQL = """INSERT INTO clientes VALUES (?, ?, ?, ?, ?);"""


def insertar_registro():
    """Función para la inserción de registro de la tabla"""

    try:
        credenciales = {
            host:"localhost",
            database:"mercantil",
            user:"postgres",
            password:"postgres"
        }
        # Establecer la conexión con la base de datos
        conexion = psycopg2.connect(**credenciales)

        # Crear un objeto cursor para ejecutar las consultas
        cursor = conexion.cursor()
        logging.info(f"¡Conectado a la base de datos {credenciales['database']}!\n")

        # Crear la tabla productos si no existe
        cursor.execute(CREATE_TABLE_SQL)

        # Insertar nuevos registros en la tabla
        cursor.executemany(INSERT_SQL, MULTIPLE_COLUMNS)
        logging.info(
            f"¡Fueron insertado(s) {cursor.rowcount} registro(s) correctamente en la tabla!\n"
        )

        # Insertar un nuevo registro en la tabla
        cursor.execute(INSERT_SQL, (4, "Liliana", "Andradez", "4001", "+58-414-6782473"))
        logging.info(
            f"¡Fueron insertado(s) {cursor.rowcount} registro(s) correctamente en la tabla!\n"
        )
        cursor.close()

    except psycopg2.errors.Error as error:
        print("¡Fallo la inserción de registro(s) en la tabla!", error)
    finally:
        if conexion:
            conexion.close()
            logging.info(
                f"¡La conexión PostgreSQL a la base de datos {credenciales['database']} fue cerrada!\n"
            )


if __name__ == "__main__":
    insertar_registro()

Importante

Usted puede descargar el código usado en esta sección haciendo clic en el siguiente enlace: postgresql_record_insert.py.

Truco

Para ejecutar el código postgresql_record_insert.py abra una consola de comando, acceda al directorio donde se encuentra el programa:

leccion2/
└── postgresql/
    └── crud/
        └── postgresql_record_insert.py

Si tiene la estructura de archivo previa, entonces ejecute el siguiente comando:

$ python postgresql_record_insert.py

El anterior código al ejecutar debe mostrar el siguiente mensaje:

INFO:root:¡Conectado a la base de datos sistema.db!

INFO:root:¡Fueron insertado(s) 3 registro(s) correctamente en la tabla!

INFO:root:¡La conexión PostgreSQL a la base de datos sistema.db fue cerrada!

2.6.4. Consultar registros

Si requiere consultar registros de tabla, a continuación tiene un ejemplo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
"""Programa para la consulta de registro(s) de la tabla"""

import logging
import psycopg2
import os

logging.basicConfig(level=logging.INFO)

SQL_SCRIPTS = """SELECT * FROM clientes;"""


def consultar_registro():
    """Función para la consulta de registro(s) de la tabla"""

    try:
        credenciales = {
            host:"localhost",
            database:"mercantil",
            user:"postgres",
            password:"postgres"
        }
        # Establecer la conexión con la base de datos
        conexion = psycopg2.connect(**credenciales)

        # Crear un objeto cursor para ejecutar las consultas
        cursor = conexion.cursor()
        logging.info(f"¡Conectado a la base de datos {credenciales['database']}!\n")

        # Realizar consulta la tabla clientes
        cursor.execute(SQL_SCRIPTS)
        registros = cursor.fetchall()

        print(f"Total de filas son: {len(registros)} \n")
        print("Mostrar cada fila: \n")
        for fila in registros:
            print(f"\tId: {fila[0]}")
            print(f"\tNombre: {fila[1]} {fila[2]}")
            print(f"\tCódigo postal: {fila[3]}")
            print(f"\tTeléfono: {fila[5]}\n")

        cursor.close()

    except psycopg2.errors.Error as error:
        print("¡Fallo la consulta de registro(s) en la tabla!", error)
    finally:
        if conexion:
            # Cerrar la conexión a la base de datos
            conexion.close()
            logging.info(
                f"¡La conexión PostgreSQL a la base de datos {credenciales['database']} fue cerrada!\n"
            )


if __name__ == "__main__":
    consultar_registro()

Importante

Usted puede descargar el código usado en esta sección haciendo clic en el siguiente enlace: postgresql_record_select.py.

Truco

Para ejecutar el código postgresql_record_select.py abra una consola de comando, acceda al directorio donde se encuentra el programa:

leccion2/
└── postgresql/
    └── crud/
        └── postgresql_record_select.py

Si tiene la estructura de archivo previa, entonces ejecute el siguiente comando:

$ python postgresql_record_select.py

El anterior código al ejecutar debe mostrar el siguiente mensaje:

INFO:root:¡Conectado a la base de datos sistema.db!

Total de filas son: 3

Mostrar cada fila:

        Id: 1
        Nombre: Leonardo
        Código postal: Caballero
        Teléfono: 5001

        Id: 2
        Nombre: Ana
        Código postal: Poleo
        Teléfono: 6302

        Id: 3
        Nombre: Pedro
        Código postal: Lopez
        Teléfono: 4001

INFO:root:¡La conexión PostgreSQL a la base de datos sistema.db fue cerrada!

2.6.5. Actualizar registros

Si requiere actualizar registro de tabla, a continuación tiene un ejemplo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
"""Programa para la actualización de registro de la tabla"""

import logging
import psycopg2
import os

logging.basicConfig(level=logging.INFO)

# Creando una lista de filas a actualizar
MULTIPLE_COLUMNS = [
    ("5051", 1),
    ("6303", 2),
]

SQL_SCRIPTS = """UPDATE clientes SET codigo_postal = ? WHERE id = ?;"""


def actualizar_registro():
    """Función para la actualización de registro de la tabla"""

    try:
        credenciales = {
            host:"localhost",
            database:"mercantil",
            user:"postgres",
            password:"postgres"
        }
        # Establecer la conexión con la base de datos
        conexion = psycopg2.connect(**credenciales)

        # Crear un objeto cursor para ejecutar las actualizaciones
        cursor = conexion.cursor()
        logging.info(f"¡Conectado a la base de datos {credenciales['database']}!\n")

        cursor.executemany(SQL_SCRIPTS, MULTIPLE_COLUMNS)
        # Guardar los cambios en la base de datos
        conexion.commit()
        logging.info(
            "¡Fueron actualizado(s) {} registro(s) correctamente en la tabla!\n".format(
                cursor.rowcount
            )
        )
        cursor.close()

    except psycopg2.errors.Error as error:
        print("¡Fallo la actualización de registro(s) en la tabla!", error)
    finally:
        if conexion:
            # Cerrar la conexión a la base de datos
            conexion.close()
            logging.info(
                f"¡La conexión PostgreSQL a la base de datos {credenciales['database']} fue cerrada!\n"
            )


if __name__ == "__main__":
    actualizar_registro()

Importante

Usted puede descargar el código usado en esta sección haciendo clic en el siguiente enlace: postgresql_record_update.py.

Truco

Para ejecutar el código postgresql_record_update.py abra una consola de comando, acceda al directorio donde se encuentra el programa:

leccion2/
└── postgresql/
    └── crud/
        └── postgresql_record_update.py

Si tiene la estructura de archivo previa, entonces ejecute el siguiente comando:

$ python postgresql_record_update.py

El anterior código al ejecutar debe mostrar el siguiente mensaje:

INFO:root:¡Conectado a la base de datos sistema.db!

INFO:root:¡Fueron actualizado(s) 2 registro(s) correctamente en la tabla!

INFO:root:¡La conexión PostgreSQL a la base de datos sistema.db fue cerrada!

2.6.6. Eliminar registros

Si requiere eliminar registro de tabla, a continuación tiene un ejemplo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
"""Programa para la eliminación de registro de la tabla"""

import logging
import psycopg2
import os

logging.basicConfig(level=logging.INFO)

DB_PATH = os.path.dirname(os.path.abspath(__file__)) + os.sep
DB_FILE = "sistema.db"
DB = DB_PATH + DB_FILE
SQL_SCRIPTS = """DELETE FROM clientes WHERE id = 3;"""


def eliminar_registro():
    """Función para la eliminación de registro de la tabla"""

    try:
        credenciales = {
            host:"localhost",
            database:"mercantil",
            user:"postgres",
            password:"postgres"
        }
        # Establecer la conexión con la base de datos
        conexion = psycopg2.connect(**credenciales)

        # Crear un objeto cursor para ejecutar las actualizaciones
        cursor = conexion.cursor()
        logging.info(f"¡Conectado a la base de datos {credenciales['database']}!\n")

        # Eliminar un fila de registro simple
        cursor.execute(SQL_SCRIPTS)

        # Guardar los cambios en la base de datos
        conexion.commit()
        logging.info("¡Registro eliminado correctamente!\n")
        cursor.close()

    except psycopg2.Error as error:
        print("¡Fallo la eliminación de registro(s) en la tabla!", error)
    finally:
        if conexion:
            # Cerrar la conexión a la base de datos
            conexion.close()
            logging.info(
                f"¡La conexión PostgreSQL a la base de datos {credenciales['database']} fue cerrada!\n"
            )


if __name__ == "__main__":
    eliminar_registro()

Importante

Usted puede descargar el código usado en esta sección haciendo clic en el siguiente enlace: postgresql_record_delete.py.

Truco

Para ejecutar el código postgresql_record_delete.py abra una consola de comando, acceda al directorio donde se encuentra el programa:

leccion2/
└── postgresql/
    └── crud/
        └── postgresql_record_delete.py

Si tiene la estructura de archivo previa, entonces ejecute el siguiente comando:

$ python postgresql_record_delete.py

El anterior código al ejecutar debe mostrar el siguiente mensaje:

INFO:root:¡Conectado a la base de datos sistema.db!

INFO:root:¡Registro eliminado correctamente!

INFO:root:¡La conexión PostgreSQL a la base de datos sistema.db fue cerrada!

Asi de esta forma puede ingresar, consultar, actualizar y eliminar registro en una tabla en una base de datos PostgreSQL.

Ver también

Consulte la sección de lecturas suplementarias del entrenamiento para ampliar su conocimiento en esta temática.