Cómo calificar ejercicios de PostgreSQL automáticamente, con pytest y una base de datos real

Also in EnglishTambém em português

¿Cómo funciona la calificación en PostgreSQL?

Una pregunta de SQL tiene una respuesta que se puede revisar: las filas que devuelve la consulta. Para calificar en el dialecto del propio PostgreSQL, necesitas un servidor PostgreSQL real, con los mismos datos para todos los estudiantes. Aquí queda así:

1. Un seed tuyo, db/01-shop.sql, crea las tablas, las llena y crea un rol que solo puede leer.
2. Cada estudiante escribe un archivo por pregunta: answers/q1.sql, answers/q2.sql y así sucesivamente.
3. Envías el seed, tus pruebas y las respuestas del estudiante a POST /api/run-tests/ en el paquete python-3.14-tests con "services": ["postgresql"]. Un servidor nuevo arranca, el seed corre y luego pytest corre tus pruebas.
4. La respuesta dice si cada prueba pasó o falló, con el mensaje de pytest, una nota y un recibo firmado.

¿Por qué no SQLite? Si tu curso enseña PostgreSQL, las respuestas correctas usan PostgreSQL: el DISTINCT ON y el date_trunc de abajo no existen en SQLite, así que un calificador en SQLite las daría por malas. Para las preguntas que SQLite sí responde, la guía de SQLite (en inglés) funciona en todos los planes, con problemas guardados y pruebas ocultas.

¿Cómo escribo el seed?

El ejercicio usa una tienda pequeña: cuatro clientes y seis pedidos hechos por tres de ellos. Cada archivo .sql en db/ corre antes de las pruebas, en orden de nombre, como el superusuario postgres.

CREATE TABLE customers (
    id serial PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL
);
CREATE TABLE orders (
    id serial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers,
    placed_on date NOT NULL,
    amount integer NOT NULL
);
INSERT INTO customers (name, city) VALUES
    ('Asha', 'Pune'), ('Ben', 'Delhi'), ('Chen', 'Pune'), ('Dev', 'Mumbai');
INSERT INTO orders (customer_id, placed_on, amount) VALUES
    (1, '2026-01-05', 120), (2, '2026-01-20', 80), (1, '2026-02-03', 200),
    (3, '2026-02-14', 50), (2, '2026-03-01', 150), (1, '2026-03-09', 30);

-- El rol con el que corren las consultas de los estudiantes: lee todas las tablas y no cambia nada.
CREATE ROLE student LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;

Las dos últimas líneas son las que importan. Dentro del sandbox, postgres es un superusuario sin contraseña, así que una consulta corrida como postgres podría cambiar cualquier cosa. El rol student lee todas las tablas y no cambia ninguna, y las pruebas corren las consultas de los estudiantes con ese rol.

¿Qué escriben los estudiantes?

Tres preguntas, un archivo cada una. Estas son las respuestas correctas:

1. Clientes por ciudad: cada ciudad y cuántos clientes viven en ella, en cualquier orden.
   answers/q1.sql Copy

   SELECT city, count(*) FROM customers GROUP BY city;

2. El último pedido de cada cliente: nombre, fecha y monto, para los clientes que hicieron pedidos, en cualquier orden. DISTINCT ON se queda con la primera fila de cada cliente según el ORDER BY.
   answers/q2.sql Copy

   SELECT DISTINCT ON (c.id) c.name, o.placed_on, o.amount
   FROM customers c
   JOIN orders o ON o.customer_id = c.id
   ORDER BY c.id, o.placed_on DESC;

3. Ingresos mensuales con total acumulado: cada mes como YYYY-MM, sus ingresos y el total hasta ese mes, con los meses en orden. Usa una función de ventana sobre una agregación.
   answers/q3.sql Copy

   SELECT to_char(date_trunc('month', placed_on), 'YYYY-MM') AS month,
          sum(amount) AS revenue,
          sum(sum(amount)) OVER (ORDER BY date_trunc('month', placed_on)) AS running_total
   FROM orders
   GROUP BY date_trunc('month', placed_on)
   ORDER BY date_trunc('month', placed_on);

¿Cómo revisan las pruebas una consulta?

