SqlAlchemy Intro (2): Conectando.

*Disclaimer: Si quieres aprender, no leas, escribe.

TLDR: Creamos el objeto de conexion al engine con engine.connect() o con Session(engine), dependiendo si trabajamos con Core o con ORM. Si realizamos consultas se nos retornará el objeto Result.

Siguiendo con la guía de introducción a SQLAlchemy.

Transacciones: trabajando con el engine.

Conexiones engine.connect():

Esta función se usa para crear un objeto que permite interactuar con la base de datos. Habitualmente se usa dentro de un “context manager”. De esta manera que podríamos ejecutar una consulta a la base de datos de la siguiente forma:

from sqlalchemy import text

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

Los cambios ejecutados no se efectúan hasta no hacer un “commit” al objeto.

Hay un modo “autocommit”, que explicaremos más adelante.

Cambios persistentes:

Para que los cambios realizados en una consulta sean persistentes utilizaremos la función commit() del objeto de conexión.

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()

De esta manera cuando queremos realizar un cambio permanente simplemente ejecutamos la función Commit() para hacerlo permanente. A esto se le llama “commit as you go”.

El otro sistema para hacer cambios permanentes se denomina “begin once”. Con este sistema en vez de utilizar engine.connect(), utilizaremos "engine.begin()". Este método creará la conexión y hará “commit” una vez finalizado el bucle.

with engine.begin() as conn:
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 6, "y": 10}, {"x": 9, "y": 10}]
    )

Instrucciones básicas.

El objeto que retorna una consulta, tanto las consultas Conection.execute() como las consultas Session.execute() (de las que hablaremos más adelante), se denomina Result. Éste es un objeto iterable compuesto por filas de resultados (Row). Las filas se componen de "named tuples",

Obtención de valores de las filas.

• Tuple Assignment:

result = conn.execute(text("select x, y from some_table"))

for x, y in result:
    # ...

• Integer Index:

result = conn.execute(text("select x, y from some_table"))

  for row in result:
      x = row[0]

• Attribute Name:

result = conn.execute(text("select x, y from some_table"))

for row in result:
    y = row.y

    # illustrate use with Python f-strings
    print(f"Row: {row.x} {row.y}")

• Mapping Access:

Mediante la función "Result.mappings()", transformamos el resultado en un generador de diccionarios. De esta manera cada fila es un diccionario compuesto del nombre de la columna y su valor.

result = conn.execute(text("select x, y from some_table"))

for dict_row in result.mappings():
    x = dict_row['x']
    y = dict_row['y']

Envío de parámetros.

Hasta el momento hemos ejecutado declaración SQL utilizando la funicón Connection.execute(), ésta también permite incluir parámetros en ella llamados "bound parameters".

>>> with engine.connect() as conn:
...     result = conn.execute(
...         text("SELECT x, y FROM some_table WHERE y > :y"),
...         {"y": 2}
...     )
...     for row in result:
...        print(f"x: {row.x}  y: {row.y}")

El parámetro se presenta dentro de la función text() como ":y". En el siguiente argumento de la función Conection.execute() incluimos los "bound parameters", en este caso un diccionario que contiene el nombre del parámetro presentado anteriormente ":y" y su valor.

Cuando se trata de enviar un único parámetro se puede integrar en la declración SQL mediente el método "TextClause.bindparams()".

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     for row in result:
...        print(f"x: {row.x}  y: {row.y}")

• Envío de múltiples parámetros:

En el caso de declaraciones SQL que no retornan un resultado (p.ej. "INSERT"), podemos enviar múltiples parámetros de la misma manera que en el ejemplo anterior, pero en vez de incluir un diccionario como "bound parameters" incluimos una lista de diccionarios.

>>> 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()

Ejecutando con ORM: "Session".

Podríamos decir que el equivalente ORM de la "Connection" usada en "Core" es "Session".

>>> from sqlalchemy.orm import Session

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
>>> with Session(engine) as session:
...     result = session.execute(stmt)
...     for row in result:
...        print(f"x: {row.x}  y: {row.y}")

El objeto "Session" también permite el uso de "Commit as you go" y el envío de múltiples parámetros con "bound parmeters".

>>> 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()