Explorando el Funcionamiento de la Base de Datos SQLite3 en Python

En el mundo de la programación, el manejo de datos es una parte esencial de muchas aplicaciones. Ya sea que estemos creando una aplicación web, una aplicación de escritorio o incluso una aplicación móvil, es probable que necesitemos almacenar y recuperar información de manera eficiente. Una de las herramientas más populares para manejar bases de datos en Python es SQLite. En este artículo, exploraremos en detalle el funcionamiento de la base de datos SQLite en el entorno de programación de Python.

¿Qué es SQLite?

SQLite es una biblioteca escrita en lenguaje C que proporciona un sistema de gestión de bases de datos relacionales de código abierto y sin servidor. A diferencia de otras bases de datos más robustas, SQLite es una base de datos de un solo archivo, lo que significa que toda la base de datos y el motor de gestión están contenidos en un solo archivo. Esto lo hace extremadamente ligero y fácil de integrar en aplicaciones Python sin la necesidad de configuraciones complejas.

Ventajas de SQLite en Python:

1. Sin configuración compleja: SQLite no requiere configuraciones de servidor complicadas. La base de datos se maneja como un archivo local, lo que facilita su gestión y distribución junto con la aplicación.

2. Eficiencia: Aunque SQLite es una base de datos de un solo archivo, ofrece un rendimiento sorprendentemente rápido para muchas aplicaciones. Es ideal para proyectos más pequeños o aplicaciones que no requieren una escalabilidad extrema.

3. Compatibilidad: Python tiene una integración incorporada con SQLite, lo que significa que no es necesario instalar bibliotecas adicionales. La mayoría de las distribuciones de Python vienen con la biblioteca SQLite lista para usar.

4. Transacciones ACID: SQLite garantiza la integridad de los datos utilizando el concepto de transacciones ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad), lo que garantiza que las operaciones sean seguras y confiables.

Trabajando con SQLite en Python:

Esquema de funcionamiento:

1.- Importar la biblioteca.

2.- Abrir o Crear la conexión.

3.- Crear un cursor o puntero.

4.- Ejecutar una consulta o query SQL.

5.- Manejar los resultados de la query o consulta. CRUD (create, read, update and delete)

6.- Cerrar la conexión y el puntero.

En una base de datos de este tipo los datos se guardan en tablas. Imaginemos que tenemos una pequeña biblioteca y queremos llevar un registro de los libros que tenemos. En este caso nuestra entidad, el libro, tiene un montón de atributos que queremos guardar. (Titulo, Año, Precio, Descripción). Pues bien nuestra tabla de libros tendrá una serie de filas y columnas. En las columnas estarán los atributos de cada libro y en las filas guardaremos cada registro, es decir los atributos de cada libro.

A continuación, se presentan los pasos básicos para trabajar con la base de datos SQLite en Python:

1. Importar la biblioteca: Comenzamos importando el módulo 'sqlite3', que proporciona las funciones y clases necesarias para interactuar con la base de datos. Como hemos dicho antes forma parte de la biblioteca estándar de Python por lo que no hay que instalar ningún módulo con con 'pip'.

import sqlite3

2. Conectar a la base de datos: Usamos la función 'connect()' para establecer una conexión con la base de datos o crear una nueva si no existe.

conexion = sqlite3.connect('mi_base.db')

La primera vez que ejecutemos el programa, como la base de datos no existe la creará. Lo que veremos será un archivo llamado 'mi_base.db' que estará en el mismo directorio que la aplicación.

A partir de aquí vamos a usar el lenguaje SQL que es un lenguaje que se usa para manejar bases de datos. Más adelante lo veremos en mucho más detalle como funciona pero ahora mismo lo que pretendemos es ver una visión general de como usar sqlite con python.

3. Crear una tabla: Podemos ejecutar comandos SQL para crear tablas en la base de datos.

Podemos ejecutar instrucciones SQL directamente utilizando el objeto 'conexion' sin necesidad de usar un cursor ('c' en el ejemplo siguiente). Cuando ejecutamos instrucciones SQL a través del objeto de conexión, SQLITE automáticamente crea un cursor en segundo plano para ejecutar la consulta. Sin embargo es una buena práctica crear un cursos explícitamente para tener un mayor control sobre las operaciones y para manejar múltiples consultas en una transacción.

try:
    c.execute('''CREATE TABLE libros (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        título TEXT,
        año INTEGER,
        precio FLOAT,
        descripción TEXT);''')
    print("Tabla de libros creada con éxito")
    
except sqlite3.OperationalError as error:
    print(error)

Antes de seguir vamos a comentar brevemente que es un campo 'Primary Key'. Se trata de un elemento que sirve para identificar cada registro. Tienen una seria de características:

