En este post vamos a ver cómo conectar Python con el motor de base de datos PostgreSQL usando el módulo psycopg2 que cumple con la DB API 2.0.

La DB API 2.0 es una API común en la que se utilizan los mismos métodos, cambiando únicamente el driver; algo así como PDO en PHP.

Vamos a hacer un CRUD con PostgreSQL usando Python, evitando inyecciones SQL.

Nota: todo el código está en mi GitHub.

Requisitos y recomendaciones

Recuerda instalar Python y PIP en Linux o Windows, y también PostgreSQL en Windows o Linux.

Recomiendo crear un usuario y base de datos para este ejercicio, o tener credenciales a la mano.

Una vez que tengas PIP, ejecuta lo siguiente para instalar Psycopg2:

pip install psycopg2

Dentro del código vamos a encontrar el archivo credenciales_ejemplo.json, el cual debemos copiar y renombrar a credenciales.json y dentro del mismo debemos poner los datos de acceso.

De esta manera puedes compartir tu código sin necesidad de pasar tus contraseñas y usuarios.

Base de datos y tabla

La base de datos se llama mascotas, y la tabla igualmente mascotas.

El esquema es el siguiente:

CREATE TABLE mascotas(
	id serial primary key,
	nombre varchar(50) NOT NULL,
	edad smallint NOT NULL
);

insert into mascotas
(nombre, edad)
values
('Maggie', 3),
('Guayaba', 2),
('Capuchina', 2),
('Snowball', 1),
('Panqué', 1);

Vamos a trabajar con un tipo de dato autoincrementable para el id, además de guardar el nombre y la edad.

Conexión

Vamos a poner lo relacionado a la conexión en un archivo llamado bd.py que tendrá la variable conexion, y después vamos a importar a la misma desde otros archivos.

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""

import psycopg2
import json
# Leer las credenciales de un archivo JSON
# Recomendado: https://parzibyte.me/blog/2019/06/09/json-python-codificar-decodificar/
with open("credenciales.json") as archivo_credenciales:
    credenciales = json.load(archivo_credenciales)
# Como la conexión devuelve un diccionario podemos convertirlo fácilmente
# a "kwargs" o key arguments ;)
# Recomendado: https://parzibyte.me/blog/2018/12/20/args-kwargs-python/
try:
    conexion = psycopg2.connect(**credenciales)
except psycopg2.Error as e:
    print("Ocurrió un error al conectar a PostgreSQL: ", e)

No te confundas con las credenciales. El mismo código podría quedar así:

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""

import psycopg2
# Recomendado: https://parzibyte.me/blog/2018/12/20/args-kwargs-python/
try:
    credenciales = {
        "dbname": "mascotas",
        "user": "parzibyte",
        "password": "hunter2",
        "host": "localhost",
        "port": 5432
    }
    conexion = psycopg2.connect(**credenciales)
except psycopg2.Error as e:
    print("Ocurrió un error al conectar a PostgreSQL: ", e)

Solo que con la primera forma ponemos las credenciales en un archivo separado, no en el código.

Prueba ejecutando el archivo, no debería generar ningún error. Si es así, podemos continuar.

Insertar (insert)

Comenzamos importando a conexion del archivo bd.

Para insertar tenemos que obtener un cursor y cerrarlo al terminar.

Cuando tenemos un cursor, podemos llamar al método execute con dos argumentos: la consulta como cadena, y una tupla con los valores que remplazarán a los %s.

Si te fijas, estamos evitando inyecciones SQL al usar placeholders en lugar de concatenar. Y aunque se parece mucho al formato para printf del viejo lenguaje C, siempre debes poner %s sin importar el tipo de dato.

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion
try:
    with conexion.cursor() as cursor:
        consulta = "INSERT INTO mascotas(nombre, edad) VALUES (%s, %s);"
        # Podemos llamar muchas veces a .execute con datos distintos
        cursor.execute(consulta, ("Maggie", 3))
        cursor.execute(consulta, ("Capuchina", 2))
        cursor.execute(consulta, ("Guayaba", 2))
        cursor.execute(consulta, ("Panqué", 1))
        cursor.execute(consulta, ("Snowball", 1))
    conexion.commit()  # Si no haces commit, los cambios no se guardan

except psycopg2.Error as e:
    print("Ocurrió un error al insertar: ", e)
finally:
    conexion.close()

Precaución: cuando solo mandamos un dato, debemos poner una coma al final. Es decir, si solo insertamos el nombre, la tupla sería:

(nombre,)

La coma al final es muy importante, pues así Python sabe que es una tupla. Si lo quitamos:

(nombre)

Lo tomará como que estamos evaluando entre paréntesis el valor, y mandará el valor solo, no en una tupla, lo que generará errores. Es decir, (nombre) se convierte en simplemente nombre.

Otro aspecto importante es usar with, ya que de esta manera el cursor se cierra automáticamente al terminar.

Finalmente, para que los cambios se guarden, debemos llamar a conexion.commit() siempre que cambiemos los datos.

Listar (select)

Para consultar a todos los valores y tenerlos dentro de un arreglo obtenemos igualmente un cursor, invocamos a execute con la consulta y después invocamos a cursor.fetchall()

Este método no es el más óptimo pero trae los datos en un arreglo, cosa sencilla:

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion

