El puente con SQL

import sqlite3

import pandas as pd

# Una base en memoria: no deja ningun archivo y sirve para practicar.
conexion = sqlite3.connect(":memory:")

pedidos = pd.read_csv("tienda_pedidos.csv")
clientes = pd.read_csv("tienda_clientes.csv")

# to_sql escribe un DataFrame como una tabla.
pedidos.to_sql("pedidos", conexion, index=False, if_exists="replace")
clientes.to_sql("clientes", conexion, index=False, if_exists="replace")

tablas = pd.read_sql_query(
    "SELECT name FROM sqlite_master WHERE type = 'table'", conexion)
print("tablas creadas:", list(tablas["name"]))

# read_sql_query devuelve un DataFrame, igual que read_csv.
primeros = pd.read_sql_query("SELECT * FROM pedidos LIMIT 3", conexion)
print()
print(primeros.to_string(index=False))
print()
print("tipo de lo que devuelve:", type(primeros).__name__)
print("dtypes:")
print(primeros.dtypes.to_string())

conexion.close()

print()
print("Eso es todo el puente: to_sql para escribir, read_sql_query para")
print("leer. Del otro lado hay un DataFrame como cualquier otro.")

import sqlite3

import pandas as pd

conexion = sqlite3.connect(":memory:")
pedidos = pd.read_csv("tienda_pedidos.csv")
pedidos.to_sql("pedidos", conexion, index=False, if_exists="replace")

consulta = """
SELECT id, cliente_id, total
FROM pedidos
WHERE total > 400 AND estado = 'entregado'
ORDER BY total DESC, id ASC
LIMIT 5
"""
en_sql = pd.read_sql_query(consulta, conexion)
print("SQL:")
print(en_sql.to_string(index=False))

en_pandas = (pedidos
             .loc[(pedidos["total"] > 400) & (pedidos["estado"] == "entregado"),
                  ["id", "cliente_id", "total"]]
             .sort_values(["total", "id"], ascending=[False, True],
                          kind="stable")
             .head(5)
             .reset_index(drop=True))
print()
print("pandas:")
print(en_pandas.to_string(index=False))

print()
print("iguales:", bool(en_sql.equals(en_pandas)))

conexion.close()

print()
print("SELECT es df[[...]], WHERE es la mascara, ORDER BY es sort_values")
print("y LIMIT es head. Es el mismo vocabulario con otra sintaxis.")

import sqlite3

import pandas as pd

conexion = sqlite3.connect(":memory:")
pedidos = pd.read_csv("tienda_pedidos.csv")
clientes = pd.read_csv("tienda_clientes.csv")
pedidos.to_sql("pedidos", conexion, index=False, if_exists="replace")
clientes.to_sql("clientes", conexion, index=False, if_exists="replace")

consulta = """
SELECT p.id, p.total, c.nombre
FROM pedidos AS p
INNER JOIN clientes AS c ON p.cliente_id = c.id
ORDER BY p.id
"""
en_sql = pd.read_sql_query(consulta, conexion)

en_pandas = (pedidos.merge(clientes, left_on="cliente_id", right_on="id",
                           how="inner", suffixes=("", "_cliente"))
             .rename(columns={"id": "id_pedido"})
             [["id_pedido", "total", "nombre"]]
             .rename(columns={"id_pedido": "id"})
             .sort_values("id", kind="stable")
             .reset_index(drop=True))

print("SQL   :", en_sql.shape)
print("pandas:", en_pandas.shape)
print()
print(en_sql.head(3).to_string(index=False))

try:
    pd.testing.assert_frame_equal(en_sql, en_pandas)
    print()
    print("assert_frame_equal: los dos caminos dan EXACTAMENTE lo mismo.")
except AssertionError as error:
    print("difieren:", str(error).splitlines()[0])

print()
perdidos = len(pedidos) - len(en_sql)
print("pedidos:", len(pedidos), "| resultado:", len(en_sql),
      "| se perdieron:", perdidos)
print("Son pedidos de un cliente_id que no esta en la tabla de clientes.")
print("INNER JOIN los descarta, igual que how='inner' y con el mismo")
print("silencio: la unica senal es que el resultado tiene menos filas.")

conexion.close()

import sqlite3

import pandas as pd

conexion = sqlite3.connect(":memory:")
pd.read_csv("tienda_pedidos.csv").to_sql("pedidos", conexion, index=False,
                                         if_exists="replace")

# La forma correcta: la consulta lleva ? y los valores van aparte.
estado = "entregado"
minimo = 400

seguro = pd.read_sql_query(
    "SELECT COUNT(*) AS n FROM pedidos WHERE estado = ? AND total > ?",
    conexion, params=(estado, minimo))
print("con params:", int(seguro["n"].item()), "pedidos")

# La forma peligrosa: pegar el valor adentro del texto de la consulta.
inseguro = pd.read_sql_query(
    f"SELECT COUNT(*) AS n FROM pedidos WHERE estado = '{estado}'", conexion)
print("con f-string:", int(inseguro["n"].item()), "pedidos")
print("Los dos funcionan. El problema aparece con OTRO valor.")

print()
# Un valor con una comilla adentro rompe la consulta armada con f-string.
raro = "O'Brien"
try:
    pd.read_sql_query(
        f"SELECT COUNT(*) AS n FROM pedidos WHERE estado = '{raro}'", conexion)
except Exception as error:
    print("con f-string y un apostrofe ->", type(error).__name__)

con_params = pd.read_sql_query(
    "SELECT COUNT(*) AS n FROM pedidos WHERE estado = ?", conexion,
    params=(raro,))
print("con params y el mismo valor:", int(con_params["n"].item()),
      "-> sin error")

print()
print("Ese apostrofe es la version inocente. La version maliciosa es un")
print("valor que cierra la comilla y agrega otra instruccion. Con params,")
print("el valor NUNCA se interpreta como codigo: es un dato y nada mas.")

conexion.close()

import sqlite3

import pandas as pd

conexion = sqlite3.connect(":memory:")
pd.read_csv("tienda_pedidos.csv").to_sql("pedidos", conexion, index=False,
                                         if_exists="replace")

# Opcion A: que la base agregue y devuelva 39 filas.
en_sql = pd.read_sql_query("""
    SELECT cliente_id, COUNT(*) AS pedidos, ROUND(SUM(total), 2) AS total
    FROM pedidos
    GROUP BY cliente_id
    ORDER BY cliente_id
""", conexion)

# Opcion B: traer las 120 filas y agregar en pandas.
todo = pd.read_sql_query("SELECT * FROM pedidos", conexion)
en_pandas = (todo.groupby("cliente_id", as_index=False)
             .agg(pedidos=("id", "size"), total=("total", "sum"))
             .assign(total=lambda d: d["total"].round(2))
             .sort_values("cliente_id", kind="stable")
             .reset_index(drop=True))

print("SQL   :", en_sql.shape, "filas que viajaron:", len(en_sql))
print("pandas:", en_pandas.shape, "filas que viajaron:", len(todo))
print()
print(en_sql.head(4).to_string(index=False))

print()
iguales = en_sql.equals(en_pandas)
print("dan lo mismo:", bool(iguales))
if not iguales:
    dif = en_sql.compare(en_pandas)
    print("primeras diferencias:")
    print(dif.head(3).to_string())

conexion.close()

print()
print("Con 120 filas da igual. Con 50 millones no: agregar en la base hace")
print("que viajen 39 filas en vez de 50 millones. La regla practica es")
print("filtrar y agregar del lado de la base, y hacer en pandas lo que la")
print("base no sabe hacer.")