SQL en Python

Acceso a bases de datos relacionales desde Python: drivers, ORM y motores analíticos

python
sql
databases
orm
analytics
Referencia comentada del stack Python para SQL: drivers nativos, SQLAlchemy, Ibis, DuckDB y Polars. Cuándo conviene cada capa, patrones idiomáticos y trampas habituales en producción.

Sobre SQL en Python

El acceso a bases de datos relacionales desde Python no es un único problema sino un espectro de capas que conviene tener interiorizado antes de elegir una herramienta. De más bajo a más alto nivel:

  • Drivers nativos (psycopg, asyncpg, pyodbc, mysqlclient, sqlite3). Hablan el protocolo binario de la base. Devuelven tuplas o filas planas. Máximo control, mínimo azúcar.
  • SQLAlchemy: el estándar de facto en Python. Aporta connection pooling, abstracción de dialecto, transacciones explícitas y dos niveles bien diferenciados (Core como constructor de expresiones SQL. ORM como mapeo a clases Python).
  • Motores analíticos en proceso (duckdb, polars.sql) y abstracciones portables (ibis). Pensados para análisis sobre datos en memoria o en ficheros (Parquet, CSV, Arrow), no para servir tráfico transaccional.
  • pandas.read_sql: atajo conveniente que apoyado en SQLAlchemy convierte un resultado en DataFrame. Cómodo para análisis ad hoc. Raramente lo que quieres en un servicio.

Dos principios que conviene tener claros:

  • Parametriza siempre. El formateo de cadenas (f"SELECT ... WHERE id = {user_id}") es la fuente número uno de inyecciones SQL. Todas las APIs serias aceptan parámetros (?, %s, :name). Úsalos sin excepción.
  • Cierra lo que abres. Las conexiones y los cursores son recursos del sistema operativo. El patrón canónico es with engine.connect() as conn: (o async with). Fugas de conexión saturan el pool y producen timeouts aparentemente aleatorios en producción.

Esta página cataloga las piezas centrales del stack. El orden refleja la jerarquía conceptual: primero la pieza estructural (SQLAlchemy y read_sql), después los motores analíticos (DuckDB, Polars, Ibis), y finalmente los drivers nativos cuando bajar a esa capa está justificado.

SQLAlchemy

SQLAlchemy es la pieza estructural del acceso a bases relacionales en Python: gestiona el connection pool, abstrae las diferencias entre dialectos (PostgreSQL, MySQL, SQLite, SQL Server, Oracle…), y ofrece dos capas que conviene distinguir desde el primer día:

  • Core: un constructor de expresiones SQL tipado. Escribes consultas componibles en Python que se compilan al dialecto del motor. Es la capa “sin ORM”.
  • ORM: mapeo declarativo de clases Python a tablas, con sesión, identity map y unit of work. Pensado para lógica de dominio compleja.

Desde SQLAlchemy 2.0 (2023) la API se ha unificado en torno a select() y un estilo más estricto que premia la claridad sobre la magia. Cualquier código anterior a 2.0 que veas en internet probablemente esté desactualizado.

Cuándo usarlo

  • Servicios y aplicaciones que persisten estado en una base relacional. Es la opción por defecto razonable.
  • Cuando necesitas portabilidad real entre motores (mismo código contra SQLite en dev y Postgres en prod).
  • Cuando quieres connection pooling gestionado: SQLAlchemy gestiona reciclado, check-out, ping y reconexión sin que tengas que pensarlo.

Cuándo NO usarlo

  • Análisis ad hoc sobre ficheros locales (Parquet, CSV). Ahí DuckDB o Polars son drásticamente más simples.
  • Cargas masivas de extracción analítica. Para mover decenas de millones de filas a un DataFrame, prefiere connectorx o el driver nativo con COPY/fetchmany.
  • Pipelines puramente asíncronos de muy alto throughput sobre Postgres. SQLAlchemy soporta async (AsyncEngine), pero asyncpg directo es más rápido y predecible cuando no necesitas las capas superiores.

Core vs ORM

La decisión no es ideológica sino pragmática:

  • Core brilla cuando tu código piensa en términos de consultas (joins, agregaciones, upserts) y no en términos de objetos con identidad. Es la elección natural en pipelines de datos y reporting.
  • ORM brilla cuando tu dominio tiene entidades con ciclo de vida (usuarios, pedidos, sesiones) y reglas de negocio que viven en clases Python. El precio es entender la unit of work y evitar el problema N+1.

En la práctica, una codebase madura usa ambas: ORM para escrituras de dominio, Core para queries analíticas dentro del mismo proceso.