Una prueba de pytest por pregunta, más una que detecta respuestas escritas a mano. El paquete python-3.14-tests incluye psycopg, y DATABASE_URL apunta al servidor.

import os
from datetime import date
from pathlib import Path

import psycopg

DB = os.environ["DATABASE_URL"]


def run(question):
    """Corre answers/<question>.sql del estudiante como el rol de solo lectura student y devuelve las filas
    de su última sentencia."""
    sql = Path(f"answers/{question}.sql").read_text()
    with psycopg.connect(DB, user="student", options="-c statement_timeout=5s") as conn:
        cur = conn.execute(sql)
        while cur.nextset():
            pass
        return cur.fetchall()


def test_q1_customers_per_city():
    # Cualquier orden de las filas es correcto, así que compara las filas ordenadas.
    assert sorted(run("q1")) == [("Delhi", 1), ("Mumbai", 1), ("Pune", 2)]


def test_q2_latest_order_per_customer():
    assert sorted(run("q2")) == [
        ("Asha", date(2026, 3, 9), 30),
        ("Ben", date(2026, 3, 1), 150),
        ("Chen", date(2026, 2, 14), 50),
    ]


def test_q3_monthly_revenue_with_running_total():
    # La pregunta pide los meses en orden, así que aquí el orden cuenta.
    assert run("q3") == [("2026-01", 200, 200), ("2026-02", 250, 450), ("2026-03", 180, 630)]


def test_q3_counts_a_new_order():
    # Detecta una respuesta que imprime las filas esperadas sin leer la tabla.
    with psycopg.connect(DB, autocommit=True) as admin:
        admin.execute("INSERT INTO orders (customer_id, placed_on, amount) VALUES (4, '2026-04-02', 100)")
        try:
            assert run("q3")[-1] == ("2026-04", 100, 730)
        finally:
            admin.execute("DELETE FROM orders WHERE placed_on = '2026-04-02'")

• run() lee el archivo del estudiante, se conecta como student con un statement_timeout de 5 segundos y devuelve las filas de la última sentencia del archivo, así que una respuesta que primero arma una tabla temporal sigue funcionando.
• Orden de las filas: las preguntas 1 y 2 comparan las filas con sorted(), así que cualquier orden pasa. La pregunta 3 pide los meses en orden, así que sus filas se comparan tal como llegan.
• Tipos: psycopg devuelve los tipos de PostgreSQL como tipos de Python, así que una columna date se compara con date(2026, 3, 9) y un count o un sum con un número común.
• La cuarta prueba agrega un pedido de abril como superusuario, vuelve a correr la pregunta 3 y espera la fila de abril, y luego quita el pedido. Una respuesta que solo imprime las filas esperadas no puede pasarla.

¿Cómo califico a un estudiante?

Envía los cinco archivos con el cliente de Python (pip install onlinecompiler), o como cuerpo JSON a https://api.onlinecompiler.io/api/run-tests/ con "compiler": "python-3.14-tests", "suite": {"preset": "pytest", "services": ["postgresql"]}, "memory_limit": 1024 y los archivos. Este estudiante, Ben, olvidó el DESC en la pregunta 2, así que DISTINCT ON se quedó con el primer pedido de cada cliente en lugar del último:

SELECT DISTINCT ON (c.id) c.name, o.placed_on, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.placed_on;

from pathlib import Path
from onlinecompiler import OnlineCompiler

client = OnlineCompiler()  # lee ONLINECOMPILER_API_KEY
names = ["db/01-shop.sql", "tests/test_answers.py", "answers/q1.sql", "answers/q2.sql", "answers/q3.sql"]
result = client.run_suite(
    "python-3.14-tests",
    [{"name": name, "content": Path(name).read_text()} for name in names],
    preset="pytest",
    services=["postgresql"],
    memory_limit=1024,  # la memoria de la base de datos cuenta en la de la ejecución
)
print(result["summary"])  # 3 of 4 tests passed.

La respuesta, resumida:

