Trabajo con Transacciones y el DBAPI

• Ultima modificación: Mar 6, 2024 | YouTube

Preparación

from sqlalchemy import create_engine

engine = create_engine(
    "sqlite+pysqlite:///:memory:",
    echo=True,
    future=True,
)

Envio de comandos SQL al motor de la base de datos

#
# El paquete text permite crear consultas en el lenguaje SQL
#
from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("select 'hello world'"))
    print(result.all())

2022-09-13 11:24:50,037 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,048 INFO sqlalchemy.engine.Engine select 'hello world'
2022-09-13 11:24:50,049 INFO sqlalchemy.engine.Engine [generated in 0.01197s] ()
[('hello world',)]
2022-09-13 11:24:50,050 INFO sqlalchemy.engine.Engine ROLLBACK

Estilo “commit as you go”

with engine.connect() as conn:

    conn.execute(text("CREATE TABLE some_table (x int, y int)"))

    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [
            {"x": 1, "y": 1},
            {"x": 2, "y": 4},
        ],
    )

    conn.commit()  # confirmar los cambios hasta aca

2022-09-13 11:24:50,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,192 INFO sqlalchemy.engine.Engine CREATE TABLE some_table (x int, y int)
2022-09-13 11:24:50,193 INFO sqlalchemy.engine.Engine [generated in 0.00186s] ()
2022-09-13 11:24:50,194 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2022-09-13 11:24:50,194 INFO sqlalchemy.engine.Engine [generated in 0.00051s] ((1, 1), (2, 4))
2022-09-13 11:24:50,195 INFO sqlalchemy.engine.Engine COMMIT

Estilo “begin once” (modo preferido en la práctica)

#
# El commit es automático cuando se termina el bloque with.
# Hay un rollback automático en caso de falla del bloque with.
#
with engine.begin() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [
            {"x": 6, "y": 8},
            {"x": 9, "y": 10},
        ],
    )

2022-09-13 11:24:50,311 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,312 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2022-09-13 11:24:50,313 INFO sqlalchemy.engine.Engine [cached since 0.1189s ago] ((6, 8), (9, 10))
2022-09-13 11:24:50,314 INFO sqlalchemy.engine.Engine COMMIT

Extracción de filas

with engine.connect() as conn:

    # Result es un objeto interactivo de tipo ResultProxy
    result = conn.execute(text("SELECT x, y FROM some_table"))

    # Iteracion sobre los nombres de los atributos del objeto
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

2022-09-13 11:24:50,371 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,373 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2022-09-13 11:24:50,373 INFO sqlalchemy.engine.Engine [generated in 0.00182s] ()
x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
2022-09-13 11:24:50,374 INFO sqlalchemy.engine.Engine ROLLBACK

with engine.connect() as conn:

    result = conn.execute(text("SELECT x, y FROM some_table"))

    # Iteracion sobre la tupla
    for x, y in result:
        print(f"x: {x}  y: {y}")

2022-09-13 11:24:50,424 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,425 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2022-09-13 11:24:50,425 INFO sqlalchemy.engine.Engine [cached since 0.05422s ago] ()
x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
2022-09-13 11:24:50,426 INFO sqlalchemy.engine.Engine ROLLBACK

with engine.connect() as conn:

    result = conn.execute(text("SELECT x, y FROM some_table"))

    # Iteracion sobre los indices de los elementos en el objeto
    for row in result:
        print(f"x: {row[0]}  y: {row[1]}")

2022-09-13 11:24:50,470 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,471 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2022-09-13 11:24:50,472 INFO sqlalchemy.engine.Engine [cached since 0.1004s ago] ()
x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
2022-09-13 11:24:50,473 INFO sqlalchemy.engine.Engine ROLLBACK

with engine.connect() as conn:

    result = conn.execute(text("SELECT x, y FROM some_table"))

    # Iteracion como mapping
    for dict_row in result.mappings():
        x = dict_row["x"]
        y = dict_row["y"]
        print(f"x: {x}  y: {y}")

2022-09-13 11:24:50,515 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,516 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2022-09-13 11:24:50,517 INFO sqlalchemy.engine.Engine [cached since 0.1456s ago] ()
x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
2022-09-13 11:24:50,518 INFO sqlalchemy.engine.Engine ROLLBACK

Envio de un grupo de parámetros

with engine.connect() as conn:

    result = conn.execute(
        text(
            "SELECT x, y FROM some_table WHERE y > :z"
        ),  # note el uso de :z al final del string
        {"z": 2},  # diccionario con los valores de los parametros
    )

    for row in result:
        print(f"x: {row.x}  y: {row.y}")

2022-09-13 11:24:50,557 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,558 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ?
2022-09-13 11:24:50,558 INFO sqlalchemy.engine.Engine [generated in 0.00153s] (2,)
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
2022-09-13 11:24:50,560 INFO sqlalchemy.engine.Engine ROLLBACK

Envio de múltiples grupos de parámetros

with engine.connect() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [
            {"x": 11, "y": 12},
            {"x": 13, "y": 14},
        ],
    )
    conn.commit()

2022-09-13 11:24:50,636 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,637 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2022-09-13 11:24:50,637 INFO sqlalchemy.engine.Engine [cached since 0.4433s ago] ((11, 12), (13, 14))
2022-09-13 11:24:50,638 INFO sqlalchemy.engine.Engine COMMIT

Empaquetado

stmt = text("SELECT x, y FROM some_table WHERE y > :z ORDER BY x, y").bindparams(z=6)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

2022-09-13 11:24:50,714 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,715 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
2022-09-13 11:24:50,715 INFO sqlalchemy.engine.Engine [generated in 0.00158s] (6,)
x: 6  y: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14
2022-09-13 11:24:50,716 INFO sqlalchemy.engine.Engine ROLLBACK

Ejecución con una sesión ORM

from sqlalchemy.orm import Session

stmt = text("SELECT x, y FROM some_table WHERE y > :z ORDER BY x, y").bindparams(z=6)

with Session(engine) as session:
    result = session.execute(stmt)
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

2022-09-13 11:24:50,814 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,815 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
2022-09-13 11:24:50,815 INFO sqlalchemy.engine.Engine [cached since 0.1014s ago] (6,)
x: 6  y: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14
2022-09-13 11:24:50,816 INFO sqlalchemy.engine.Engine ROLLBACK

#
# Sesion usa "commit as you go" por defecto
#
with Session(engine) as session:
    result = session.execute(
        text("UPDATE some_table SET y=:y WHERE x=:x"),
        [
            {"x": 9, "y": 11},
            {"x": 13, "y": 15},
        ],
    )
    session.commit()

2022-09-13 11:24:50,853 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-13 11:24:50,854 INFO sqlalchemy.engine.Engine UPDATE some_table SET y=? WHERE x=?
2022-09-13 11:24:50,855 INFO sqlalchemy.engine.Engine [generated in 0.00063s] ((11, 9), (15, 13))
2022-09-13 11:24:50,856 INFO sqlalchemy.engine.Engine COMMIT