Bases de datos SQLite con Python

Cuando los datos de un programa crecen, guardarlos en JSON o en CSV empieza a dar problemas: para buscar un registro hay que leer el archivo entero, para cambiar uno hay que reescribirlo todo y nada impide guardar datos incoherentes. Para eso existen las bases de datos. En esta lección aprenderás a usar SQLite desde Python con el módulo sqlite3, que viene incluido: crear tablas, insertar datos de forma segura, consultarlos, modificarlos y montar un pequeño CRUD.

Qué es SQLite

SQLite es una base de datos relacional (datos en tablas con filas y columnas, que se consultan con el lenguaje SQL) que se guarda en un único archivo. A diferencia de MySQL o PostgreSQL, no necesita un servidor: tu programa abre el archivo y trabaja con él directamente.

Está en todas partes: en cada móvil Android e iPhone, en los navegadores, en muchas aplicaciones de escritorio. Es ideal para:

• Aplicaciones pequeñas o medianas con un solo usuario escribiendo a la vez.
• Prototipos y prácticas de clase.
• Guardar la configuración o los datos locales de un programa.

Esta lección se centra en cómo usar SQL desde Python. Si es la primera vez que ves SELECT o CREATE TABLE, échale antes un vistazo al curso de SQL.

Conectar con la base de datos

import sqlite3

con = sqlite3.connect(":memory:")
print(type(con).__name__)   # Connection

sqlite3.connect abre una conexión con la base de datos:

• Con un nombre de archivo, como sqlite3.connect("biblioteca.db"), los datos se guardan en ese archivo. Si no existe, se crea.
• Con ":memory:", la base de datos vive solo en memoria y desaparece al cerrar el programa. Es perfecta para ejemplos y para pruebas, y es la que usaremos en esta lección.

Crear una tabla

Las órdenes SQL se envían con execute. Vamos a crear la tabla de libros de la biblioteca de un instituto:

con.execute("""
    CREATE TABLE IF NOT EXISTS libros (
        id         INTEGER PRIMARY KEY,
        titulo     TEXT NOT NULL,
        autor      TEXT NOT NULL,
        anio       INTEGER,
        prestado   INTEGER NOT NULL DEFAULT 0
    )
""")

• INTEGER PRIMARY KEY: SQLite asigna automáticamente un número único a cada fila (1, 2, 3…).
• NOT NULL: el campo es obligatorio.
• SQLite no tiene tipo booleano: se usa INTEGER con 0 (falso) y 1 (verdadero).
• IF NOT EXISTS evita un error si la tabla ya existe, algo muy útil cuando el programa se ejecuta muchas veces sobre el mismo archivo.

Las comillas triples permiten escribir el SQL en varias líneas, mucho más legible.

Insertar datos con parámetros

Para insertar una fila con valores que vienen de variables, escribe un ? en cada sitio donde vaya un valor y pasa los valores en una tupla:

cur = con.execute(
    "INSERT INTO libros (titulo, autor, anio) VALUES (?, ?, ?)",
    ("La sombra del viento", "Carlos Ruiz Zafón", 2001),
)
print(cur.lastrowid)   # 1

execute devuelve un cursor, un objeto que representa el resultado de la orden. lastrowid dice el id que se ha asignado a la fila recién insertada.

Por qué nunca con f-strings

Podrías sentir la tentación de montar el SQL con un f-string. No lo hagas nunca. Mira qué pasa si el texto viene de un usuario malintencionado:

busqueda = "x' OR '1'='1"
sql = f"SELECT * FROM libros WHERE autor = '{busqueda}'"
print(sql)
# SELECT * FROM libros WHERE autor = 'x' OR '1'='1'

La condición '1'='1' es siempre verdadera, así que esa consulta devolvería todos los libros. Con textos más elaborados, un atacante podría leer otras tablas o borrar datos. Esto se llama inyección SQL y es uno de los fallos de seguridad más comunes de la web.

Con ?, el valor se envía aparte del SQL y la base de datos lo trata siempre como un dato, nunca como código:

filas = con.execute("SELECT * FROM libros WHERE autor = ?", (busqueda,)).fetchall()
print(filas)   # []

Cuidado: si solo hay un parámetro, sigue haciendo falta una tupla: (busqueda,), con la coma. Escribir (busqueda) es solo una cadena entre paréntesis y da error.

Parámetros con nombre

Si hay muchos valores, los ? se vuelven difíciles de seguir. Puedes usar nombres con dos puntos y pasar un diccionario:

libro = {"titulo": "Manolito Gafotas", "autor": "Elvira Lindo", "anio": 1994}
con.execute(
    "INSERT INTO libros (titulo, autor, anio) VALUES (:titulo, :autor, :anio)",
    libro,
)

Insertar muchas filas: executemany

executemany ejecuta la misma orden una vez por cada tupla de la lista:

nuevos = [
    ("El camino", "Miguel Delibes", 1950),
    ("Los santos inocentes", "Miguel Delibes", 1981),
    ("Nada", "Carmen Laforet", 1945),
    ("La casa de los espíritus", "Isabel Allende", 1982),
]
con.executemany("INSERT INTO libros (titulo, autor, anio) VALUES (?, ?, ?)", nuevos)
con.commit()

commit y rollback: confirmar los cambios

Las órdenes que modifican datos (INSERT, UPDATE, DELETE) forman parte de una transacción: un grupo de cambios que se aplican todos juntos o ninguno. Mientras no llames a con.commit(), los cambios son provisionales. Si cierras la conexión sin confirmar, se pierden.

con.rollback() hace lo contrario: deshace todo lo que no se haya confirmado todavía.

con.execute("DELETE FROM libros")        # ¡borra todo!
con.rollback()                           # ...pero lo deshacemos
total = con.execute("SELECT COUNT(*) FROM libros").fetchone()[0]
print(total)   # 6

Las transacciones son las mismas que en el curso de SQL: puedes repasarlas en transacciones.

Consultar datos con SELECT

Tras un SELECT, el cursor tiene las filas del resultado. Hay tres formas de sacarlas:

Método	Devuelve
fetchone()	La siguiente fila (una tupla) o None si no hay más
fetchall()	Una lista con todas las filas restantes
for fila in cur:	Recorre las filas una a una, sin cargarlas todas

cur = con.execute("SELECT titulo, anio FROM libros WHERE autor = ? ORDER BY anio", ("Miguel Delibes",))
print(cur.fetchone())   # ('El camino', 1950)
print(cur.fetchall())   # [('Los santos inocentes', 1981)]

for titulo, anio in con.execute("SELECT titulo, anio FROM libros WHERE anio < ?", (1960,)):
    print(f"{anio}: {titulo}")
# 1950: El camino
# 1945: Nada

Cada fila llega como una tupla, con los valores en el orden de las columnas del SELECT. Los números llegan ya como int o float: a diferencia del CSV, no hay que convertir nada.

Funciones de agregado

Todo lo que sabes de SQL funciona igual. Para un único valor, fetchone()[0]:

media = con.execute("SELECT AVG(anio) FROM libros").fetchone()[0]
print(round(media))   # 1976

row_factory: filas con nombre

Acceder por posición (fila[2]) obliga a recordar el orden de las columnas. Si asignas sqlite3.Row como fábrica de filas, cada fila se puede leer también por el nombre de la columna:

con.row_factory = sqlite3.Row

fila = con.execute("SELECT * FROM libros WHERE id = ?", (2,)).fetchone()
print(fila["titulo"], fila["anio"])   # Manolito Gafotas 1994
print(dict(fila))
# {'id': 2, 'titulo': 'Manolito Gafotas', 'autor': 'Elvira Lindo', 'anio': 1994, 'prestado': 0}

dict(fila) la convierte en un diccionario normal, muy útil si luego quieres guardarla en JSON o devolverla en una API.

UPDATE y DELETE

Se ejecutan igual, con parámetros. El atributo rowcount del cursor dice cuántas filas se han visto afectadas, y así sabes si había algo que modificar:

cur = con.execute("UPDATE libros SET prestado = 1 WHERE id = ?", (3,))
print(cur.rowcount)   # 1

cur = con.execute("DELETE FROM libros WHERE id = ?", (99,))
print(cur.rowcount)   # 0 (no existe ningún libro con id 99)
con.commit()

Cuidado: un UPDATE o un DELETE sin WHERE afecta a todas las filas. Revisa siempre la condición antes de ejecutar.

with: transacciones automáticas

Usar la conexión en un bloque with hace commit automáticamente si todo va bien, y rollback si se produce una excepción. Así no te quedas nunca con cambios a medias:

try:
    with con:
        con.execute("UPDATE libros SET prestado = 1 WHERE id = ?", (1,))
        con.execute("INSERT INTO libros (titulo) VALUES (?)", ("Sin autor",))  # falla: autor es NOT NULL
except sqlite3.IntegrityError as e:
    print("Error:", e)
# Error: NOT NULL constraint failed: libros.autor

print(con.execute("SELECT prestado FROM libros WHERE id = 1").fetchone()[0])   # 0

El segundo INSERT falla, así que el with deshace también el UPDATE anterior: el libro 1 sigue sin prestar. Esto es justo lo que quieres en operaciones como “prestar un libro y anotar el préstamo”: o se hacen las dos cosas, o ninguna.

Nota: el with de una conexión no la cierra, solo confirma o deshace. Cuando termines de usarla, llama a con.close().

IntegrityError es la excepción que lanza SQLite cuando se incumple una restricción (NOT NULL, UNIQUE, PRIMARY KEY…). Otros errores de SQL, como escribir mal el nombre de una tabla, lanzan sqlite3.OperationalError.

Un CRUD completo