- Ningún registro puede tener la misma 'Primary Key' o clave primaria.

- La 'Primary Key' nunca se vuelve a reutilizar.

- Una 'Primary Key' no se puede modificar una vez creada.

- Una 'Primary Key' debe tener siempre un valor, no puede ser un valor nulo 'NULL'.

En el ejemplo de arriba creamos un campo llamado id, que será un número entero único para cada registro, en el que si nosotros no lo introducimos, automáticamente el programa lo hará por nosotros y además, esos números los irá incrementando automáticamente.

Visto lo anterior continuamos.

Creamos un try/except ya que la primera vez que se ejecute la instrucción se creará la tabla, pero la segunda nos mostrará un error porque ya está creada. Si no queremos usar el gestor de excepciones también podemos usar esta sentencia:

c.execute('''CREATE TABLE IF NOT EXISTS libros (
       id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
       título TEXT,
       año INTEGER,
       precio FLOAT,
       descripción TEXT);''') 

Nota: Aunque en sqlite3 en Python no es necesario, ya que solo se puede ejecutar una instrucción por cada "execute" normalmente todas las instrucciones SQL terminan con un ; porque en SQL si que se pueden ejecutar varias instrucciones simultáneamente en una instrucción.

Trabajando con Registros (CRUD)

- Crear o Insertar datos: Usamos el comando INSERT para agregar datos a la base de datos.

La forma general sería:

INSERT INTO tabla VALUES (valor_campo1, valor_campo2,...,valor_campo_n);

Lo tenemos que hacer en el mismo orden que estén creados los campos. Además tenemos que introducir tantos valores como campos tengamos. Si no queremos especificar un determinado valor deberemos poner Null en el valor de ese campo.

Si quisiéramos introducir los datos en otro orden distinto al que creamos los campos podemos hacerlos de la siguiente forma:

INSERT INTO tabla(campo_n,...,campo2, campo1) VALUES (valor_campo_n,...,valor_Campo2, valor_campo1);

En nuestro ejemplo el código de Python para agregar un valor a la base de datos sería:

c.execute("INSERT INTO libros VALUES (NULL,'El Final', 1998, 15.50, 'libro de ficción');")

Introducimos los campos en el mismo orden en los que lo creamos en la tabla. He puesto NULL en el id porque al crear la tabla, yo no quiero crear el id y será el programa el que lo introduzca por mi automáticamente.

Otra forma, sobre todo si se quiere pasar listas con datos a través de un bucle 'For' o una sentencia 'executemany', sería:

c.execute("INSERT INTO libros (titulo, año, precio, descripción) VALUES (?,?,?,?)", ('El final', 1998, 15.50, 'libro de ficción'))

Si utilizamos 'executemany' entonces para representar los valores nulos se utiliza None en lugar de Null

# Insertar varios registros.
    
set_data = [
        (None, 'El ocho', 1987, 23.50, 'libro de ficción'),
        (None, 'Historia del Arte', 2023, 60, 'libro didáctico'),
        (None,'Programar en Python', 2010, 25, 'libro de Informática'),
        ]

c.executemany("INSERT INTO libros VALUES (?,?,?,?,?);", set_data)

- Recuperar datos: Podemos ejecutar consultas SELECT para recuperar información de la base de datos.

# Recuperar todos los datos
c.execute("SELECT * FROM libros;")
for fila in c:
    print(fila)

La salida sería:

(1, 'El Final', 1998, 15.5, 'libro de ficción')

(2, 'El ocho', 1987, 23.5, 'libro de ficcion')

(3, 'Historia del Arte', 2023, 60.0, 'libro didáctico')

(4, 'Programar en Python', 2010, 25.0, 'libro de Informática')

Podemos tambien utilizar los métodos del cursor: fetchall(), fetchmany(número) y fetchone() para recuperar todos, algunos o uno solo de los registros. Por ejemplo para recuperar todos los registros sería:

# Recuperar todos los datos
c.execute("SELECT * FROM libros;")
datos = c.fetchall()
print(datos)

SALIDA:
[(1, 'El Final', 1998, 15.5, 'libro de ficción'), 
(2, 'El ocho', 1987, 23.5, 'libro de ficción'), 
(3, 'Historia del Arte', 2023, 60.0, 'libro didáctico'), 
(4, 'Programar en Python', 2010, 25.0, 'libro de Informática')]

- Actualizar un Registro. Para actualizar un registro ya existente en la base de datos usamos el comando UPDATE.

# Actualizar un registro
UPDATE tabla SET campo=valor, campo=valor WHERE campo_id=n

En el ejemplo de la librería.