try:
    with conexion.cursor() as cursor:
        # En este caso no necesitamos limpiar ningún dato
        cursor.execute("SELECT id, nombre, edad FROM mascotas;")

        # Con fetchall traemos todas las filas
        mascotas = cursor.fetchall()

        # Recorrer e imprimir
        for mascota in mascotas:
            print(mascota)
except psycopg2.Error as e:
    print("Ocurrió un error al consultar: ", e)
finally:
    conexion.close()

En cada iteración tenemos a la fila en forma de lista, y podemos acceder a sus elementos a través del índice, por ejemplo, mascota[0].

Consultar con un cursor

El método de iterar con un cursor es más óptimo al recorrer grandes cantidades de datos, pues no guardamos las filas en un arreglo, sino que traemos una por una a la vez.

El siguiente código ejemplifica cómo consultar datos usando un cursor:

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion

try:
    with conexion.cursor() as cursor:
        # En este caso no necesitamos limpiar ningún dato
        cursor.execute("SELECT id, nombre, edad FROM mascotas;")
        # Hacer un while, mientras fetchone no regrese None
        mascota = cursor.fetchone()
        while mascota:
            print(mascota)
            mascota = cursor.fetchone()
except psycopg2.Error as e:
    print("Ocurrió un error al consultar: ", e)
finally:
    conexion.close()

Ahora no tenemos los datos en un arreglo, sino que traemos de a uno por uno con fetchone. El ciclo se cumplirá hasta que fetchone regrese algo que no se evalúe como True, por ejemplo, None.

Consultar usando where

Las consultas también pueden tener datos que debemos escapar para evitar inyecciones SQL. El siguiente ejemplo hace una consulta usando una condición:

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "SELECT id, nombre, edad FROM mascotas WHERE edad > %s;"
        cursor.execute(consulta, (1,))

        # Con fetchall traemos todas las filas
        mascotas = cursor.fetchall()

        # Recorrer e imprimir
        for mascota in mascotas:
            print(mascota)
except psycopg2.Error as e:
    print("Ocurrió un error al consultar con where: ", e)
finally:
    conexion.close()

Está de más recordar que esas variables pueden venir de cualquier otro lugar, aquí las estoy poniendo directamente en el código para hacer las cosas más sencillas.

Consultar usando like

El método de búsqueda por coincidencia en donde se usa like es un poco complejo cuando hablamos de inyecciones SQL, ya que a veces se necesita concatenar con los signos de porcentaje o comodines.

Para hacer este tipo de búsqueda hay que poner un simple placeholder, y concatenar la búsqueda con los signos de porcentaje:

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion
try:
    with conexion.cursor() as cursor:
        consulta = "SELECT id, nombre, edad FROM mascotas WHERE nombre like %s"
        # Para Maggie
        busqueda = "agg"
        cursor.execute(consulta, ("%" + busqueda + "%",))

        # Con fetchall traemos todas las filas
        mascotas = cursor.fetchall()

        # Recorrer e imprimir
        for mascota in mascotas:
            print(mascota)
except psycopg2.Error as e:
    print("Ocurrió un error: ", e)
finally:
    conexion.close()

Editar (update)

Para actualizar un dato usamos de nuevo un cursor con el método execute e invocamos al método commit de la conexión.

Los argumentos se pasan como placeholders y se envían como tupla, justo como se hace al insertar.

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "UPDATE mascotas SET edad = %s WHERE id = %s;"
        nueva_edad = 5
        id_editar = 17
        cursor.execute(consulta, (nueva_edad, id_editar))

    # No olvidemos hacer commit cuando hacemos un cambio a la BD
    conexion.commit()
except psycopg2.Error as e:
    print("Ocurrió un error al editar: ", e)
finally:
    conexion.close()

Eliminar (delete)

El código para eliminar sigue las mismas reglas que el de actualizar e insertar.

Recuerda que se pueden pasar cualquier número de argumentos, siempre y cuando los indiques con placeholders. También podrías no pasar argumentos.

Finalmente, no olvides hacer un commit.

"""
    Conexión a PostgreSQL con Python
    Ejemplo de CRUD evitando inyecciones SQL
    
    @author parzibyte

    Más tutoriales en:
                        parzibyte.me/blog
"""
import psycopg2
from bd import conexion
try:
    with conexion.cursor() as cursor:

        consulta = "DELETE FROM mascotas WHERE edad < %s;"
        # También podría ser sin where
        #consulta = "DELETE FROM mascotas"
        edad = 2
        cursor.execute(consulta, (edad,))

    # No olvidemos hacer commit cuando hacemos un cambio a la BD
    conexion.commit()
except psycopg2.Error as e:
    print("Error eliminando: ", e)
finally:
    conexion.close()

Las excepciones

En todo el código estoy atrapando cualquier excepción de psycopg2.Error, es importante manejarlas ya que así evitas mostrar errores o generar más de los mismos.

No intentes cachar una excepción global con Exception, pues cualquier error del código (aunque no sea de la base de datos) se tomará como error de la base de datos, aunque no tenga nada que ver con la misma, generando confusión.

Conclusión

Hemos visto cómo conectar Python con PostgreSQL a través del módulo psycopg2.

Sé que son ejemplos sencillos, pero lo hago de esta forma para no complicar el código con cosas que no tienen que ver con el objetivo: mostrar cómo hacer un CRUD con Python y PSQL.

Obviamente los datos pueden venir de otro lugar, se pueden separar los componentes, etcétera; todo lo puedes acomodar a tu modo.

Te invito a leer más sobre Python o PostgreSQL.