CRUD son las cuatro operaciones básicas sobre datos: Create (crear), Read (leer), Update (actualizar) y Delete (borrar). Vamos a reunirlas en funciones para un pequeño gestor de socios de un gimnasio. Cada función recibe la conexión, hace una sola cosa y no sabe nada de print ni de input, así que se puede reutilizar en un programa de consola, en una web o en unos tests.

def crear_tabla(con):
    con.execute("""
        CREATE TABLE IF NOT EXISTS socios (
            id     INTEGER PRIMARY KEY,
            nombre TEXT NOT NULL,
            email  TEXT NOT NULL UNIQUE,
            cuota  REAL NOT NULL
        )
    """)

def crear_socio(con, nombre, email, cuota):
    with con:
        cur = con.execute(
            "INSERT INTO socios (nombre, email, cuota) VALUES (?, ?, ?)",
            (nombre, email, cuota),
        )
    return cur.lastrowid

def listar_socios(con):
    return con.execute("SELECT * FROM socios ORDER BY nombre").fetchall()

def cambiar_cuota(con, id_socio, cuota):
    with con:
        cur = con.execute("UPDATE socios SET cuota = ? WHERE id = ?", (cuota, id_socio))
    return cur.rowcount == 1

def borrar_socio(con, id_socio):
    with con:
        cur = con.execute("DELETE FROM socios WHERE id = ?", (id_socio,))
    return cur.rowcount == 1

Ahora lo usamos:

gym = sqlite3.connect(":memory:")
gym.row_factory = sqlite3.Row
crear_tabla(gym)

crear_socio(gym, "Lucía Gómez", "lucia@correo.es", 35.0)
crear_socio(gym, "Iván Ruiz", "ivan@correo.es", 29.9)
id_ana = crear_socio(gym, "Ana Torres", "ana@correo.es", 35.0)

print(cambiar_cuota(gym, id_ana, 25.0))   # True
print(borrar_socio(gym, 42))              # False

try:
    crear_socio(gym, "Otra Lucía", "lucia@correo.es", 30.0)
except sqlite3.IntegrityError:
    print("Ese email ya está registrado")
# Ese email ya está registrado

for s in listar_socios(gym):
    print(f"{s['id']}. {s['nombre']:<12} {s['cuota']:>6.2f} €")
# 3. Ana Torres    25.00 €
# 2. Iván Ruiz     29.90 €
# 1. Lucía Gómez   35.00 €

gym.close()

Fíjate en que la restricción UNIQUE del email la comprueba la propia base de datos: no tienes que recorrer los socios para ver si está repetido.

Consejo: para inspeccionar visualmente un archivo .db, instala el programa gratuito DB Browser for SQLite. Verás las tablas, los datos y podrás lanzar consultas a mano.

Un archivo real

Todo lo anterior funciona igual con un archivo en disco. El patrón típico de un programa es:

con = sqlite3.connect("biblioteca.db")
con.row_factory = sqlite3.Row
crear_tabla(con)
# ... trabajar con los datos ...
con.close()

La primera vez se crea biblioteca.db; las siguientes, se abre con los datos que tuviera. Como crear_tabla usa IF NOT EXISTS, se puede llamar siempre al arrancar.

Errores frecuentes

• Olvidar commit(): el programa parece funcionar, pero al abrirlo otra vez los datos no están. Usa with con: y no te olvidarás.
• Montar el SQL con f-strings o +: abre la puerta a la inyección SQL. Usa siempre ? o :nombre.
• Pasar un único parámetro sin coma: (valor) no es una tupla; escribe (valor,).
• fetchone()[0] cuando no hay resultados: fetchone() devuelve None y None[0] da TypeError. Comprueba antes si es None.
• Pensar que with con: cierra la conexión: solo gestiona la transacción; ciérrala con close().
• UPDATE o DELETE sin WHERE: modifica o borra toda la tabla.
• OperationalError: no such table: la tabla no existe en esa base de datos; revisa el nombre del archivo o crea la tabla al arrancar.

Resumen

Tarea	Código
Conectar	con = sqlite3.connect("archivo.db") o ":memory:"
Ejecutar SQL	cur = con.execute(sql, parametros)
Parámetros seguros	? con tupla, o :nombre con diccionario
Insertar muchas filas	con.executemany(sql, lista_de_tuplas)
Id de la fila insertada	cur.lastrowid
Filas afectadas	cur.rowcount
Leer resultados	fetchone(), fetchall() o for fila in cur
Filas por nombre	con.row_factory = sqlite3.Row
Confirmar o deshacer	con.commit(), con.rollback()
Transacción automática	with con:
Cerrar	con.close()

Con SQLite tus programas ya pueden guardar datos de forma ordenada y segura. En la siguiente lección aprenderás a organizar datos y comportamiento juntos con clases y objetos.

sql = f"SELECT * FROM libros WHERE autor = '{busqueda}'"