# Actualizar un registro
c.execute("UPDATE libros SET título='otro libro', precio=18 WHERE id=2";)

- Borrar un Registro.  La instrucción DELETE se usa para borrar un registro de una tabla.

# Borrar un registro
DELETE FROM nombre_tabla WHERE condición; 

NOTA IMPORTANTE: ¡Mucho cuidado cuando borres registros en una tabla!. Ten en cuenta la clausula WHERE en la instrucción DELETE. La clausula WHERE especifica que registro debe ser borrado. SI la omites, todos los registros de la tabla se borrarán.

Vamos ahora a borrar el último registro:

# borrar un registro
c.execute("DELETE FROM libros WHERE id=4") 

Si lo que queremos es borrar la tabla y no solo los registros, por ejemplo para empezar de nuevo usaremos la instrucción:

# borrar una tabla
c.execute("DROP TABLE nombre_tabla") 

Después de ver las operaciones básicas CRUD, vamos a ver dos cosas más.

Modificar una tabla para añadir un nuevo campo.

Usamos la instrucción:

# Añadir un nuevo campo a la tabla
ALTER TABLE nombre_tabla ADD nuevo_campo tipo_de_dato;

Por ejemplo si queremos añadir un nuevo campo para poner nuestra opinión sobre los libros (un campo booleano con 0 si no nos ha gustado y 1 si nos ha gustado) haríamos los siguiente:

# Añadir un nuevo campo a la tabla
c.execute("ALTER TABLE libros ADD opinion BOOLEAN")

Todos los registros que ya estuvieran creados de antes aparecerán con el valor NULL para esta columna.

Crear una nueva tabla en la base de datos. (suponemos que ya hay una creada)

Por ejemplo vamos a poner una tabla llamada autor, que tenga los campos nombre, apellidos y editorial.

# Añadir una nueva tabla
    c.execute('''
        CREATE TABLE IF NOT EXISTS autor(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        nombre TEXT,
        apellidos TEXT,
        editorial TEXT)
''')

4. Confirmar y cerrar: Si no estamos en un entorno contextual (estamos utilizando el with) después de realizar las operaciones necesarias, confirmamos los cambios y cerramos la conexión.

conexion.commit()
c.close()
conexion.close()

¡IMPORTANTE! Utilizar el contexto with en combinación con sqlite3.connect es una excelente práctica para manejar la conexión a la base de datos de manera más segura y eficiente. El bloque with se encargará de cerrar automáticamente la conexión y el puntero una vez que el bloque se haya ejecutado, independientemente de si hay excepciones o no.

Con esta estructura, la conexión se cerrará automáticamente al finalizar el bloque 'with', y no necesitas llamar a conexion.commit() explícitamente, ya que SQLite realizará las confirmaciones automáticamente cuando se cierre la conexión. Esta es una forma segura y práctica de trabajar con bases de datos en Python.

Aquí tienes cómo se vería el código utilizando el contexto with:

import sqlite3

# Creamos la conexión

with sqlite3.connect('mi_base.db') as conexion:

    # Creamos el cursor

    c = conexion.cursor()

    c.execute('''CREATE TABLE IF NOT EXISTS libros (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            título TEXT,
            año INTEGER,
            precio FLOAT,
            descripción TEXT);''')
        
    # Insertar un registro
    
    c.execute("INSERT INTO libros VALUES (NULL,'El Final', 1998, 15.50, 'libro de ficción');")
    
    # Insertar varios registros.
    
    set_data = [
        (None, 'El ocho', 1987, 23.50, 'libro de ficcion'),
        (None, 'Historia del Arte', 2023, 60, 'libro didáctico'),
        (None,'Programar en Python', 2010, 25, 'libro de Informática'),
        ]
    
    c.executemany("INSERT INTO libros VALUES (?,?,?,?,?);", set_data)

    # Recuperar todos los datos
    
    c.execute("SELECT * FROM libros;")
    datos = c.fetchall()
    print(datos)
    
    # Actualizar un registro.
    c.execute("UPDATE libros SET título='El ocho', precio=18 WHERE id=49")
    
    # Borrar un registro
    c.execute("DELETE FROM libros WHERE id=4")    
    
    # Añadir un nuevo campo a la tabla
    c.execute("ALTER TABLE libros ADD opinion BOOLEAN")
    
    # Añadir una nueva tabla
    c.execute('''
        CREATE TABLE IF NOT EXISTS autor(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        nombre TEXT,
        apellidos TEXT,
        editoria TEXT)
''')

Ahora que hemos visto lo básico vamos a profundizar más en las instrucciones SQL en si. Para trabajar más cómodamente vamos a necesitar un programa para ver las bases de datos creadas.

