psycopg2 - Python & PostgreSQL

En este ejemplo vamos a aprender a usar una conexion de PostgreSQL en Python a traves de la biblioteca psycopg2, la cual es un modulo que nos permite ademas hacer consultas desde codigo con la sintaxis SQL.

Requerimientos

Para este ejemplo como es una introduccion a una biblioteca vamos a usar dos servicios que nos ayudaran a practicar sin tener que instalar nada. estos seran Replit y Heroku Postgres

Cuenta de Replit
Cuenta de Heroku

Creacion de un entorno virtual

crea un entorno virtual:

python -m venv venv

otra opcion tambien es:

virtualenv venv

activate el entorno virtual:

source ./venv/bin/activate

Instalación de psycopg2

pip install psycopg2

linux

Si estas en Linux (Ubuntu), es probable que necesites instalar esta dependencia del sistema, para que puedes isntalar psycopg2.

sudo apt-get install libpq-dev

https://stackoverflow.com/questions/11618898/pg-config-executable-not-found

Conexion a PostgreSQL

import psycopg2

host = "localhost"
database = "demodb"
username = "postgres"
password = "mysecretpassword"
port = 5432

conn = None
cur = None

try:
    conn = psycopg2.connect(
        host=host,
        database=database,
        user=username,
        password=password,
        port=port
    )

    # cur = conn.cursor()
    # cur.close()

except Exception as error:
    print(error)
finally:
    if cur is not None:
        cur.close()
    if conn is not None:
        conn.close()

Creacion de Base de datos

CREATE DATABASE demodb;

create TABLE

create_table = """
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
    )
"""

cur.execute(create_table)
conn.commit()

Insertar un dato

    cur = conn.cursor()
    cur.execute(
        "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)",
        ("Ryan Ray", "[email protected]", "ryan123"))
    conn.commit()

O puedes usar variables

    insert_query = "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)"
    new_user = ("John Doe", "[email protected]", "password")

    cur.execute(insert_query, new_user)

Returning

Ahora si por alguna razon queremos que la consulta nos devuelva el dato que acaba de guardar en la table podemos usar la palabra RETURNING de postgreSQL.

Añade el returning y el fetchone para obtener el nuevo dato:

     cur = conn.cursor()
    cur.execute("INSERT INTO users (name, email, password) VALUES (%s, %s, %s) RETURNING *", ("Joe", "[email protected]", "joe1234"))
    new_user = cur.fetchone()
    print(new_user)

Insertar multiples datos

    input_multiple = [
        ("Ryan ray", "[email protected]", "somepassword"),
        ("Jane Doe", "[email protected]", "password"),
        ("John Smith", "[email protected]", "password")
    ]
    insert_users = "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)"
    for input in input_multiple:
      cur.execute(insert_users, input)

    conn.commit()

executeMany()

insertar multiples datos a traves de un executemany:

    input_multiple = [
        ("Ryan ray", "[email protected]", "somepassword"),
        ("Jane Doe", "[email protected]", "password"),
        ("John Smith", "[email protected]", "password")
    ]
    insert_users = "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)"
    cur.executemany(insert_users, input_multiple)

select * from users

    cur.execute("SELECT * FROM users")
    print(cur.fetchall())

    conn.commit()

Al ser una tupla la repuesta, puedes recorrerla con un bucle for:

    cur.execute("SELECT * FROM users")
    for row in cur.fetchall():
        print(row)

o tambien puedes acceder a los indicies de la tupla:

    cur.execute("SELECT * FROM users")
    for row in cur.fetchall():
        print(row[0], row[1])

Dictionary

por defecto los datos que obtenemos como respuesta a las consutlas son Tuples, pero si queremos obtener tambien los nombres de las columnas y los datos en un formato Dictionary podemos hacer esto:

import psycopg2.extras

    conn = psycopg2.connect(
        ...
    )
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    for row in cur.fetchall():
        print(row["name"], row["password"])

En caso quieras obtener un verdadero diccionario con pares Clave/Valor

Real Dict cursor, https://varun-verma.medium.com/use-psycopg2-to-return-dictionary-like-values-key-value-pairs-4d3047d8de1b

Actualizar Datos

    update_user = "UPDATE users SET password = CONCAT(password, %s) WHERE id = %s"
    cur.execute(update_user, ("secretpassword", 3))

    cur.execute('SELECT * FROM users')
    for row in cur.fetchall():
        print(row)
  
    conn.commit()

delete

    delete_user = "DELETE FROM users WHERE id = %s"
    delete_input = 2
    cur.execute(delete_user, (delete_input,))

    cur.execute('SELECT * FROM users')
    for row in cur.fetchall():
        print(row)

drop table

    cur.execute("DROP TABLE IF EXISTS users")

    conn.commit()

with clause

con la palabra with, no tenemos que cerrar la conexion del cursor manualmente. ademas se ha quitado la variable cur, debido a with. Tampoco es necesario manejar si cur es null ya que no es manejado por nosotros.

...
conn = None

try:
    with psycopg2.connect(
        host=host,
        database=database,
        user=username,
        password=password,
        port=port
    ) as conn:
        with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
            create_table = """
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL,
                password VARCHAR(255) NOT NULL
            )
            """

            cur.execute(create_table)
            ...
except Exception as error:
    print(error)
finally:
    if conn is not None:
        conn.close()