9.A Conectar y crear tablas¶

¿Quieres aprender SQL a fondo?

Esta unidad explica lo mínimo de SQL para entender los ejemplos. Para estudiarlo con calma (consultas, JOIN, agrupaciones, diseño de tablas, transacciones...) y con ejercicios, tienes la web de SQL y bases de datos.

Qué es una base de datos relacional¶

Una base de datos relacional guarda la información en tablas: cada tabla tiene columnas (los datos que se guardan) y filas (cada elemento guardado). Las tablas se relacionan entre sí mediante claves, y todo se maneja con un lenguaje común, SQL.

Concepto	Qué es	Ejemplo
Tabla	Un conjunto de datos del mismo tipo	`libros`
Fila (registro)	Un elemento guardado	el libro «Don Quijote»
Columna (campo)	Un dato de cada fila, con un tipo	`titulo`, `anio`, `stock`
Clave primaria (primary key)	Columna que identifica de forma única cada fila	`id`
Clave foránea (foreign key)	Columna que apunta a la clave primaria de otra tabla	`libros.id_autor` → `autores.id`

En los ejemplos de esta unidad hay tres tablas relacionadas: un autor tiene muchos libros y un libro puede tener muchos préstamos.

autores (id, nombre)  1 ──── N  libros (id, titulo, anio, stock, id_autor)  1 ──── N  prestamos (id, id_libro, socio)

Motores de bases de datos¶

Motor	Cómo funciona	Cuándo se usa
SQLite	Un archivo (o la memoria); no hay servidor ni instalación	Aprender, apps móviles y de escritorio, pruebas
H2, HSQLDB	Base de datos embebida en Java (archivo o memoria)	Pruebas y programas Java sencillos
MySQL, PostgreSQL	Servidor al que se conecta por red con usuario y contraseña	Aplicaciones reales con muchos usuarios

Qué motor usan estos ejemplos

Todos los ejemplos usan SQLite, porque no necesita instalar nada. Los ejercicios proponen H2 para Java y Kotlin: el código JDBC es el mismo, solo cambian la dependencia (com.h2database:h2) y la URL (jdbc:h2:./data/tienda). Yo he comprobado los ejemplos con SQLite, no con H2. Las sentencias también varían un poco entre motores: por ejemplo, la columna que se numera sola es AUTOINCREMENT en SQLite y AUTO_INCREMENT en MySQL y H2.

Conectar, crear las tablas e insertar datos¶

Python incluye el módulo sqlite3 en la biblioteca estándar. sqlite3.connect('biblioteca.db') abre (o crea) la base de datos en un archivo y sqlite3.connect(':memory:') la crea solo en memoria. Las sentencias se lanzan con con.execute(sql, parametros) y los cambios se confirman con con.commit(). Los errores de SQL heredan de sqlite3.Error. Cuidado: with sqlite3.connect(...) as con: no cierra la conexión (solo confirma o deshace la transacción); hay que llamar a con.close(), como hace el ejemplo con try/finally.

import os
import sqlite3


def main():
    con = sqlite3.connect("biblioteca.db")
    try:
        print("conectado a la base de datos")
        con.execute("CREATE TABLE autores (id INTEGER PRIMARY KEY AUTOINCREMENT, nombre TEXT NOT NULL)")
        con.execute("""CREATE TABLE libros (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            titulo TEXT NOT NULL,
            anio INTEGER,
            stock INTEGER NOT NULL,
            id_autor INTEGER NOT NULL REFERENCES autores(id))""")
        con.execute("""CREATE TABLE prestamos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            id_libro INTEGER NOT NULL REFERENCES libros(id),
            socio TEXT NOT NULL)""")
        print("tablas creadas")

        autores = [("Cervantes",), ("García Márquez",)]
        con.executemany("INSERT INTO autores (nombre) VALUES (?)", autores)
        print(f"autores insertados: {len(autores)}")

        libros = [
            ("Don Quijote", 1605, 3, 1),
            ("Novelas ejemplares", 1613, 2, 1),
            ("Cien años de soledad", 1967, 5, 2),
            ("El coronel no tiene quien le escriba", 1961, 1, 2),
        ]
        con.executemany("INSERT INTO libros (titulo, anio, stock, id_autor) VALUES (?, ?, ?, ?)", libros)
        print(f"libros insertados: {len(libros)}")
        con.commit()

        total = con.execute("SELECT COUNT(*) FROM libros").fetchone()[0]
        print(f"libros en la base de datos: {total}")

        try:
            con.execute("INSERT INTO editoriales (nombre) VALUES ('X')")
        except sqlite3.Error:
            print("Error controlado: la sentencia no es válida")
    finally:
        con.close()
        print("conexión cerrada")

    os.remove("biblioteca.db")
    print("archivo biblioteca.db borrado")