{
  "verdict": "failed",
  "passed": 3, "failed": 1, "total": 4,
  "score": 75.0,
  "tests": [
    {"name": "test_q1_customers_per_city", "status": "passed", "time": 0.017, "message": null},
    {"name": "test_q2_latest_order_per_customer", "status": "failed", "time": 0.015,
     "message": "AssertionError: assert [('Asha', dat..., 2, 14), 50)] == [('Asha', dat..., 2, 14), 50)]\n  \n  At index 0 diff: ('Asha', datetime.date(2026, 1, 5), 120) != ('Asha', datetime.date(2026, 3, 9), 30)\n  Use -v to get more diff"},
    {"name": "test_q3_monthly_revenue_with_running_total", "status": "passed", "time": 0.013, "message": null},
    {"name": "test_q3_counts_a_new_order", "status": "passed", "time": 0.023, "message": null}
  ],
  "run": {"exit_code": 1, "time": 1.1516, "output": "", "error": ".F..    [100%]\n=== FAILURES ===\n..."},
  "pack": {"version": "1.1"},
  "summary": "3 of 4 tests passed."
}

El mensaje de pytest muestra la primera fila de Ben, el pedido de enero de Asha, donde la prueba esperaba el de marzo. La ejecución completa tardó unos 1.2 segundos con el arranque del servidor y el seed, y las respuestas con todas las preguntas correctas volvieron accepted, 4 de 4.

¿Qué reciben otras respuestas?

Cada fila es una ejecución de las mismas pruebas con una respuesta cambiada; las otras dos respuestas eran correctas.

La respuesta a la pregunta 1 con tabla temporal:

CREATE TEMP TABLE c AS SELECT city FROM customers;
SELECT city, count(*) FROM c GROUP BY city;

¿Cómo califico a un grupo completo?

Deja el seed y las pruebas donde están y las respuestas de cada estudiante en submissions/<nombre>/, como q1.sql, q2.sql y q3.sql. Este script califica a todos los estudiantes y escribe grades.csv:

import csv
from pathlib import Path
from onlinecompiler import OnlineCompiler

client = OnlineCompiler()  # lee ONLINECOMPILER_API_KEY
ours = [{"name": name, "content": Path(name).read_text()} for name in ["db/01-shop.sql", "tests/test_answers.py"]]

with open("grades.csv", "w", newline="") as f:
    out = csv.writer(f)
    out.writerow(["estudiante", "veredicto", "pasaron", "total", "nota", "pruebas que fallaron"])
    for folder in sorted(Path("submissions").iterdir()):
        answers = [{"name": f"answers/{p.name}", "content": p.read_text()} for p in sorted(folder.glob("*.sql"))]
        r = client.run_suite("python-3.14-tests", ours + answers, preset="pytest", services=["postgresql"], memory_limit=1024)
        failed = [t["name"] for t in r["tests"] if t["status"] != "passed"]
        out.writerow([folder.name, r["verdict"], r["passed"], r["total"], r["score"], "; ".join(failed)])
        print(folder.name, r["verdict"], f"{r['passed']}/{r['total']}")

estudiante,veredicto,pasaron,total,nota,pruebas que fallaron
asha,accepted,4,4,100.0,
ben,failed,3,4,75.0,test_q2_latest_order_per_customer
chen,failed,3,4,75.0,test_q3_counts_a_new_order

Cada estudiante es una ejecución, con su propio servidor. Para dar puntos distintos a cada pregunta, busca los nombres de las pruebas en una tabla tuya, como muestra la página del calificador de Java (en inglés).

¿Cuáles son los límites?

• Plan: Pro y superiores, y los planes de exámenes, con memory_limit de al menos 1,024 MB (precios).
• Servidor: PostgreSQL 18.6, 8 conexiones a la vez, datos en memoria que desaparecen cuando termina la ejecución.
• Datos: el seed viaja en la petición, hasta 20 archivos y 1 MB en total; los datos del servidor comparten con los archivos los 256 MB del sandbox.
• Tiempo: la suite completa tiene 20 segundos por defecto y hasta 30, con el arranque del servidor y el seed.
• Un seed con un error detiene la ejecución antes de cualquier prueba, con el mensaje de PostgreSQL y el veredicto no_report.

Cada ajuste y cada error están en la referencia de run-tests (en inglés).