1.- En local podemos usar DB Browser for sqlite --> https://sqlitebrowser.org/

2.- Si preferimos trabajar online podemos ir a https://sqliteonline.com/

3.- Para diseñar las bases de datos de forma gráfica podemos utlizar https://drawsql.app/

Los elementos fundamentales de una base de datos son:

Tabla: Una tabla es una estructura de datos fundamental en una base de datos relacional como SQLite. Representa una colección organizada de datos relacionados. Puedes pensar en una tabla como una hoja de cálculo en la que los datos se almacenan en filas y columnas. Cada tabla tiene un nombre único y define la estructura de los datos que contendrá, incluyendo el nombre y el tipo de cada columna.

Columna: Una columna es una parte de una tabla que define el tipo de datos que se almacenan en ella. Cada columna tiene un nombre único y un tipo de datos asociado que especifica el tipo de valores que puede contener. Por ejemplo, una columna podría almacenar números enteros, cadenas de texto, fechas, etc. Las columnas permiten organizar y categorizar los datos en la tabla.

Fila: Una fila, a veces llamada registro o tupla, es una entrada individual en una tabla. Representa un conjunto completo de datos que pertenecen a un único elemento o entidad en el mundo real. Cada fila contiene valores para cada una de las columnas definidas en la tabla. En otras palabras, una fila es una instancia específica de los datos que se almacenan en la tabla.

SQLite admite varios tipos de datos que puedes utilizar en una base de datos en Python. Aquí tienes una lista de los tipos de datos más comunes que puedes emplear en SQLite.

TIPOS DE DATOS.

INTEGER vs. INT: Almacena números enteros. Puedes especificar la longitud, por ejemplo, INTEGER(4) o INT(4) para un entero de 4 bytes. 

REAL vs FLOAT: Almacena números de punto flotante, como números decimales. (FLOAT)

TEXT vs VARCHAR:  En SQLite, puedes utilizar TEXT para almacenar cadenas de texto. La diferencia principal entre TEXT y VARCHAR es que VARCHAR permite especificar una longitud máxima para la cadena, mientras que TEXT no impone una restricción de longitud. Sin embargo, en SQLite, incluso si declaras una columna como VARCHAR, SQLite tratará la columna de manera similar a TEXT, ya que no impone restricciones de longitud máxima de manera estricta. La mayoría de las bases de datos SQLite tratan VARCHAR y TEXT de manera intercambiable. 

BLOB: Almacena datos binarios, como imágenes o archivos.

NUMERIC: Puede almacenar cualquier valor numérico, incluidos enteros y decimales. SQLite intentará convertir automáticamente los valores en el formato adecuado.

BOOLEAN: Aunque SQLite no tiene un tipo BOOLEAN específico, generalmente se usa INTEGER o NUMERIC con valores 0 y 1 para representar valores booleanos (falso y verdadero).

DATE: Puedes almacenar fechas en formato 'YYYY-MM-DD'.

TIME: Para almacenar horas en formato 'HH:MM:SS'.

DATETIME: Combina fecha y hora en un solo campo, en formato 'YYYY-MM-DD HH:MM:SS'.

TIMESTAMP: Se utiliza para almacenar la marca de tiempo (timestamp) de cuándo se insertó o actualizó un registro en una tabla.

Como ejemplo vamos a utilizar esta base de datos que recrea algunos datos de los mundiales de futbol en la que se han definido las siguientes tablas:

- teams: representa las diferentes selecciones que han participado.

- countries: los distintos paises que existen y que están representados por su selección.

- events: los diferentes mundiales que se han disputado.

- events_teams: los mundiales en los que ha participado cada equipo.

- games: Partidos, que equipos jugaron y el resultado.

Aunque ahora no lo entiendas muy bien el esquema de esta base de datos es el siguiente:

Seria conveniente que vieras el archivo sql1.py que es que he utilizado para crear la base de datos. En el se ve como conectar a la base de datos, la creación de las tablas y la inserción de datos de diferentes formas.  En el archivo sql2.py encontrarás diversas búsquedas hechas sobre esa base de datos.

A la hora de crear las tablas se pueden especificar ciertas restricciones a los campos y algunas reglas. 

Siempre cada registro de una tabla tiene que tener un identificador único o id al que se denomina, como ya hemos comentado antes, PRIMARY KEY

Por ejemplo en la tabla countries, que recoge algunos países que han participado en un mundial, la instrucción SQL para SQLITE3 de Python que la crea es la siguiente:

sql1.py: creación de la base de datos. primary_key

 c.execute('''
            CREATE TABLE IF NOT EXISTS countries (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            name VARCHAR(255) NOT Null            
            )''')