if __name__ == "__main__":
    main()

Salida:

conectado a la base de datos
tablas creadas
autores insertados: 2
libros insertados: 4
libros en la base de datos: 4
Error controlado: la sentencia no es válida
conexión cerrada
archivo biblioteca.db borrado

Qué hace cada paso:

Conecta con la base de datos (el archivo biblioteca.db, que se crea si no existe).
Crea las tres tablas con CREATE TABLE, indicando para cada columna su tipo y sus restricciones.
Inserta datos con INSERT y parámetros (?): los valores no se pegan dentro del texto SQL, se pasan aparte. Esto es lo que se explica en 9.C y es obligatorio cuando el dato viene del usuario.
Cuenta las filas con una consulta.
Provoca un error a propósito (insertar en una tabla que no existe) y lo captura: el programa no se detiene y muestra un mensaje claro.
Cierra la conexión (con.close() en el bloque finally) siempre, haya habido error o no. Solo después se puede borrar el archivo: con la conexión abierta, Windows no permite borrarlo.

Crear tablas: tipos y restricciones¶

CREATE TABLE libros (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,   -- identifica cada fila; se numera solo
  titulo   TEXT    NOT NULL,                    -- obligatorio
  anio     INTEGER,                             -- puede quedar vacío (NULL)
  stock    INTEGER NOT NULL,
  id_autor INTEGER NOT NULL REFERENCES autores(id)   -- clave foránea
);

Restricción	Significa
`PRIMARY KEY`	Identifica la fila; no se repite ni puede ser nula
`NOT NULL`	La columna no puede quedar vacía
`UNIQUE`	No puede haber dos filas con el mismo valor (por ejemplo, un correo)
`REFERENCES tabla(columna)`	Clave foránea: el valor debe existir en la otra tabla
`DEFAULT valor`	Valor que se usa si no se indica ninguno

Tipo en SQLite	Para qué	Equivalente habitual en otros motores
`INTEGER`	Números enteros	`INT`, `BIGINT`
`TEXT`	Texto	`VARCHAR(n)`, `TEXT`
`REAL`	Números decimales	`DOUBLE`, `FLOAT`
`BLOB`	Datos binarios	`BLOB`, `BYTEA`

Dinero y decimales

Para importes, otros motores ofrecen DECIMAL(10,2), que guarda decimales exactos. Con REAL (coma flotante) pueden aparecer errores de redondeo, como en 0.1 + 0.2. Una alternativa muy usada es guardar el importe en céntimos, como entero.

Errores frecuentes¶

Error	Cómo evitarlo
No cerrar la conexión	`finally` / `try-with-resources` / `use`
Pegar el valor dentro del SQL	Usar parámetros `?`
Olvidar confirmar los cambios (en Python, `commit`)	Confirmar al terminar o usar una transacción (9.C)
Crear una tabla que ya existe	Crear las tablas una vez, o usar `CREATE TABLE IF NOT EXISTS`
Insertar con claves foráneas que no existen	Insertar primero la tabla «padre» (los autores) y luego la «hija» (los libros)

Para practicar¶

Haz los ejercicios de U9.1 · Conexión y creación de tablas. Pasar de uno a otro compara cómo se escribe en cada lenguaje.