¿Puedo escribir las pruebas en JavaScript?

Sí. El paquete nodejs-24-tests tiene los mismos services, con pg para la conexión y Jest, Mocha o node:test para las pruebas; el seed funciona tal cual. La página de pytest y Jest (en inglés) califica así una app de Express con PostgreSQL.

¿Dónde encaja esto en un curso?

Las suites corren por run-tests (en inglés), así que tu seed y tus pruebas se quedan en tu sistema y viajan con cada petición. Para preguntas de SQLite en cualquier plan, con problemas guardados, pruebas ocultas y un editor en el navegador para los estudiantes, ve la guía de SQLite (en inglés). Para calificar tareas en todos los demás lenguajes, ve la API de calificación automática. Crea una clave de API para empezar; las escuelas pueden pedir Pro gratis con el programa Founding Institutions (en inglés) o con Pro gratis.

Preguntas frecuentes

¿Se pueden calificar consultas de PostgreSQL automáticamente, y no solo de SQLite?

Sí, en Pro y superiores. Agrega "services": ["postgresql"] a una suite de pruebas en el paquete python-3.14-tests y un servidor PostgreSQL 18.6 nuevo arranca en el sandbox, con los datos de tus archivos .sql en db/. Tus pruebas de pytest corren la consulta de cada estudiante en él y comparan las filas, así que las respuestas que usan DISTINCT ON, date_trunc, funciones de ventana o cualquier otra parte del dialecto de PostgreSQL se califican como PostgreSQL las corre. Las respuestas de un estudiante son una petición y una ejecución.

¿La consulta de un estudiante puede cambiar o borrar los datos?

No, cuando las pruebas la corren con un rol que solo puede leer. El seed crea el rol student con SELECT en todas las tablas, y las pruebas se conectan con ese rol, así que un DELETE se detiene con "permission denied for table orders" y un CREATE TABLE con "permission denied for schema public". Una tabla temporal sigue funcionando, lo que ayuda a las respuestas hechas en dos pasos. Además, cada estudiante recibe un servidor nuevo, así que nada de lo que corre un estudiante llega a otro.

¿Importa el orden de las filas?

Lo decide tu prueba. Compara sorted(rows) cuando cualquier orden es correcto, y compara las filas tal como llegan cuando la pregunta pide ORDER BY. En esta guía, las preguntas 1 y 2 aceptan cualquier orden, y la pregunta 3 pide los meses en orden.

¿Cómo evito que los estudiantes escriban a mano las filas esperadas?

Agrega una prueba que cambie los datos y revise que la respuesta cambie con ellos. Aquí la cuarta prueba inserta un pedido de abril como superusuario, vuelve a correr la consulta del estudiante y espera una fila de abril con el nuevo total acumulado; una respuesta que solo imprime las tres filas esperadas con VALUES pasa la pregunta 3 y falla esa prueba.

¿Qué pasa cuando una consulta nunca termina?

Las pruebas se conectan con statement_timeout de 5 segundos, así que PostgreSQL cancela la consulta y esa prueba falla con "canceling statement due to statement timeout"; las demás pruebas corren igual. La suite completa, con el arranque del servidor y el seed, tiene 20 segundos por defecto y hasta 30 con time_limit.

¿Qué plan necesito?

PostgreSQL en una suite necesita Pro o superior, o un plan de exámenes, y un memory_limit de al menos 1,024 MB en la petición, porque la memoria de la base de datos cuenta en la de la ejecución. El conjunto de respuestas de un estudiante cuenta como una ejecución, tenga las preguntas que tenga. Las escuelas pueden pedir Pro gratis con los programas Founding Institutions y Pro gratis. En todos los planes, las preguntas de SQLite y DuckDB se pueden calificar como muestra la guía de SQLite.

¿Cuántos datos puede tener el seed?

Una petición lleva hasta 20 archivos y 1 MB en total, así que el seed es un texto SQL dentro de eso. Para tablas más grandes, genera las filas en el propio seed con generate_series. El servidor guarda sus datos en los 256 MB de memoria del sandbox, compartidos con los archivos de la petición, de los que el servidor vacío ocupa unos 31 MB, y acepta 8 conexiones a la vez.