Como vemos hemos creado dos campos, el campo "id" y el campo "name". Le hemos especificado a la base de datos que el campo id será un número entero que será el "primary_key" de cada registro. Si nosotros no lo especificamos se creará automáticamente, no podrá ser un valor nulo y al ser un primary_key será único y no se podrá repetir. 

También puede ocurrir que un campo de una tabla, haga referencia a otro campo de otra tabla, es lo que se conoce como foreign_key. Por ejemplo en la tabla "games" los campos team1_id y team2_id hacen referencia a registros que están en la tabla "teams" y events_id hace referencia al id del mundial en el que se jugo ese partido y en el que participaron esos equipos y esta en la tabla "events".

sql1.py: creación de la base de datos. foreign_key

c.execute('''
            CREATE TABLE IF NOT EXISTS games (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            event_id INTEGER NOT Null,
            round VARCHAR(255) NOT NULL,
            team1_id INTEGER NOT Null,
            team2_id INTEGER NOT Null,
            play_at DATETIME NOT Null,
            score1 INTEGER,
            score2 INTEGER,
            winner INTEGER,
            FOREIGN KEY(event_id) REFERENCES events(id),
            FOREIGN KEY(team1_id) REFERENCES teams(id),
            FOREIGN KEY(team2_id) REFERENCES teams(id))''')

Las claves externas o FOREIGN KEY se añaden al final de la instrucción una vez definidos los campos.

    ELEMENTOS DEL LENGUAJE SQL.

- Mostrar un elemento.

La estructura de la instrucción SQL para mostrar un elemento es:

   SELECT campo FROM tabla;

Nos mostrará el campo seleccionado de la tabla que le hayamos indicado. 

Si sustituimos campo por * un asterisco nos mostrará todos los campos de esa tabla.

En la base de datos de ejemplo si queremos mostrar el "title", el nombre de los países, de la tabla "teams" equipos:

Si queremos mostrar todos los campos de esa tabla utilizamos el wildcard *.

Podemos usar también una serie de condicionantes para el registro.

Por ejemplo para que nos muestra en esta tabla "teams" que registro o registros tienen un valor del campo "title" igual a Spain.

   SELECT * FROM teams WHERE title='Spain';

Como el id de Spain en la tabla "teams" es 129 podemos contar las veces que en esta base de datos España ha jugado un partido en el mundial. 

 SELECT count(*) FROM games WHERE team1_id=129 or team2_id=129;

En este caso han sido en 11 ocasiones, es decir hay 11 registro en el que se cumple la condición que hemos especificado. 

COUNT(*) -> devuelve el número de registros que cumplen la condición.

Podemos ordenar la forma en la que se muestran los registros con la clausula ORDER BY. Por ejemplo en la tabla "teams" se nos muestren los registros ordenados por "title".

SELECT id, title FROM teams ORDER BY title;

Lo mismo pero en orden descendente:

SELECT id, title FROM teams ORDER BY title DESC;

También en vez del nombre del campo o columna se puede poner un número que será el que le corresponda según la posición que ocupe en la tabla. Lo mismo que antes se puede conseguir con esta instrucción:

SELECT id, title FROM teams ORDER BY 3 DESC;

donde 3 es el orden del campo "title" en la tabla "teams".

Operadores que se pueden utilizar con la clausula WHERE.

=	igual
>	mayor que
>=	mayor o igual que
!>	no más grande que
<	menos que
<=	menor o igual que
!<	no menor que
<>	no igual que
!=	no igual que
BETWEEN	entre los valores especificados incluidos los mismos
IS NULL	si es un missing value

Un ejemplo podría ser:

SELECT campo, campo, campo FROM tabla WHERE campo<>1;

Filtros Avanzados AND, OR o IN.

Se pueden anidar sentencias usando el operador AND como en los siguientes ejemplos. (AND = y )

SELECT campo, campo, campo FROM tabla WHERE campo>2 AND campo>3;

Que se cumpla una condición u otra con OR:

SELECT campo, campo, campo FROM tabla WHERE campo>2 OR campo>3;

Se pueden utilizar paréntesis para agrupar opciones:

SELECT campo, campo, campo FROM tabla WHERE (campo=1 OR campo=2) AND campo>=2;

La clausula "IN" en SQL se utiliza para filtrar filas en una consulta en función de una lista de valores especificados. Básicamente, nos permite seleccionar filas cuyos valores en una columna coincidan con uno o más valores en una lista. Es útil cuando queremos recuperar registros que cumplan con múltiples condiciones en una sola consulta .

SELECT columnas FROM tabla WHERE columna IN (valor1, valor2, valor3...);

