import warnings
from pathlib import Path
warnings.filterwarnings("ignore")Bases de datos
Introducción
Una enorme cantidad de datos vive en bases de datos, así que es esencial que sepas cómo acceder a ellas. A veces puedes pedirle a alguien que te descargue una instantánea en un .csv, pero esto se vuelve molesto rápidamente: cada vez que necesites hacer un cambio tendrás que comunicarte con otra persona. Lo que quieres es poder acceder directamente a la base de datos para obtener los datos que necesitas, cuando los necesitas.
En este capítulo, primero aprenderás los fundamentos de los paquetes de Python que pueden interactuar con bases de datos SQL: cómo usarlos para conectarte a una base de datos y luego recuperar datos con una consulta SQL1. SQL, abreviatura de structured query language (lenguaje de consulta estructurado), es la lingua franca de las bases de datos y es un lenguaje importante que todo científico de datos debería aprender. No te convertirás en un experto en SQL al final del capítulo, pero sí podrás identificar los componentes más importantes y entender qué hacen.
Requisitos previos
Para este capítulo necesitarás los paquetes pandas, SQLModel e ibis. Probablemente ya tengas pandas instalado; para instalar SQLModel e ibis, ejecuta respectivamente uv add sqlmodel y uv add ibis-framework en la línea de comandos de tu computadora. Primero, importemos algunos paquetes generales y desactivemos las advertencias detalladas.
Fundamentos de bases de datos
En el nivel más simple, puedes pensar en una base de datos como una colección de dataframes, llamados tablas en la terminología de bases de datos. Al igual que un dataframe de pandas, una tabla de una base de datos es una colección de columnas con nombre, donde cada valor de la columna es del mismo tipo. Hay tres diferencias de alto nivel entre los dataframes y las tablas de bases de datos:
Las tablas de bases de datos se almacenan en disco (es decir, en un archivo) y pueden ser arbitrariamente grandes. Los dataframes se almacenan en memoria y están fundamentalmente limitados (aunque ese límite sigue siendo suficientemente grande para muchos problemas). Puedes pensar en la diferencia entre en disco y en memoria como la diferencia entre la memoria a largo plazo y la memoria a corto plazo (y la capacidad de esta última es mucho más limitada).
Las tablas de bases de datos casi siempre tienen índices. Al igual que el índice de un libro, un índice de base de datos permite encontrar rápidamente las filas de interés sin tener que revisar cada una de ellas.
La mayoría de las bases de datos clásicas están optimizadas para recopilar datos rápidamente, no para analizar datos existentes. Estas bases de datos se llaman orientadas a filas porque los datos se almacenan fila por fila, en lugar de columna por columna. Más recientemente, se han desarrollado mucho las bases de datos orientadas a columnas, que hacen que el análisis de los datos existentes sea mucho más rápido.
Las bases de datos son gestionadas por sistemas de gestión de bases de datos (DBMS, por sus siglas en inglés), que existen en tres formas básicas:
- Los DBMS cliente-servidor se ejecutan en un potente servidor central al que te conectas desde tu computadora (el cliente). Son excelentes para compartir datos con varias personas en una organización. Entre los DBMS cliente-servidor más populares están PostgreSQL, MariaDB, SQL Server y Oracle.
- Los DBMS en la nube, como Snowflake, RedShift de Amazon y BigQuery de Google, son similares a los DBMS cliente-servidor, pero se ejecutan en la nube. Esto significa que pueden manejar fácilmente datasets extremadamente grandes y proporcionar automáticamente más recursos de cómputo según sea necesario.
- Los DBMS en proceso, como SQLite o duckdb, se ejecutan completamente en tu computadora. Son excelentes para trabajar con datasets grandes cuando tú eres el usuario principal.
Conectarse a bases de datos
Para conectarte a una base de datos desde Python hay varias opciones, pero depende del tipo de base de datos a la que te conectes. Sin embargo, los pasos (por ejemplo, conectarse a la base de datos, subir datos, ejecutar consultas SQL) son similares, así que no perderemos mucho eligiendo un solo ejemplo: una base de datos SQLite. SQLite es un motor de base de datos SQL pequeño, rápido y autónomo, y el más utilizado del mundo. De hecho, muchos de los datos de tu computadora y de tu teléfono móvil estarán en bases de datos SQLite. (¡Simon Willison tiene algunas herramientas excelentes para ayudarte a exportar algunos de los datos que has generado!)
Sin embargo, siempre necesitaremos dos pasos, sin importar a qué tipo de base de datos SQL nos conectemos:
Siempre usarás una interfaz de base de datos que proporcione una conexión a la base de datos, por ejemplo el paquete integrado de Python sqlite
También usarás un paquete que envíe y/o extraiga datos hacia/desde la base de datos, por ejemplo pandas
Los detalles precisos de la conexión varían mucho de un DBMS a otro, así que lamentablemente no podemos cubrirlos todos aquí. La configuración inicial a menudo requerirá algunos ajustes (y quizá algo de investigación) para que funcione, pero por lo general solo tendrás que hacerla una vez. Haremos lo posible por cubrir algunos aspectos básicos aquí.
Configurar un DBMS cliente-servidor o en la nube sería complicado para este libro, así que en su lugar usaremos un DBMS dentro de la computadora.
Trabajar directamente con bases de datos
Conectémonos a una pequeña base de datos SQLite llamada Chinook database, que contiene información sobre los artistas, canciones y álbumes de una tienda de música, así como información sobre los empleados de la tienda, los clientes y sus compras. Esta información está contenida en once tablas. La figura siguiente muestra el esquema de datos:
Puedes descargar el archivo, Chinook.sqlite, desde el repositorio de github de este libro aquí; en tu computadora, tendrás que guardarlo en un subdirectorio (relativo a donde se ejecuta tu código) llamado ‘data’ para poder seguir los ejercicios siguientes.
Para empezar, usemos el motor integrado de Python sqlite3 para conectarnos a la base de datos y ejecutar una consulta SQL muy sencilla que seleccione las diez primeras entradas de la tabla ‘Artists’:
import sqlite3
con = sqlite3.connect(Path("data/Chinook.sqlite"))
cursor = con.execute("SELECT * FROM Artist LIMIT 10;")
rows = cursor.fetchall()
rows[(1, 'AC/DC'),
(2, 'Accept'),
(3, 'Aerosmith'),
(4, 'Alanis Morissette'),
(5, 'Alice In Chains'),
(6, 'Antônio Carlos Jobim'),
(7, 'Apocalyptica'),
(8, 'Audioslave'),
(9, 'BackBeat'),
(10, 'Billy Cobham')]Observa que la salida aquí tiene la forma de un objeto de Python llamado tupla. Si quisiéramos ponerla en un dataframe de pandas, basta con pasarla directamente:
import pandas as pd
pd.DataFrame(rows)| 0 | 1 | |
|---|---|---|
| 0 | 1 | AC/DC |
| 1 | 2 | Accept |
| 2 | 3 | Aerosmith |
| 3 | 4 | Alanis Morissette |
| 4 | 5 | Alice In Chains |
| 5 | 6 | Antônio Carlos Jobim |
| 6 | 7 | Apocalyptica |
| 7 | 8 | Audioslave |
| 8 | 9 | BackBeat |
| 9 | 10 | Billy Cobham |
Otro consejo útil es que, si no estás seguro de cuáles son los nombres de las columnas, puedes obtenerlos con:
[i[0] for i in cursor.description]['ArtistId', 'Name']Crear una base de datos
A menudo querrás crear una base de datos SQL para acceder más tarde (de forma eficiente) a subconjuntos de datos. Creemos una base de datos de prueba directamente con el paquete sqlite. Este proceso implica una sentencia CREATE TABLE, luego el nombre de la tabla seguido de los nombres de las columnas y sus tipos de datos.
create_query = """CREATE TABLE test (country VARCHAR(20), gdp REAL, health INTEGER);"""
con_new = sqlite3.connect("data/test_database.sqlite")
con_new.execute(create_query)
con_new.commit()Si esto se ejecuta, ¡has creado una base de datos de prueba! (Puedes revisar el directorio data en tu propia computadora para ver si funcionó, pero obtendrás un error si el directorio data no existe previamente.)
Ahora llenemos la base de datos con algunos valores:
test_data = [("US", 1, 3), ("UK", 0.6, 2), ("France", 0.8, 1)]
con_new.executemany("INSERT INTO test VALUES(?, ?, ?)", test_data)
con_new.commit()Por último, comprobemos si ha funcionado:
con_new.execute("SELECT * FROM test").fetchall()[('US', 1.0, 3), ('UK', 0.6, 2), ('France', 0.8, 1)]¡Yuju, funcionó!
Consultas SQL básicas
Una descripción completa de las consultas SQL está fuera del alcance de este libro, pero intentaremos darte lo básico. Los componentes de nivel superior de SQL se llaman sentencias. Entre las sentencias comunes están CREATE para definir nuevas tablas, INSERT para añadir datos y SELECT para recuperar datos. Nos centraremos en las sentencias SELECT, también llamadas consultas, porque son casi exclusivamente lo que usarás como científico de datos.
Una consulta está formada por cláusulas. Hay seis cláusulas importantes: SELECT, FROM, WHERE, ORDER BY, GROUP BY y LIMIT. Toda consulta debe tener las cláusulas SELECT2 y FROM3, y la consulta más sencilla es SELECT * FROM table, que selecciona todas las columnas de una tabla especificada llamada “table”. WHERE y ORDER BY controlan qué filas se incluyen y cómo se ordenan. GROUP BY convierte la consulta en un resumen, provocando una agregación. LIMIT limita cuántas filas se devuelven.
Importante: en SQL, el orden importa. Siempre debes escribir las cláusulas en el orden SELECT, FROM, WHERE, GROUP BY, ORDER BY. Para mayor confusión, este orden no coincide con el orden en que realmente se evalúan las cláusulas, que es primero FROM, luego WHERE, GROUP BY, SELECT y ORDER BY.
Veamos algunas de ellas en acción. Para obtener algunas entradas, se usa
SELECT * FROM Artist LIMIT 10;Para especificar columnas, se usa
SELECT name FROM Artist LIMIT 10;Para invertir el orden, usa “ORDER BY” y luego “DESC”. Por ejemplo, esta consulta nos da las diez pistas más largas
SELECT name, milliseconds FROM track ORDER BY milliseconds DESC LIMIT 10;Otra característica destacada es poder filtrar. Por ejemplo, podríamos pedir las diez primeras pistas que duren al menos 3 minutos. Ejecutemos esta de verdad:
sql_query = "SELECT name, milliseconds FROM track WHERE milliseconds > 1e3*3*60 ORDER BY milliseconds ASC LIMIT 10;"
cursor = con.execute(sql_query)
rows = cursor.fetchall()
rows[('Bodies', 180035),
('Vivo Isolado Do Mundo', 180035),
('Elvis Ate America', 180166),
('Remote Control', 180297),
('Promises', 180401),
('Emergency', 180427),
('À Vontade (Live Mix)', 180636),
('Hyperconectividade', 180636),
('On Fire', 180636),
('Fascinação', 180793)]Probemos un groupby. Este agrupará por álbum para encontrar la duración media de las pistas en minutos para (los cinco primeros) álbumes:
sql_groupby = "SELECT albumid, AVG(milliseconds)/1e3/60 FROM track GROUP BY albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;"
cursor = con.execute(sql_groupby)
rows = cursor.fetchall()
rows[(340, 0.863),
(345, 1.11065),
(318, 1.6882166666666667),
(314, 1.69135),
(328, 1.8377666666666668)]Joins
Si conoces los joins en pandas, los joins de SQL son muy parecidos. Veamos si podemos unir las tablas ‘album’ y ‘track’ para encontrar el nombre de los álbumes de la consulta anterior.
Ten en cuenta que, en cuanto tenemos los mismos nombres de columna en más de una tabla, debemos especificar a qué tabla nos referimos cuando usamos ese nombre de columna. Hay distintas opciones de join (p. ej., INNER, LEFT) sobre las que puedes saber más aquí.
sql_join = "SELECT track.albumid, AVG(milliseconds)/1e3/60, album.title FROM track INNER JOIN album ON (track.albumid = album.albumid) GROUP BY album.albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;"
cursor = con.execute(sql_join)
rows = cursor.fetchall()
rows[(340, 0.863, "Liszt - 12 Études D'Execution Transcendante"),
(345, 1.11065, "Monteverdi: L'Orfeo"),
(318, 1.6882166666666667, 'SCRIABIN: Vers la flamme'),
(314, 1.69135, 'English Renaissance'),
(328, 1.8377666666666668, 'Charpentier: Divertissements, Airs & Concerts')]Funciones
Ya colamos una función: AVG. Otras que puedes aplicar a columnas son SUM, MIN, MAX, FIRST, LAST y COUNT (la amiga del científico de datos). Puedes saber más sobre las funciones de SQL aquí.
Explorar bases de datos SQL con Datasette
SQL puede resultar bastante intimidante al principio, pero hay una gran herramienta para conocerlo mejor llamada Datasette. Datasette es una herramienta para explorar (y crear y compartir) bases de datos. Su autor dice que “Datasette is aimed at data journalists, museum curators, archivists, local governments, scientists, researchers and anyone else who has data that they wish to share with the world. It is part of a wider ecosystem of 40 tools and 99 plugins dedicated to making working with structured data as productive as possible.” (Datasette está dirigido a periodistas de datos, conservadores de museos, archivistas, gobiernos locales, científicos, investigadores y cualquier otra persona que tenga datos que quiera compartir con el mundo. Forma parte de un ecosistema más amplio de 40 herramientas y 99 plugins dedicados a hacer que trabajar con datos estructurados sea lo más productivo posible).
En este contexto, Datasette es útil de dos maneras:
- para explorar una base de datos de forma interactiva, apuntando y haciendo clic
- para ver cómo una sentencia
SELECTde SQL se construye a partir de operaciones de filtrado, o viceversa
Básicamente, cuando Datasette está en ejecución, verás una página web de inicio (sí, incluso si lo ejecutas en tu propio computador) que presenta todas las tablas de tu dataset. Puedes hacer clic en las tablas para explorarlas, reordenarlas, filtrarlas y más. También puedes ver la consulta SQL equivalente a cualquier selección que hagas, o ejecutar una consulta SQL en el cuadro para obtener la selección que quieras. Pero ten en cuenta que Datasette solo admite sentencias SELECT.
Una función adicional útil es que puedes descargar los resultados de tu consulta SQL como archivo CSV o JSON desde la página web que ejecuta Datasette, una vez que hayas filtrado lo que quieres.
Hay varias formas de usar Datasette para explorar datos:
- puedes ejecutarlo en tu propio computador
- puedes probar una versión en línea (que ya está alojada en la nube), por ejemplo esta base de datos de centrales eléctricas
- puedes usar el servicio de programación en línea glitch para ejecutarlo. Mira un ejemplo aquí.
Datasette se distribuye como un paquete de Python que puedes instalar en la línea de comandos ejecutando uv tool install datasette. Una vez instalado en un entorno de Python, ejecuta
datasette path/to/database.db -oy deberías ver que tu navegador predeterminado se abre de inmediato en una página que muestra las tablas de la base de datos (la página debería tener una dirección que empiece por http://localhost:8001/).
Por último, hay material de formación en SQL que usa Datasette disponible aquí.
TipEjercicio
Usando la base de datos Chinook en tu propia computadora, ejecuta la consulta SQL con el join de antes, es decir:
SELECT
albumid,
AVG(milliseconds) / 1e3 / 60
FROM
track
GROUP BY
albumid
ORDER BY
AVG(milliseconds) ASC
LIMIT
5SQL con pandas
pandas está bien equipado para trabajar con SQL. Podemos pasar directamente la consulta que acabamos de crear usando su función read_sql(), pero ten en cuenta que también debemos pasarle la conexión que creamos a la base de datos:
pd.read_sql(sql_join, con)| AlbumId | AVG(milliseconds)/1e3/60 | Title | |
|---|---|---|---|
| 0 | 340 | 0.863000 | Liszt - 12 Études D'Execution Transcendante |
| 1 | 345 | 1.110650 | Monteverdi: L'Orfeo |
| 2 | 318 | 1.688217 | SCRIABIN: Vers la flamme |
| 3 | 314 | 1.691350 | English Renaissance |
| 4 | 328 | 1.837767 | Charpentier: Divertissements, Airs & Concerts |
Una ventaja de esto es que los nombres de columna de SQL pasan directamente a los nombres de columna de nuestro dataframe.
Ahora bien, cuando escribes Python en Visual Studio Code (al menos con las extensiones de Python instaladas), obtienes mucho soporte de sintaxis y autocompletado de alta calidad. Las extensiones del lenguaje Python también te permiten controlar con mucho cuidado los tipos de las variables con las que trabajas. ¿No sería genial tener todo eso también con SQL (incluso al acceder a él desde Python)? Los dos paquetes que veremos a continuación lo ofrecen. Ambos hacen que trabajar con bases de datos SQL desde Python sea mucho más fácil y productivo.
SQL con ibis
No es del todo satisfactorio tener que escribir tus consultas SQL como texto. ¿Y si pudiéramos crear comandos directamente a partir de comandos de pandas? No puedes hacer exactamente eso, pero hay un paquete que se acerca bastante y se llama ibis. ibis es especialmente útil cuando lees de una base de datos y quieres consultarla igual que lo harías con un dataframe de pandas.
Ibis puede conectarse a bases de datos locales (p. ej., una base de datos SQLite), bases de datos en servidor (p. ej., Postgres) o bases de datos en la nube (p. ej., BigQuery de Google). La sintaxis para crear una conexión es, por ejemplo, ibis.bigquery.connect.
Veamos ibis en acción reproduciendo los comandos que ya vimos con la base de datos Chinook, una base de datos alojada localmente. Primero lo importamos y creamos una conexión a la base de datos.
import ibis
ibis.options.interactive = True
connection = ibis.sqlite.connect("data/Chinook.sqlite")
track = connection.table("track")
track.head()┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string │ int64 │ !int64 │ int64 │ string │ !int64 │ int64 │ !decimal(10, 2) │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────────────────────────────────────────────────────────┼──────────────┼──────────┼─────────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ │ 2 │ Balls to the Wall │ 2 │ 2 │ 1 │ NULL │ 342562 │ 5510424 │ 0.99 │ │ 3 │ Fast As a Shark │ 3 │ 2 │ 1 │ F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman │ 230619 │ 3990994 │ 0.99 │ │ 4 │ Restless and Wild │ 3 │ 2 │ 1 │ F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman │ 252051 │ 4331779 │ 0.99 │ │ 5 │ Princess of the Dawn │ 3 │ 2 │ 1 │ Deaffy & R.A. Smith-Diesel │ 375418 │ 6290521 │ 0.99 │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────┴──────────────┴──────────┴─────────────────┘
Bien, ahora reproduzcamos la siguiente consulta: “SELECT albumid, AVG(milliseconds)/1e3/60 FROM track GROUP BY albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;”. Usaremos un groupby, un mutate (que puedes ver como la sentencia assign de pandas), una ordenación y luego limit() para mostrar solo las cinco primeras entradas.
track.group_by("AlbumId").mutate(
mean_mins_track=track.Milliseconds.mean() / 1e3 / 60
).order_by("mean_mins_track").limit(5)┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ mean_mins_track ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string │ int64 │ !int64 │ int64 │ string │ !int64 │ int64 │ !decimal(10, 2) │ float64 │ ├─────────┼──────────────────────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────┼──────────────┼─────────┼─────────────────┼─────────────────┤ │ 3496 │ Étude 1, In C Major - Preludio (Presto) - Liszt │ 340 │ 4 │ 24 │ NULL │ 51780 │ 2229617 │ 0.99 │ 0.863000 │ │ 3501 │ L'orfeo, Act 3, Sinfonia (Orchestra) │ 345 │ 2 │ 24 │ Claudio Monteverdi │ 66639 │ 1189062 │ 0.99 │ 1.110650 │ │ 3452 │ SCRIABIN: Prelude in B Major, Op. 11, No. 11 │ 318 │ 4 │ 24 │ NULL │ 101293 │ 3819535 │ 0.99 │ 1.688217 │ │ 3448 │ Lamentations of Jeremiah, First Set \ Incipit Lamentatio │ 314 │ 2 │ 24 │ Thomas Tallis │ 69194 │ 1208080 │ 0.99 │ 1.691350 │ │ 3492 │ Sing Joyfully │ 314 │ 2 │ 24 │ William Byrd │ 133768 │ 2256484 │ 0.99 │ 1.691350 │ └─────────┴──────────────────────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────┴──────────────┴─────────┴─────────────────┴─────────────────┘
track.group_by("AlbumId").mutate(mean_mins_track=track.Milliseconds.mean() / 1e3 / 60)┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ mean_mins_track ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string │ int64 │ !int64 │ int64 │ string │ !int64 │ int64 │ !decimal(10, 2) │ float64 │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼───────────────────────────────────────────┼──────────────┼──────────┼─────────────────┼─────────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ 4.000692 │ │ 6 │ Put The Finger On You │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205662 │ 6713451 │ 0.99 │ 4.000692 │ │ 7 │ Let's Get It Up │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 233926 │ 7636561 │ 0.99 │ 4.000692 │ │ 8 │ Inject The Venom │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 210834 │ 6852860 │ 0.99 │ 4.000692 │ │ 9 │ Snowballed │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 203102 │ 6599424 │ 0.99 │ 4.000692 │ │ 10 │ Evil Walks │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263497 │ 8611245 │ 0.99 │ 4.000692 │ │ 11 │ C.O.D. │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 199836 │ 6566314 │ 0.99 │ 4.000692 │ │ 12 │ Breaking The Rules │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263288 │ 8596840 │ 0.99 │ 4.000692 │ │ 13 │ Night Of The Long Knives │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205688 │ 6706347 │ 0.99 │ 4.000692 │ │ 14 │ Spellbound │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 270863 │ 8817038 │ 0.99 │ 4.000692 │ │ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴───────────────────────────────────────────┴──────────────┴──────────┴─────────────────┴─────────────────┘
¿Y los joins? Por supuesto, también puedes hacerlos. Como ejemplo, unamos las tablas genre y track mediante la variable compartida “GenreId”.
genre = connection.table("genre")
genre_and_track = track.inner_join(
genre, predicates=track["GenreId"] == genre["GenreId"]
)
genre_and_track┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ Name_right ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩ │ int64 │ string │ int64 │ int64 │ int64 │ string │ int64 │ int64 │ decimal(10, 2) │ string │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────────────────────────────────────────────────────────┼──────────────┼──────────┼────────────────┼────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ Rock │ │ 2 │ Balls to the Wall │ 2 │ 2 │ 1 │ NULL │ 342562 │ 5510424 │ 0.99 │ Rock │ │ 3 │ Fast As a Shark │ 3 │ 2 │ 1 │ F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman │ 230619 │ 3990994 │ 0.99 │ Rock │ │ 4 │ Restless and Wild │ 3 │ 2 │ 1 │ F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman │ 252051 │ 4331779 │ 0.99 │ Rock │ │ 5 │ Princess of the Dawn │ 3 │ 2 │ 1 │ Deaffy & R.A. Smith-Diesel │ 375418 │ 6290521 │ 0.99 │ Rock │ │ 6 │ Put The Finger On You │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205662 │ 6713451 │ 0.99 │ Rock │ │ 7 │ Let's Get It Up │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 233926 │ 7636561 │ 0.99 │ Rock │ │ 8 │ Inject The Venom │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 210834 │ 6852860 │ 0.99 │ Rock │ │ 9 │ Snowballed │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 203102 │ 6599424 │ 0.99 │ Rock │ │ 10 │ Evil Walks │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263497 │ 8611245 │ 0.99 │ Rock │ │ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────┴──────────────┴──────────┴────────────────┴────────────┘
Ibis hace mucho más de lo que se muestra aquí, incluida la evaluación diferida (lazy evaluation), el trabajo geoespacial, el tipado y, por supuesto, todos los comandos SQL habituales que esperarías. Para saber más, consulta la documentación.
SQL con SQLModel
Ibis no es la única opción inteligente para trabajar con SQL desde Python; SQLModel es otra. Usa sqlmodel cuando quieras crear bases de datos SQL de calidad de producción o quieras consultarlas con una sintaxis algo distinta a la de ibis y con un soporte extremadamente completo.
Así, un par de puntos fuertes clave de sqlmodel son su fantástico soporte de autocompletado y su gran rigor con los tipos de datos (lo que te ahorrará tiempo a largo plazo, sobre todo si estás creando bases de datos).
Primero, asegúrate de tener instalado el paquete ejecutando uv add sqlmodel en la línea de comandos.
Crear una base de datos SQL con SQLModel
Imagina que tienes una tabla SQL llamada “hero” y quieres que contenga estos datos:
| id | name | secret_name | age |
|---|---|---|---|
| 1 | Deadpond | Dive Wilson | null |
| 2 | Spider-Boy | Pedro Parqueador | null |
| 3 | Rusty-Man | Tommy Sharp | 48 |
| 4 | Ms Amazing | Barjabeen Bhabra | 17 |
Ahora, para crear la tabla SQL, escribimos una estructura llamada clase. Las clases se parecen un poco a las funciones, pero en el cuerpo de esta clase enumeramos el nombre de cada campo seguido del código Python del tipo de dato que representa:
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = NoneLuego creamos las filas de datos (esto podría hacerse de forma programática, pero aquí las escribiremos a mano). ¡Fíjate en que hay autocompletado de los campos en el código siguiente!
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Ms Amazing", secret_name="Barjabeen Bhabra", age=17)Ahora necesitamos crear la base de datos propiamente dicha y conectarnos a ella. La sintaxis es el nombre del tipo de base de datos, aquí sqlite, seguido de :/// y de la ruta relativa donde quieres guardar la base de datos.
engine = create_engine("sqlite:///data/hero.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.commit()Ahora podemos obtener una lectura de la base de datos que creamos usando el enfoque directo (es decir, con el paquete sqlite3):
con = sqlite3.connect(Path("data/hero.db"))
con.execute("SELECT * FROM hero;").fetchall()[(1, 'Deadpond', 'Dive Wilson', None),
(2, 'Spider-Boy', 'Pedro Parqueador', None),
(3, 'Rusty-Man', 'Tommy Sharp', 48),
(4, 'Ms Amazing', 'Barjabeen Bhabra', 17)]Leer de SQL con SQLModel
SQLModel traduce todas las sentencias SQL habituales a funciones. “SELECT” no es una excepción. Veamos un ejemplo de lectura de la base de datos SQL directamente desde SQLModel:
from sqlmodel import select
with Session(engine) as session:
statement = select(Hero) # Equivalent to SELECT * from hero
results = session.exec(statement) # Execute the command
for hero in results:
print(hero)secret_name='Dive Wilson' age=None id=1 name='Deadpond'
secret_name='Pedro Parqueador' age=None id=2 name='Spider-Boy'
secret_name='Tommy Sharp' age=48 id=3 name='Rusty-Man'
secret_name='Barjabeen Bhabra' age=17 id=4 name='Ms Amazing'También podemos recuperarlo todo mediante results.all(). El objeto resultante es una lista de objetos “hero” cuyas propiedades se pueden consultar mediante heroes[0].name, etc.
with Session(engine) as session:
statement = select(Hero)
results = session.exec(statement)
heroes = results.all()
print(*heroes, sep="\n")secret_name='Dive Wilson' age=None id=1 name='Deadpond'
secret_name='Pedro Parqueador' age=None id=2 name='Spider-Boy'
secret_name='Tommy Sharp' age=48 id=3 name='Rusty-Man'
secret_name='Barjabeen Bhabra' age=17 id=4 name='Ms Amazing'Puedes encontrar más información en la documentación de SQLModel, pero la mayoría de los comandos de consulta SQL que esperarías están disponibles. Ejecutemos un ejemplo de antes:
with Session(engine) as session:
statement = select(Hero).where(Hero.age < 100).limit(2).order_by(Hero.age)
results = session.exec(statement)
heroes = results.all()
print(*heroes, sep="\n")secret_name='Barjabeen Bhabra' age=17 id=4 name='Ms Amazing'
secret_name='Tommy Sharp' age=48 id=3 name='Rusty-Man'Esto apenas araña la superficie de lo que SQLModel puede hacer: joins, actualización de datos, eliminación de filas y mucho más también son posibles.
Dónde aprender más sobre SQL
Si has terminado este capítulo y quieres aprender más sobre SQL, aquí tienes algunos recursos adicionales:
- SQL for Data Scientists de Renée M. P. Teate es una introducción a SQL diseñada específicamente para las necesidades de los científicos de datos, e incluye ejemplos del tipo de datos altamente interconectados que probablemente encontrarás en organizaciones reales.
- Practical SQL de Anthony DeBarros está escrito desde la perspectiva de un periodista de datos (un científico de datos especializado en contar historias convincentes) y profundiza en cómo introducir tus datos en una base de datos y gestionar tu propio SGBD.
- Consulta alguna de las muchas hojas de referencia de SQL que reúnen todo lo básico en un solo lugar
- Prueba SQL en tu navegador en SQLime
- Aprende SQL resolviendo un misterio de asesinato
SQL se pronuncia “s”-“q”-“l” o “sequel”.↩︎
Para mayor confusión, según el contexto,
SELECTes una sentencia o una cláusula. Para evitar esta confusión, en general usaremos consulta en lugar de sentenciaSELECT.↩︎Técnicamente, solo se requiere
SELECT, ya que puedes escribir consultas comoSELECT 1+1para realizar cálculos básicos. Pero si quieres trabajar con datos (¡como siempre!) también necesitarás una cláusulaFROM.↩︎