Conceptos clave

  • Engine vs Connection vs Session. create_engine(url) crea una factoría con un pool, debe ser un único objeto a nivel de aplicación, no por petición. engine.connect() saca una conexión del pool. La Session del ORM envuelve una conexión con semántica transaccional propia.
  • Transacciones. Desde 2.0, engine.begin() es el patrón canónico: confirma al salir del with si no hubo excepción, rollback si la hubo. engine.connect() por sí solo no compromete cambios hasta que llames commit() (modo commit as you go).
  • Parametrización. text("SELECT ... WHERE id = :id") con params={"id": ...} o expresiones tipadas con select(Table).where(Table.c.id == ...). Nunca concatenes valores de usuario en la cadena SQL.
  • Dialecto. La URL (postgresql+psycopg://, mysql+pymysql://, mssql+pyodbc://) determina el driver. SQLAlchemy traduce funciones, tipos y sintaxis específica de cada motor.
  • Async. create_async_engine + AsyncSession. El código de modelo es el mismo. Los puntos de I/O usan await.

Patrón mínimo

from sqlalchemy import create_engine, text

# Un único engine a nivel de aplicación
engine = create_engine(
    "postgresql+psycopg://user:pass@localhost:5432/mydb",
    pool_size=5,
    pool_pre_ping=True,   # detecta conexiones muertas tras idle
)

# Lectura con transacción implícita
with engine.connect() as conn:
    rows = conn.execute(
        text("SELECT id, email FROM users WHERE created_at > :since"),
        {"since": "2026-01-01"},
    ).all()

# Escritura con transacción explícita
with engine.begin() as conn:
    conn.execute(
        text("UPDATE users SET active = :a WHERE id = :id"),
        [{"a": False, "id": 1}, {"a": False, "id": 2}],   # bulk
    )

Con ORM declarativo (2.0):

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
from sqlalchemy import select

class Base(DeclarativeBase): ...

class User(Base):
    __tablename__ = "users"
    id:    Mapped[int]  = mapped_column(primary_key=True)
    email: Mapped[str]
    active: Mapped[bool] = mapped_column(default=True)

with Session(engine) as session, session.begin():
    user = session.scalar(select(User).where(User.email == "a@b.com"))
    user.active = False   # flush automático al commit

Trampas habituales

  • Crear engines por petición. El pool deja de tener sentido y se acumulan conexiones. create_engine se llama una vez al iniciar la aplicación.
  • Olvidar commit() en modo commit as you go. engine.connect() no confirma automáticamente. Si no llamas conn.commit() ni usas engine.begin(), los cambios se pierden al cerrar la conexión. Síntoma típico: “los INSERT funcionan en tests pero no aparecen en la BD”.
  • Concatenación de strings en cláusulas dinámicas. Para nombres de columna o tabla parametrizables, usa bindparam + lista blanca explícita. Nunca interpolación.
  • Sesiones largas en ORM. La Session mantiene el identity map en memoria. Sesiones de larga vida acumulan objetos y producen staleness. Patrón: sesión por unidad de trabajo (petición HTTP, job de cola), no por aplicación.
  • N+1 silencioso. Acceder a relaciones lazy dentro de un bucle dispara una consulta por iteración. selectinload() o joinedload() lo arreglan. Sin perfilado SQL, pasan desapercibidos.
  • pool_pre_ping=False con bases tras balanceador. Conexiones cortadas por el LB producen errores en el primer uso. Activar pool_pre_ping=True añade un SELECT 1 barato pero evita errores espurios.

Enlaces

Relacionados en esta página

  • pandas.read_sql, se apoya en SQLAlchemy para la conexión.
  • ibis, DSL portable que puede ejecutar contra SQLAlchemy como backend.
  • psycopg y asyncpg, drivers Postgres que SQLAlchemy utiliza por debajo.

pandas.read_sql

pandas.read_sql (y sus primas read_sql_query, read_sql_table) es el atajo canónico para materializar el resultado de una consulta SQL como DataFrame. Internamente delega en SQLAlchemy (o, deprecado, en una conexión DB-API directa) y aplica conversión de tipos a NumPy/pandas.

Es la pieza más usada, y más abusada, del catálogo. Cómoda para análisis ad hoc en notebooks, problemática como pieza de un pipeline serio.

Cuándo usarlo

  • Notebooks y análisis exploratorios donde el resultado cabe holgadamente en memoria (decenas o cientos de miles de filas).
  • Reporting interno donde el flujo natural es SQL → DataFrame → gráfica.
  • Cuando ya tienes un Engine de SQLAlchemy a mano y quieres el atajo en vez de iterar cursores.

Cuándo NO usarlo

  • Cargas masivas. Para mover millones de filas, connectorx.read_sql es órdenes de magnitud más rápido (lee directamente a Arrow), y los drivers nativos con COPY (Postgres) o fetchmany por bloques evitan picos de memoria.
  • Producción transaccional. read_sql está pensado para análisis. Si tu lógica de negocio depende del resultado, usa SQLAlchemy directamente, más control sobre transacciones, tipos y errores.
  • Cuando vas a procesar con Polars o DuckDB. Pasar por pandas es overhead innecesario. Ambos leen SQL directamente.

Conceptos clave

  • Acepta con= como Engine, Connection o conexión DB-API. Con SQLAlchemy se obtiene parametrización segura. Con DB-API plano, el contrato es ambiguo y depende del driver.
  • params= parametriza la consulta. Sintaxis depende del driver: :name con SQLAlchemy, %s o ? con DB-API. La regla práctica: usa siempre SQLAlchemy y :name.
  • chunksize= devuelve un iterador de DataFrames. Útil para procesar resultados grandes sin agotar memoria, aunque el tipado por chunk puede variar y dar dolores de cabeza.
  • dtype= (pandas ≥ 2.0) y dtype_backend="pyarrow" permiten respaldar el DataFrame con Arrow, lo que evita la conversión costosa NumPy ↔︎ object para strings y fechas.

Patrón mínimo

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://user:pass@host/db")

# Consulta parametrizada — NUNCA usar f-strings
df = pd.read_sql(
    "SELECT id, email, created_at FROM users WHERE country = :c",
    con=engine,
    params={"c": "ES"},
    parse_dates=["created_at"],
)

# Procesado por chunks para resultados grandes
for chunk in pd.read_sql(
    "SELECT * FROM events WHERE ts > :since",
    con=engine,
    params={"since": "2026-01-01"},
    chunksize=100_000,
):
    process(chunk)

Trampas habituales

  • F-strings en la consulta. El antipatrón clásico, f"... WHERE id = {user_input}" es inyección SQL. Usa params= siempre.
  • Conversión de tipos imprevista. Columnas BIGINT con nulos se convierten a float64. Columnas TIMESTAMP WITH TIME ZONE pueden perder la zona o ganar la del sistema. Verifica df.dtypes después de cargar y considera dtype_backend="pyarrow".
  • chunksize y tipos inconsistentes. Cada chunk infiere tipos por separado. Valores ausentes en chunks tempranos generan dtypes distintos a los de chunks tardíos. Fija dtypes con dtype= cuando uses chunks.
  • Conexión vs Engine. read_sql(..., con=conn) con una Connection abierta no la cierra. Con un Engine, pandas la saca y la devuelve al pool automáticamente. Pasar un Engine es lo seguro por defecto.
  • Memoria. El DataFrame con object para strings consume mucho más que el resultado original. Para más de unos millones de filas, considera polars.read_database o connectorx.

Enlaces

Relacionados en esta página

  • SQLAlchemy, la pieza sobre la que read_sql se apoya.
  • polars y duckdb, alternativas modernas más rápidas para análisis.

DuckDB

DuckDB es una base de datos analítica embebida (in-process), columnar y vectorizada. La analogía habitual es “SQLite para análisis”: no hay servidor, no hay configuración, el motor vive dentro del proceso Python. La diferencia clave con SQLite es la arquitectura columnar y el ejecutor vectorizado, que la hacen órdenes de magnitud más rápida en queries analíticas sobre miles de millones de filas.

Particularmente eficaz consultando directamente ficheros Parquet, CSV o Arrow sin importarlos previamente. Lee y predica selectivamente sobre Parquet (pushdown de filtros), lo que permite trabajar con datasets más grandes que la memoria sin esfuerzo.

Cuándo usarlo

  • Análisis interactivo sobre Parquet, CSV o Arrow locales (o en S3/GCS) sin montar infraestructura.
  • ETL de tamaño medio (gigabytes a algunas decenas de TB con particionado) dentro de un único nodo.
  • Sustituir SQLite en cualquier escenario donde el patrón de uso sea OLAP (agregaciones, joins amplios) y no OLTP.
  • Como motor SQL detrás de una API analítica embebida en una aplicación Python.

Cuándo NO usarlo

  • Cargas transaccionales concurrentes. DuckDB es de proceso único y de escritor único. Para OLTP usa PostgreSQL.
  • Escalado horizontal. Es nodo único por diseño. Si necesitas distribuir, mira ClickHouse, BigQuery, Snowflake o Trino.
  • Persistencia compartida entre procesos en escritura. Múltiples procesos escribiendo al mismo fichero .duckdb no está soportado. Sí lo está la lectura concurrente.

Conceptos clave

  • Modo en memoria vs fichero. duckdb.connect() (sin path) es efímero. duckdb.connect("mi.duckdb") persiste. La sintaxis es idéntica.
  • Integración Arrow / pandas / polars zero-copy. duckdb.sql("SELECT ... FROM my_df") consulta directamente un DataFrame de pandas o polars que esté en el ámbito Python actual, sin copia ni registro explícito.
  • Lectura directa de ficheros. SELECT * FROM 'data/*.parquet', read_csv_auto('data.csv'), read_json_auto(...). El optimizador empuja predicados y proyecciones al lector de Parquet.
  • Tipos. Soporte nativo para DECIMAL, INTERVAL, STRUCT, LIST, MAP, tipos temporales con zona, UUID. Más rico que SQLite, más cercano a Postgres.
  • Httpfs. INSTALL httpfs; LOAD httpfs; permite leer directamente de S3 / HTTPS.

Patrón mínimo

import duckdb
import polars as pl

# Consulta directa contra Parquet — sin importar
res = duckdb.sql("""
    SELECT country, COUNT(*) AS n, AVG(revenue) AS avg_rev
    FROM 'data/sales_*.parquet'
    WHERE year = 2026
    GROUP BY country
    ORDER BY n DESC
""")

# Materializar a polars (zero-copy a Arrow, después a Polars)
df = res.pl()

# Consultar un DataFrame existente como si fuera tabla
users = pl.read_csv("users.csv")
duckdb.sql("SELECT * FROM users WHERE age > 30").show()

# Conexión persistente
con = duckdb.connect("warehouse.duckdb")
con.execute("CREATE TABLE IF NOT EXISTS events AS SELECT * FROM 'events.parquet'")
con.close()

Trampas habituales

  • Esperar semántica OLTP. No hay control de concurrencia multiproceso para escritura. Si dos procesos abren el mismo fichero .duckdb en modo lectura/escritura, fallan o corrompen.
  • SELECT * FROM df con df ambiguo. El nombre Python tiene que estar en el ámbito visible para DuckDB. Si lo creas dentro de una función y consultas fuera, no lo encuentra.
  • Memoria con joins amplios. Aunque es muy eficiente, joins entre tablas grandes sin predicado pueden hinchar memoria. Configura SET memory_limit = '8GB' y SET temp_directory = '...' para que use disco si hace falta.
  • Versiones del fichero. El formato .duckdb evoluciona y entre minor versions a veces requiere EXPORT DATABASE y IMPORT DATABASE. No confíes en compatibilidad eterna del binario sin probarlo.
  • Tipos DECIMAL con from_pandas. Pandas no tiene DECIMAL nativo y convierte a float64. Si necesitas precisión exacta (finanzas), pasa por Arrow o construye desde Polars con pl.Decimal.

Enlaces

Relacionados en esta página

  • polars, comparte filosofía vectorizada. Integración bidireccional zero-copy.
  • ibis, DuckDB es uno de los backends mejor soportados de Ibis.

Polars (SQL)

polars es un DataFrame vectorizado en Rust con API Python idiomática. Su pieza relevante para esta página es pl.SQLContext: un motor SQL ligero que permite consultar LazyFrames y DataFrames con SQL estándar, además de pl.read_database para extraer datos directamente de una base relacional a un DataFrame Polars.

No pretende competir con DuckDB en alcance del dialecto, pero cierra muy bien el ciclo cuando ya estás trabajando en Polars y prefieres expresar una consulta en SQL en vez de en la API de expresiones.

Cuándo usarlo

  • Pipelines que ya viven en Polars y donde puntualmente conviene SQL (joins complejos, lectores SQL existentes).
  • Extracción rápida de una base a DataFrame con pl.read_database, más rápido que pandas.read_sql para volúmenes medios y altos, sobre todo con connectorx como backend.
  • Análisis en streaming sobre ficheros grandes: scan_parquet + SQLContext mantiene el modo lazy y solo materializa al final.

Cuándo NO usarlo

  • Como motor de consulta primario sobre Parquet de gran escala. DuckDB ofrece más SQL, mejor pushdown y mejor planificador analítico.
  • Cuando necesitas todas las funciones de SQL. El dialecto de SQLContext es menos completo que el de DuckDB. Ciertas funciones de ventana o agregaciones avanzadas todavía no están.
  • Para escribir en bases relacionales. polars tiene write_database, pero el caso de uso natural es lectura + transformación, no escritura transaccional.

Conceptos clave

  • pl.SQLContext registra DataFrames y LazyFrames como tablas y permite consultarlos con SQL. Mantiene la pereza: el plan se compila al ejecutor de Polars.
  • pl.read_database(query, connection) carga el resultado a un DataFrame. Acepta una URL ADBC, una conexión SQLAlchemy o, idealmente, una de connectorx (mucho más rápida en lecturas masivas).
  • Modo lazy. Con scan_* y SQLContext(eager=False), la consulta SQL se traduce al plan lazy de Polars y se optimiza junto con el resto del pipeline (pushdown de proyección y predicado).
  • Tipos. Polars usa Arrow internamente, así que el tipado es estricto. Las fechas con zona se preservan. Los Decimal requieren tipo explícito.

Patrón mínimo

import polars as pl

# Consultar un DataFrame existente con SQL
df = pl.DataFrame({
    "user_id":  [1, 2, 3, 4],
    "country":  ["ES", "FR", "ES", "DE"],
    "revenue":  [100, 200, 150, 300],
})

ctx = pl.SQLContext(users=df)
res = ctx.execute("""
    SELECT country, SUM(revenue) AS total
    FROM users
    GROUP BY country
    ORDER BY total DESC
""", eager=True)

# Lectura directa desde una base relacional (vía connectorx)
df_db = pl.read_database_uri(
    query="SELECT id, email FROM users WHERE country = 'ES'",
    uri="postgresql://user:pass@host/db",
    engine="connectorx",
)

# Modo lazy sobre Parquet grande
lf = pl.scan_parquet("events/*.parquet")
ctx = pl.SQLContext(events=lf)
plan = ctx.execute("SELECT user_id, COUNT(*) FROM events GROUP BY user_id")
out  = plan.collect()   # materializa solo aquí

Trampas habituales

  • pl.read_database con SQLAlchemy es lento. El backend por defecto pasa por filas DB-API. Para volumen, usa pl.read_database_uri(..., engine="connectorx") o “adbc”, extracción zero-copy a Arrow.
  • Dialecto incompleto. Funciones específicas (algunas de ventana, ciertos casts) pueden no estar. El mensaje de error a veces es críptico. Verifica contra la matriz de soporte en la documentación.
  • Confundir read_database con read_database_uri. El primero acepta una conexión. El segundo, una URI. Los rendimientos no son equivalentes según el backend.
  • Conversión to_pandas() con tipos extendidos. Si tu DataFrame tiene pl.Decimal o pl.Datetime con zona, to_pandas() puede degradar tipos. Especifica use_pyarrow_extension_array=True.

Enlaces

Relacionados en esta página

  • duckdb, alternativa con SQL más completo. Integración bidireccional zero-copy.
  • pandas.read_sql, equivalente más lento para extracción a DataFrame.

Ibis

Ibis es una DSL Python para construir expresiones de tabla que se compilan a SQL del backend que elijas (DuckDB, BigQuery, Snowflake, Postgres, ClickHouse, Trino, Spark, Polars…). La idea central: escribes una vez en Python con una API tipo dataframe-on-tables, y el mismo código se ejecuta contra cualquier motor compatible. Es la respuesta moderna al problema de portabilidad multi-motor, el espacio que durante años ocupó SQLAlchemy Core con menos azúcar.

Mantenido por Voltron Data, los mismos detrás de Arrow y muchos del equipo original de Ibis en Cloudera. Especialmente útil cuando tus datos viven en varios sitios (lago Parquet + warehouse + Postgres operacional) y quieres una capa común.

Cuándo usarlo

  • Mismo análisis contra múltiples backends. Notebook local en DuckDB, producción en BigQuery o Snowflake.
  • Pipelines complejos con joins y agregaciones donde escribir SQL plano se vuelve frágil. Las expresiones Ibis son componibles y refactorizables.
  • Cuando quieres tipado y autocompletado sobre columnas y resultados, en vez de cadenas SQL.

Cuándo NO usarlo

  • Una sola base y SQL ya bien dominado. La capa de abstracción cuesta. Si solo trabajas contra Postgres y conoces el dialecto, escribir SQL directo es más rápido y más legible para el equipo.
  • Necesitas usar funciones muy específicas del backend sin equivalente en Ibis. Aunque hay escape hatches (@ibis.udf, raw SQL), pelearse contra la abstracción anula su ventaja.
  • DDL pesado. Ibis es fuerte en lectura y consulta. Para migraciones y DDL transaccional, usa Alembic + SQLAlchemy o herramientas dedicadas (Liquibase, dbt si encaja en el flujo).

Conceptos clave

  • Backend explícito. ibis.connect("duckdb://..."), ibis.duckdb.connect(...), ibis.bigquery.connect(...). La misma expresión se evalúa distinto según el backend.
  • Expresiones perezosas. t.filter(...).group_by(...).aggregate(...) no ejecuta nada hasta .execute() o .to_pandas() / .to_polars(). Permite componer y reusar fragmentos.
  • ibis.show_sql(expr) imprime el SQL generado. Imprescindible en debugging, y útil como aprendizaje del dialecto.
  • Tipos. Sistema de tipos propio (int64, string, timestamp("UTC"), array, struct) que se mapea al del backend. Errores de tipo se detectan en construcción, no en ejecución.
  • ibis-framework vs ibis-* backends. Cada backend es una dependencia opcional (pip install 'ibis-framework[duckdb,postgres]').

Patrón mínimo

import ibis

ibis.options.interactive = True   # ejecución eager para exploración

con = ibis.duckdb.connect()   # o ibis.connect("postgres://...")
t = con.read_parquet("sales_*.parquet", "sales")

expr = (
    t.filter(t.year == 2026)
     .group_by("country")
     .aggregate(
         n=t.count(),
         avg_rev=t.revenue.mean(),
     )
     .order_by(ibis.desc("n"))
)

# Inspecciona el SQL generado
print(ibis.to_sql(expr))

# Ejecuta y materializa
df = expr.to_pandas()   # o .to_polars()

Trampas habituales

  • Asumir que todo el dialecto está soportado. Algunas funciones específicas (ciertas funciones de ventana avanzadas, GIS, JSON nested) varían por backend. Verifica con ibis.show_sql antes de confiar.
  • Mezclar UDFs y portabilidad. Una UDF Python registrada en DuckDB no funcionará en BigQuery. Si la portabilidad es objetivo, mantén las UDFs en una capa configurable.
  • Conexiones por defecto efímeras. ibis.duckdb.connect() sin path crea una BD en memoria, distinta cada vez. Para persistir, pasa un path.
  • interactive=True en producción. Está pensado para notebooks. En código de aplicación produce ejecuciones implícitas inesperadas. Déjalo solo en exploración.

Enlaces

Relacionados en esta página

  • SQLAlchemy, alternativa más madura cuando el backend es una BD relacional única.
  • duckdb, uno de los backends preferidos de Ibis. Los dos se usan juntos con naturalidad.

psycopg

psycopg (versión 3, sucesora de psycopg2) es el driver canónico de PostgreSQL para Python. Implementa la DB-API 2.0, soporta el protocolo binario nativo de Postgres y cubre las funcionalidades avanzadas del motor: COPY, LISTEN/NOTIFY, prepared statements, tipos compuestos, arrays, JSONB, RETURNING, server-side cursors para resultados grandes.

psycopg2 sigue funcionando pero está en mantenimiento. Los proyectos nuevos deberían empezar en psycopg (3.x). API muy similar, mejoras importantes en async, soporte de tipos y rendimiento.

Cuándo usarlo

  • Acceso directo a Postgres cuando SQLAlchemy es excesivo (scripts cortos, herramientas administrativas).
  • Operaciones nativas de Postgres sin equivalente en abstracciones más altas: COPY para cargas masivas, LISTEN/NOTIFY para pub/sub ligero, server-side cursors para streaming.
  • Como driver por debajo de SQLAlchemy (postgresql+psycopg://...).

Cuándo NO usarlo

  • Portabilidad multi-motor. Si tu código debe ejecutarse contra MySQL o SQL Server además de Postgres, usa SQLAlchemy.
  • Servicios asíncronos de muy alto throughput. asyncpg es notablemente más rápido en su nicho (Postgres + asyncio). psycopg async funciona, pero asyncpg está optimizado para ese caso concreto.

Conceptos clave

  • DB-API 2.0. connect(), cursor(), execute(sql, params), fetchone() / fetchall() / fetchmany(). Parametrización con %s o %(name)s, nunca con f-strings.
  • Autocommit. Por defecto, psycopg abre una transacción implícita en el primer comando. conn.autocommit = True cambia a modo autocommit (cada sentencia compromete sola). Imprescindible para VACUUM, CREATE DATABASE y otras sentencias que no pueden ir en transacción.
  • COPY. Para cargas masivas, cursor.copy("COPY t FROM STDIN") es órdenes de magnitud más rápido que INSERT por fila.
  • Server-side cursors. conn.cursor(name="cur") crea un cursor named que mantiene el resultado en el servidor, esencial para iterar sobre resultados de millones de filas sin agotar memoria del cliente.
  • Adaptadores de tipos. Mapeo automático entre tipos Python y tipos Postgres (uuid, datetime, Decimal, dict → JSONB, listas → array). Registrables y extensibles.

Patrón mínimo

import psycopg

# Context manager: connect / cursor / commit / close manejados
with psycopg.connect(
    "postgresql://user:pass@localhost:5432/mydb",
    autocommit=False,
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, email FROM users WHERE country = %s",
            ("ES",),
        )
        for row in cur:
            print(row)

# Carga masiva con COPY
with psycopg.connect("...") as conn, conn.cursor() as cur:
    with cur.copy("COPY events (user_id, ts, payload) FROM STDIN") as cp:
        for record in records:
            cp.write_row(record)

# Server-side cursor para streaming
with psycopg.connect("...") as conn:
    with conn.cursor(name="big_query") as cur:
        cur.itersize = 10_000
        cur.execute("SELECT * FROM huge_table")
        for row in cur:
            process(row)

Trampas habituales

  • Concatenar parámetros en la consulta. Antipatrón clásico que abre inyección SQL. Usa siempre %s o %(name)s y pasa los valores como tupla/dict. Para nombres de tabla o columna dinámicos, usa psycopg.sql.Identifier/SQL.
  • No cerrar transacciones. Si abres conexión sin with y olvidas commit() o rollback(), los cambios se pierden y mantienes locks en el servidor. El with conn: (context manager de conexión) confirma al salir si no hubo excepción.
  • autocommit=True por defecto en código de dominio. Pierdes atomicidad en operaciones que deben ir juntas. Úsalo deliberadamente en sentencias que no admiten transacción, no como configuración global.
  • Mezclar zonas horarias. Postgres distingue TIMESTAMP (sin zona) de TIMESTAMPTZ (con zona, almacenado en UTC). psycopg convierte TIMESTAMPTZ a datetime con tzinfo. TIMESTAMP a datetime naive. Pasar un datetime naive a una columna TIMESTAMPTZ produce resultados según el TIMEZONE de la sesión, y rara vez el que esperas.
  • fetchall() sobre resultados gigantes. Trae todo a memoria del cliente. Para tablas grandes, usa cursores named (server-side) o fetchmany(N) por lotes.

Enlaces

Relacionados en esta página

  • SQLAlchemy, lo usa como driver por debajo.
  • asyncpg, alternativa async-first para Postgres.

asyncpg

asyncpg es un driver Postgres puro nativo y asincrónico mantenido por MagicStack. A diferencia de psycopg async, no es una adaptación: está construido sobre asyncio desde cero y habla directamente el protocolo binario de Postgres. En benchmarks es típicamente de 2 a 5 veces más rápido que las alternativas en cargas async puras.

No implementa DB-API 2.0, su API es propia y asíncrona. Eso lo hace incompatible con SQLAlchemy Core sin un adaptador (existe: sqlalchemy-asyncpg).

Cuándo usarlo

  • Servicios asyncio con alto throughput de queries contra Postgres (APIs FastAPI/Starlette, workers async, procesadores de stream).
  • Cuando el cuello de botella medido es el driver y necesitas exprimir microsegundos.
  • Como driver detrás de SQLAlchemy AsyncEngine (postgresql+asyncpg://...).

Cuándo NO usarlo

  • Código síncrono. No hay manera de usarlo bloqueante. Para sync, psycopg.
  • Si no necesitas el rendimiento. El ecosistema sync (psycopg + SQLAlchemy) es más maduro, con más herramientas (migrations, debuggers, profiling). El async paga complejidad en debugging y manejo de errores.
  • Portabilidad. Solo Postgres. No hay equivalente directo para MySQL u otros motores.

Conceptos clave

  • Pool. asyncpg.create_pool(dsn, min_size=..., max_size=...) crea el pool a nivel de aplicación. async with pool.acquire() as conn: saca una conexión.
  • Parametrización con $1, $2, ... (sintaxis nativa de Postgres, no DB-API). conn.fetch("SELECT ... WHERE id = $1", user_id).
  • API tipada. fetch() devuelve list[Record], fetchrow() un Record o None, fetchval() un valor escalar, execute() para sentencias sin resultados.
  • Prepared statements. await conn.prepare(sql) devuelve un objeto reutilizable. El pool los cachea por conexión automáticamente cuando llamas fetch varias veces con la misma SQL.
  • Tipos. Mapeo nativo a Python para casi todos los tipos Postgres, incluyendo zona horaria en TIMESTAMPTZ. Adaptadores personalizados con conn.set_type_codec.

Patrón mínimo

import asyncio
import asyncpg

async def main():
    # Pool a nivel de aplicación
    pool = await asyncpg.create_pool(
        dsn="postgresql://user:pass@localhost/db",
        min_size=2,
        max_size=10,
    )

    async with pool.acquire() as conn:
        # Lectura parametrizada
        rows = await conn.fetch(
            "SELECT id, email FROM users WHERE country = $1",
            "ES",
        )

        # Transacción explícita
        async with conn.transaction():
            await conn.execute(
                "UPDATE users SET active = $1 WHERE id = $2",
                False, 1,
            )
            await conn.execute(
                "INSERT INTO audit (user_id, action) VALUES ($1, $2)",
                1, "deactivate",
            )

    await pool.close()

asyncio.run(main())

Trampas habituales

  • Mezclar %s con $1. asyncpg usa solo la sintaxis nativa de Postgres ($1, $2, ...). %s (DB-API) no funciona y produce errores confusos.
  • Compartir conexiones entre tareas. Una conexión asyncpg no es thread-safe ni task-safe para uso concurrente. Saca una conexión del pool por tarea (async with pool.acquire()).
  • Olvidar pool.close(). Cerrar el pool drena conexiones limpiamente. En servicios FastAPI/Starlette, gestiónalo en lifespan.
  • Tipos numéricos vs Decimal. Por defecto, NUMERIC se convierte a decimal.Decimal. Para cast a float, registra un codec. no lo hagas a menos que el dominio lo permita (finanzas suele exigir precisión exacta).
  • Errores envueltos en otra excepción. En código async con muchos await, los stack traces son menos directos. Configura asyncpg.exceptions explícitamente en handlers en vez de tragar excepciones genéricas.

Enlaces

Relacionados en esta página

  • psycopg, equivalente síncrono y opción async alternativa.
  • SQLAlchemy, puede usar asyncpg como driver con AsyncEngine.

pyodbc

pyodbc es el driver ODBC para Python. Más que un driver, es un puente que delega en los drivers ODBC del sistema operativo (Microsoft ODBC Driver for SQL Server, FreeTDS, drivers de Oracle, IBM Db2, Snowflake…). Implementa DB-API 2.0.

En la práctica, lo encuentras casi siempre en escenarios SQL Server (Windows / Azure SQL / on-prem) y en migraciones desde sistemas legacy que solo exponen ODBC.

Cuándo usarlo

  • SQL Server / Azure SQL Database: la combinación más habitual. pyodbc + ODBC Driver 18 for SQL Server es la ruta canónica.
  • Sistemas legacy donde el único driver disponible es ODBC.
  • Cuando tu organización ya tiene infraestructura ODBC homogénea (DSNs configurados, drivers instalados centralmente).

Cuándo NO usarlo

  • Postgres o MySQL. Hay drivers nativos mejores (psycopg, mysqlclient / PyMySQL).
  • Contenedores ligeros. ODBC requiere instalar el driver del SO (msodbcsql18 en Debian, equivalentes en Alpine). Añade superficie de imagen y dependencias.
  • Async. No hay soporte async razonable. Para SQL Server async, mira aioodbc (wrapper sobre pyodbc en hilos) o pymssql async. Ambos son menos maduros que el ecosistema síncrono.

Conceptos clave

  • Cadena de conexión. Formato propio de ODBC: DRIVER={ODBC Driver 18 for SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...;TrustServerCertificate=yes. Larga, propensa a errores. Centralízala en configuración.
  • Driver del SO. pyodbc no incluye los drivers. En Linux: apt install msodbcsql18 unixodbc-dev. En Windows: instalador de Microsoft. En CI/Docker, instalarlo es paso obligatorio.
  • Parametrización con ?. No %s, no :name. cur.execute("SELECT ... WHERE id = ?", user_id).
  • fast_executemany=True acelera enormemente executemany() en cargas masivas a SQL Server. Por defecto está desactivado por razones históricas.
  • Encoding. Históricamente fuente de dolor con caracteres no ASCII. ODBC Driver 17+ y pyodbc recientes lo gestionan bien con UTF-8. En drivers antiguos, configura cnxn.setdecoding(pyodbc.SQL_CHAR, encoding="utf-8").

Patrón mínimo

import pyodbc

conn_str = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=myserver.database.windows.net;"
    "DATABASE=mydb;"
    "UID=myuser;"
    "PWD=mypassword;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
)

with pyodbc.connect(conn_str) as conn:
    with conn.cursor() as cur:
        # Lectura parametrizada
        cur.execute(
            "SELECT id, email FROM users WHERE country = ?",
            "ES",
        )
        for row in cur.fetchall():
            print(row.id, row.email)

# Carga masiva
with pyodbc.connect(conn_str) as conn:
    cur = conn.cursor()
    cur.fast_executemany = True
    cur.executemany(
        "INSERT INTO events (user_id, ts) VALUES (?, ?)",
        records,
    )
    conn.commit()

Trampas habituales

  • Driver no instalado. pyodbc.InterfaceError: ('IM002', '... Data source name not found') casi siempre significa que el driver ODBC del SO falta. Verifica con odbcinst -q -d (Linux) o el administrador de orígenes de datos ODBC (Windows).
  • TLS. Por defecto el driver moderno exige cifrado. Encrypt=yes;TrustServerCertificate=yes salta validación. Úsalo solo en entornos de desarrollo. En producción, instala el certificado correcto.
  • fast_executemany y tipos. Acelera mucho executemany pero es estricto con tipos: pasar None mezclado con Decimal en la misma columna puede fallar. Asegura tipado homogéneo del batch.
  • Encoding silente. En sistemas con ODBC Driver 13 o anteriores, columnas NVARCHAR con caracteres acentuados aparecen corruptas. Solución: actualizar el driver del SO, no la librería Python.
  • Cadenas de conexión en código. Acaban en commits. Usa variables de entorno o un gestor de secretos. Nunca hardcodees PWD=.

Enlaces

Relacionados en esta página