4.2 Python y SQLite

sqlite3 es un módulo estándar de Python que permite:

  • Conectarse a una base de datos SQLite
  • Ejecutar consultas SQL
  • Gestionar transacciones (commit, rollback)
  • Recibir datos como tuplas o diccionarios
  • Crear tablas, insertar, actualizar y borrar datos
import sqlite3

conn = sqlite3.connect("chinook.db")
cursor = conn.cursor()

conn es un objeto conexión análogo al que se usa en R. Representa un canal abierto entre tu programa y el archivo de base de datos.

Sirve para:

  • Iniciar y finalizar transacciones
  • Ejecutar consultas
  • Confirmar cambios (commit)
  • Revertir cambios (rollback)
  • Crear cursores
  • Cerrar la base de datos

Para ejecutar consultas se obtiene un cursor a partir de esa conexión. El cursor es uno de los conceptos más importantes cuando trabajas con cualquier base de datos desde Python. Un cursor es un objeto intermediario entre el programa y la base de datos. Se usa para:

  • enviar consultas SQL a la base de datos
  • recibir resultados de las consultas
  • iterar sobre las filas devueltas
  • ejecutar operaciones como INSERT, UPDATE, DELETE, CREATE TABLE

En otras palabras, es como una “pluma” que escribe y lee en la base de datos a través de la conexión.

A diferencia de R, Python separa estos dos conceptos:

  • conexión (conn): maneja transacciones
  • cursor: ejecuta consultas y maneja resultados

Cada cursor mantiene un “estado”

Cuando haces un SELECT, el cursor “apunta” a la posición actual dentro del conjunto de resultados.

Los cursores no necesitan cerrarse explícitamente, pero es buena práctica hacerlo:

cursor.close()

4.2.1 Operaciones de inserción, borrado o modificación de datos

  • Igual que en R, se utiliza el método execute del objeto python
  • Python inicia transacciones automáticamente en estas operaciones
  • Por legibilidad normalmente las consultas se escriben en varias líneas. Para escrbir cadenas multilínea se usa """ en lugar de ".
cursor.execute("""
CREATE TABLE IF NOT EXISTS usuarios (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    edad INTEGER
)
""")

cursor.executemany(
    "INSERT INTO usuarios (nombre, edad) VALUES (?, ?)",
    [("Luis", 25), ("María", 22), ("Pedro", 40)]
)

cursor.execute(
    "UPDATE usuarios SET edad = ? WHERE nombre = ?",
    (31, "Ana")
)

cursor.execute("DELETE FROM usuarios WHERE edad < ?", (30,))

conn.commit()
  • Para ejecutar varias sentencias a la vez, en lugar del método execute se usa executescript
cursor.executescript("""
    INSERT INTO usuarios (nombre, edad) VALUES ('Roi', 20);
    INSERT INTO usuarios (nombre, edad) VALUES ('Sara', 33);
""")
conn.commit()

4.2.2 Operaciones de consulta

Cuando se ejecuta una consulta, el cursor apunta a la primera fila de resultado. Para obtener los datos hay 3 métodos útiles:

  • fetchone(): Devuelve una fila cada vez. Cuando ya no quedan filas, devuelve None.
  • fetchall(): Devuelve todas las filas como una lista. Si la consulta devuelve muchas filas, es mejor optar por una de las otras opciones.
  • fetchmany(n): Devuelve una lista con un bloque de como máximo n filas. Si quedan menos, devuelve un bloque menor. Si no quedan filas, devuelve una lista vacía [].
  • fetchmany(): Si no se utiliza el parámetro n, se usa el tamaño por defecto del cursor (cursor.arraysize), que si no se modifica tiene valor 1.

Ejemplo iterando sobre el cursor:

cursor.execute("SELECT * FROM Artist")
filas = cursor.fetchall()

fila = cursor.fetchone()
while fila is not None:
    print(fila)
    fila = cursor.fetchone()

Ejemplo iterando sobre los resultados:

cursor.execute("SELECT * FROM Artist")
filas = cursor.fetchall()

for fila in filas:
    print(fila)

4.2.2.1 Seleccionar una fila

cursor.execute("SELECT * FROM usuarios WHERE id = ?", (1,))
fila = cursor.fetchone()
print(fila)

El valor que devuelve es de tipo tupla. Es una estructura inmutable, rápida y ligera que garantiza que los datos no se modifiquen accidentalmente. Refleja una fila tal cual la devuelve SQLite.

Se puede acceder a los campos por índices:

id = fila[0]
nombre = fila[1]
edad = fila[2]

Se puede desempaquetar la tupla:

id, nombre, edad = fila
print(nombre)

Si la consulta devuelve un conjunto de tuplas, se puede recorrer en bucle:

cursor.execute("SELECT id, nombre, edad FROM usuarios")

for fila in cursor.fetchall():
    print(f"ID={fila[0]}, Nombre={fila[1]}, Edad={fila[2]}")

Se puede convertir en lista si se necesita modificar:

fila_lista = list(fila)
fila_lista[1] = "NuevoNombre"

O en diccionario para trabajar con mayor comodidad:

columnas = [desc[0] for desc in cursor.description]
dic = dict(zip(columnas, fila))
print(dic)

4.2.2.2 Devolver resultados como diccionarios

También se puede cambiar el tipo devuelto para que sea un diccionario en lugar de una tupla y evitar la conversión explícita:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM usuarios")
for fila in cursor.fetchall():
    print(dict(fila))

De esta forma también se puede acceder a los campos de una forma más human-readable:

cursor.execute("SELECT * FROM usuarios")
for fila in cursor.fetchall():
    print(fila["nombre"])

4.2.3 Usar SQLite con Pandas

import pandas as pd

df = pd.read_sql_query("SELECT * FROM usuarios", conn)
print(df)