WILD CARDS.

Las "WILD CARDS" o comodines en SQL son caracteres especiales que se utilizan en las consultas para buscar patrones de texto en una columna de una tabla. Los comodines más comunes en SQL son el asterisco '*', el signo de '%' y el guion bajo '_'.

1.- Asterisco '*' El asterisco se utiliza para seleccionar todas las columnas de una tabla en una consulta. Esto ya lo hemos visto anteriormente en múltiples casos:

 SELECT * FROM teams WHERE title='Spain';

2.- Porcentaje '%': El signo de porcentaje se usa como comodín en combinación con la clausula 'LIKE' para buscar patrones de texto en una columna. Por ejemplo:

SELECT name FROM countries WHERE name LIKE 'F%';

Nos devuelve el nombre de todos los países de la tabla 'countries' que empiecen por la letra 'F'

O por ejemplo:

'%texto': encuentra los registros que termine con texto.

'texto%': encuentra los registros que comiencen con texto.

'%texto%': encuentra los registros que contengan la palabra 'texto' en cualquier parte.

3.- Guiones Bajos (_): El guion bajo _ se utiliza como comodín junto con la clausula LIKE para buscar patrones de texto, pero en lugar de representar múltiples caracteres, representa un único carácter. Por ejemplo:

SELECT name FROM countries WHERE name LIKE '_o%';

Esta consulta selecciona todos los nombres de países donde el segundo carácter es una 'o' y dando igual el texto que haya después.

Foreign Keys.

FOREIGN KEY(nombre_campo) REFERENCES nombre_tabla(nombre_campo)

Se definen al final de la instrucción SQL que crea esa tabla.

Cada tabla debería tener una Primary Key que es única para cada registro de la tabla. Las Foreigns Keys son como las bases de datos, representan las relaciones entre las tablas. Mientras que una tabla solo tiene una Primary Key, puede tener múltiples Foreign Keys, una por cada tabla con la que tenga relación.

Tipos de relaciones.

One to Many. Un registro en una tabla puede relacionarse con uno o más registros en otra tabla. Esto se puede implementar utilizando una clave foránea en la tabla "many" que hace referencia a la clave principal en la tabla "one". El ejemplo tipico es que un autor puede haber escrito varios libros pero un libro solo puede tener un autor.

import sqlite3

# Conectarse a la base de datos SQLite
conn = sqlite3.connect('biblioteca.db')
cursor = conn.cursor()

# Crear la tabla de Autores
cursor.execute('''CREATE TABLE autores (
                    id INTEGER PRIMARY KEY,
                    nombre TEXT)''')

# Crear la tabla de Libros
cursor.execute('''CREATE TABLE libros (
                    id INTEGER PRIMARY KEY,
                    titulo TEXT,
                    autor_id INTEGER,
                    FOREIGN KEY (autor_id) REFERENCES autores(id))''')

# Insertar datos
cursor.execute("INSERT INTO autores (nombre) VALUES ('J.K. Rowling')")
cursor.execute("INSERT INTO libros (titulo, autor_id) VALUES ('Harry Potter y la piedra filosofal', 1)")
cursor.execute("INSERT INTO libros (titulo, autor_id) VALUES ('Harry Potter y la cámara secreta', 1)")

# Consulta para obtener todos los libros de un autor específico (por ejemplo, J.K. Rowling)
cursor.execute("SELECT libros.titulo FROM libros JOIN autores ON libros.autor_id = autores.id WHERE autores.nombre = 'J.K. Rowling'")
libros_jk_rowling = cursor.fetchall()
print("Libros de J.K. Rowling:")
for libro in libros_jk_rowling:
    print(libro[0])

# Cerrar la conexión
conn.commit()
conn.close()

One to one. Un registro en una tabla solo puede estar relacionado con un registro en otra. Esto se consigue generalmente mediante la inclusión de una clave foránea en una de las tablas. En el ejemplo del futbol un país solo puede tener un equipo que lo represente un equipo nacional puede pertenecer a un solo paí.

# tabla countries
c.execute('''
            CREATE TABLE IF NOT EXISTS countries (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            name VARCHAR(255) NOT Null            
            )''')

# tabla teams
c.execute('''
            CREATE TABLE IF NOT EXISTS teams (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            key VARCHAR(255) NOT Null,
            title VARCHAR(255) NOT Null,
            country_id INTEGER NOT Null,
            FOREIGN KEY(country_id) REFERENCES countries (id))''')

Otro ejemplo aquí.

Many to many. Uno o más registros pueden relacionarse con uno o mas registros en otra tabla.

Esto se logra creando una tabla intermedia que conecta las dos tablas. En nuestro ejemplo del futbol un equipo (teams) puede participar en varios mundiales (events) y en un mundial pueden participar varios equipos.

# tabla events
c.execute('''
            CREATE TABLE IF NOT EXISTS events (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            key VARCHAR(255) NOT Null,
            start_at DATE NOT Null,
            end_at DATE)''')

# tabla temas
c.execute('''
            CREATE TABLE IF NOT EXISTS teams (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            key VARCHAR(255) NOT Null,
            title VARCHAR(255) NOT Null,
            country_id INTEGER NOT Null,
            FOREIGN KEY(country_id) REFERENCES countries (id))''')

# tabla many to many events_teams
c.execute('''
            CREATE TABLE IF NOT EXISTS events_teams (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT Null,
            event_id INTEGER NOT Null,
            team_id INTEGER NOT Null,
            FOREIGN KEY(event_id) REFERENCES events (id),
            FOREIGN KEY(team_id) REFERENCES teams (id))''')

Otro ejemplo aquí.

Seleccionando datos en múltiples tablas.

Una vez que hemos visto como se relacionan las tablas en una base de datos, vamos a ver como combinar datos de dos o más tablas en función de una columna común.

Supongamos que tienes dos tablas en tu base de datos: "clientes" y "pedidos", y ambas tienen una columna llamada "cliente_id" que se utiliza para relacionar los registros. Vamos a crear las tablas y poner algunos datos.

import sqlite3

# Conectarse a la base de datos o crearla si no existe
conn = sqlite3.connect("mi_base_de_datos.db")
cursor = conn.cursor()

# Crear la tabla "clientes"
cursor.execute("""
    CREATE TABLE IF NOT EXISTS clientes (
        cliente_id INTEGER PRIMARY KEY,
        nombre TEXT,
        email TEXT
    );
""")

# Crear la tabla "pedidos"
cursor.execute("""
    CREATE TABLE IF NOT EXISTS pedidos (
        pedido_id INTEGER PRIMARY KEY,
        cliente_id INTEGER,
        producto TEXT,
        cantidad INTEGER,
        FOREIGN KEY (cliente_id) REFERENCES clientes (cliente_id)
    );
""")

# Confirmar los cambios y cerrar la conexión
conn.commit()
conn.close()

Ahora vamos a ver como mostrar los nombres de los clientes y los productos de los pedidos que tienen coincidencias en ambas tablas.  Para ello utilizaremos la sentencia INNER JOIN que devuelve solo los registros que tienen coincidencias en ambas tablas.

Ejemplo de INNER JOIN.

import sqlite3

# Conectarse a la base de datos
conn = sqlite3.connect("mi_base_de_datos.db")
cursor = conn.cursor()

# Consulta SQL utilizando INNER JOIN
query = """
SELECT clientes.nombre, pedidos.producto
FROM clientes
INNER JOIN pedidos ON clientes.cliente_id = pedidos.cliente_id;
"""

# Ejecutar la consulta y obtener resultados
cursor.execute(query)
resultados = cursor.fetchall()

# Imprimir resultados
for resultado in resultados:
    print(resultado)

# Cerrar la conexión
conn.close()

El resultado sería algo como esto:

antonio	| alicates
antonio	| tenazas
ana	| llave inglesa

Vamos a ver como funciona:

• 'SELECT clientes.nombre, pedidos.producto': Esta parte de la consulta especifica las columnas que deseas seleccionar en el resultado. En este caso, estás seleccionando las columnas "nombre" de la tabla "clientes" y "producto" de la tabla "pedidos".

• 'FROM clientes': Aquí se indica la tabla principal de la que deseas seleccionar datos. En este caso, estás seleccionando datos de la tabla "clientes".

• 'INNER JOIN pedidos ON clientes.cliente_id = pedidos.cliente_id': Esta es la parte clave de la consulta que realiza la combinación entre las dos tablas. Aquí se está utilizando INNER JOIN para combinar registros de las tablas "clientes" y "pedidos" en función de una condición. La condición de unión es clientes.cliente_id = pedidos.cliente_id, lo que significa que los registros se combinarán cuando el valor de la columna "cliente_id" en la tabla "clientes" sea igual al valor de la columna "cliente_id" en la tabla "pedidos".

Cuando se ejecuta esta consulta, obtendrás un conjunto de resultados que incluirá el nombre de los clientes y los productos de los pedidos que coinciden en la columna "cliente_id". En otras palabras, esta consulta te dará una lista de clientes y los productos que han pedido.

Esencialmente, INNER JOIN se utiliza para seleccionar datos de dos o más tablas relacionadas únicamente cuando hay coincidencias en las columnas especificadas en la condición de unión. Si no hay coincidencias, esos registros no aparecerán en el resultado.

Ejemplo de LEFT JOIN.

Devuelve todos los registros de la tabla1 (clientes) y los registros coincidentes de la tabla2 (pedidos), si los hay. Si no los hay los datos de la tabla2(pedidos) serán NULL. He puesto un cliente nuevo que aun no tiene pedidos y he ejecutado la instrucción.

import sqlite3

# Conectarse a la base de datos
conn = sqlite3.connect("mi_base_de_datos.db")
cursor = conn.cursor()

# Consulta SQL utilizando LEFT JOIN
query = """
SELECT clientes.nombre, pedidos.producto
FROM clientes
LEFT JOIN pedidos ON clientes.cliente_id = pedidos.cliente_id;
"""

# Ejecutar la consulta y obtener resultados
cursor.execute(query)
resultados = cursor.fetchall()

# Imprimir resultados
for resultado in resultados:
    print(resultado)

# Cerrar la conexión
conn.close()

En este caso la salida es:

ana	| llave inglesa
antonio	| alicates
antonio	| tenazas
teresa	| NULL

Existen más instrucciones pero no vamos a entrar de momento en ellas.

OPERACIONES CON BASES DE DATOS.

 - CONTAR el numero de filas o registros.

SELECT count(*) FROM Nombre_Tabla;

Para que la salida no aparezca count(*) sino algo más legible podemos poner un alias usando la clave AS.

SELECT count(*) AS Numero_Registos FROM Nombre_Tabla;

También podemos utilizar la clausula WHERE para filtrar los resultados.

SELECT count(*) AS Numero_Registos FROM Nombre_Tabla WHERE condición;

- SUMAR una columna.

SELECT SUM(columna) AS Valor_Total FROM Nombre_Tabla WHERE condición;

- MEDIA de una Columna. 

SELECT AVG(columna) AS Valor_Total FROM Nombre_Tabla WHERE condición;

- AGRUPAR datos.

Agrupa registros o filas basándose en una determinada columna.

SELECT columna_1, count(*) FROM Nombre_Tabla GROUP BY columna_1;

CHULETA de declaraciones SQL básicas.

Declaración	Descripción	Código de Ejemplo
SELECT	Recupera datos de una o más tablas basadas en columnas especificadas.	SELECT column1, column2 FROM table_name;
FROM	Especifica la tabla de la que se obtienen los datos recuperados.	SELECT column1, column2 FROM table_name;
WHERE	Filtra los datos según las condiciones especificadas.	SELECT column1, column2 FROM table_name WHERE condition;
ORDER BY	Ordena el conjunto de resultados según la columna especificada en orden ascendente (ASC) o descendente (DESC).	SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
GROUP BY	Agrupa las filas o registros en base a la columna especificada.	SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
HAVING	Filtra datos agrupados según lo especificado en las condiciones.	SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;
INSERT INTO	Inserta datos en una tabla.	INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE	Modifica o actualiza los datos en una tabla en base a lo especificado en las condiciones.	UPDATE table_name SET column1=value1 WHERE condition;
DELETE FROM	Elimina datos de una tabla según lo especificado en las condiciones.	DELETE FROM table_name WHERE condition;
JOIN	Combina filas o registros de varias tablas según tengan columnas relacionadas.	SELECT column1, column2 FROM table1 JOIN table2 ON table1.column = table2.column
INNER JOIN	Devuelve solo las filas coincidentes de ambas tablas.	SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column = table2.column;
LEFT JOIN	Devuelve todas las filas de la tabla izquierda o tabla 1 y las coincidentes en la tabla derecha o tabla 2.	SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
RIGHT JOIN	Devuelve todas las filas de la tabla derecha o tabla 2 y las coincidentes de la tabla derecha o tabla 1.	SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
FULL JOIN	Devuelve todas las filas o registros de ambas tablas.	SELECT column1, column2 FROM tabke1 FULL JOIN table2 ON table1.column = table2.column;
DISTINCT	Devuelve los valores únicos de una columna	SELECT DISTINCT column1 FROM table_name;
COUNT	Cuenta el número de filas o valores no nulos en una columna.	SELECT COUNT(*) FROM table_name; or  SELECT COUNT(column1) FROM table_name;
SUM	Calcula la suma de valores en una columna.	SELECT SUM(column1) FROM table_name;
AVG	Calcula el valor promedio de una columna.	SELECT AVG(column1) FROM table_name;
MAX	Encuentra el valor máximo en una columna.	SELECT MAX(column1) FROM table_name;
MIN	Encuentra le valor mínimo en una columna.	SELECT MIN(column1) FROM table_name;

....