Saltar al contenido principal
InicioTutorialesPython

Tutorial sobre cómo ejecutar consultas SQL en Python y R

Aprenda formas fáciles y eficaces de ejecutar consultas SQL en Python y R para el análisis de datos y la gestión de bases de datos.
3 may 2024  · 13 min leer

Qué es SQL

El lenguaje de consulta estructurado (SQL) es el más utilizado para ejecutar diversas tareas de análisis de datos. También se utiliza para mantener una base de datos relacional, por ejemplo: añadir tablas, eliminar valores y optimizar la base de datos. Una base de datos relacional simple consta de varias tablas interconectadas, y cada tabla consta de filas y columnas.

Por término medio, una empresa tecnológica genera millones de puntos de datos cada día. Se necesita una solución de almacenamiento sólida y eficaz que permita utilizar los datos para mejorar el sistema actual o crear un nuevo producto. Una base de datos relacional como MySQL, PostgreSQL y SQLite resuelve estos problemas proporcionando una sólida gestión de bases de datos, seguridad y alto rendimiento.

Funciones básicas de SQL

  • Crear nuevas tablas en una base de datos
  • Ejecutar consultas en una base de datos
  • Recuperar datos de una base de datos
  • Insertar registros en una base de datos
  • Actualizar registros en una base de datos
  • Eliminar registros de una base de datos
  • Optimice cualquier base de datos

SQL es una habilidad muy demandada que te ayudará a conseguir cualquier trabajo en el sector tecnológico. Empresas como Meta, Google y Netflix siempre están buscando profesionales de datos que puedan extraer información de bases de datos SQL y proponer soluciones innovadoras. Puedes aprender los conceptos básicos de SQL siguiendo el tutorial Introducción a SQL en DataCamp.

¿Por qué utilizar SQL con Python y R?

SQL puede ayudarnos a descubrir el rendimiento de la empresa, comprender los comportamientos de los clientes y controlar las métricas de éxito de las campañas de marketing. La mayoría de los analistas de datos pueden realizar la mayoría de las tareas de inteligencia empresarial ejecutando consultas SQL, así que ¿por qué necesitamos herramientas como PoweBI, Python y R? Las consultas SQL permiten saber qué ha ocurrido en el pasado, pero no predecir proyecciones futuras. Estas herramientas nos ayudan a comprender mejor el rendimiento actual y el crecimiento potencial.

Python y R son lenguajes polivalentes que permiten a los profesionales ejecutar análisis estadísticos avanzados, construir modelos de aprendizaje automático, crear API de datos y, en última instancia, ayudar a las empresas a pensar más allá de los KPI. En este tutorial, aprenderemos a conectar bases de datos SQL, rellenar bases de datos y ejecutar consultas SQL utilizando Python y R.

Nota: Si usted es nuevo en SQL, entonces tome la pista de habilidades SQL para entender los fundamentos de la escritura de consultas SQL.

Tutorial de Python

El tutorial de Python cubrirá los aspectos básicos de la conexión con varias bases de datos (MySQL y SQLite), la creación de tablas, la adición de registros, la ejecución de consultas y el aprendizaje de la función de Pandas read_sql.

Puesta en marcha

Podemos conectar la base de datos utilizando SQLAlchemy, pero en este tutorial, vamos a utilizar el paquete incorporado de Python SQLite3 para ejecutar consultas en la base de datos. SQLAlchemy ofrece soporte para todo tipo de bases de datos mediante una API unificada. Si estás interesado en aprender más sobre SQLAlchemy y cómo funciona con otras bases de datos, consulta el curso Introducción a las bases de datos en Python.

MySQL es el motor de bases de datos más popular del mundo, y es ampliamente utilizado por empresas como Youtube, Paypal, LinkedIn y GitHub. Aquí aprenderemos a conectar la base de datos. El resto de los pasos para utilizar MySQL son similares a los del paquete SQLite3.

Primero, instala el paquete mysql usando '!pip install mysql' y luego crea un motor de base de datos local proporcionando tu nombre de usuario, contraseña y nombre de la base de datos.

import mysql.connector as sql

conn = sql.connect(
  host="localhost",
  user="abid",
  password="12345",
  database="datacamp_python"
)

Del mismo modo, podemos crear o cargar una base de datos SQLite utilizando la función sqlite3.connect. SQLite es una biblioteca que implementa un motor de base de datos autónomo, sin configuración y sin servidor. Es compatible con DataLab, por lo que lo utilizaremos en nuestro proyecto para evitar errores de host local.

import sqlite3
import pandas as pd

conn= sqlite3.connect("datacamp_python.db")

Empieza a aprender Python y SQL gratis

Introduction to Databases in Python

BeginnerSkill Level
4 hr
95.3K learners
In this course, you'll learn the basics of relational databases and how to interact with them.
See DetailsRight Arrow

Crear una base de datos

En esta parte, aprenderemos a cargar el conjunto de datos COVID-19's impact on airport traffic, bajo licencia CC BY-NC-SA 4.0, en nuestra base de datos SQLite. También aprenderemos a crear tablas desde cero.

Imagen del conjunto de datos Kaggle

El conjunto de datos de tráfico del aeropuerto consiste en un porcentaje del volumen de tráfico durante el periodo de referencia comprendido entre el 1 de febrero de 2020 y el 15 de marzo de 2020. Cargaremos un archivo CSV utilizando la función de Pandas read_csv y luego utilizaremos la función to_sql para transferir el marco de datos a nuestra tabla SQLite. La función to_sql requiere un nombre de tabla (String) y la conexión al motor SQLite.

data = pd.read_csv("data/covid_impact_on_airport_traffic.csv")


data.to_sql(
            'airport',             # Name of the sql table
            conn,                 # sqlite.Connection or sqlalchemy.engine.Engine
            if_exists='replace'
           )

Ahora comprobaremos si lo hemos conseguido ejecutando una rápida consulta SQL. Antes de ejecutar una consulta, necesitamos crear un cursor que nos ayudará a ejecutar consultas, como se muestra en el bloque de código siguiente. Puede tener varios cursores en la misma base de datos dentro de una misma conexión.

En nuestro caso, la consulta SQL devuelve tres columnas y cinco filas de la tabla de aeropuertos. Para mostrar la primera fila, utilizaremos cursor.fetchone().

cursor = conn.cursor()
cursor.execute("""SELECT Date, AirportName, PercentOfBaseline  
                  FROM airport
                  LIMIT 5""")
cursor.fetchone()


>>> ('2020-04-03', 'Kingsford Smith', 64)

Para visualizar el resto de registros, utilizaremos cursor.fetchall(). El conjunto de datos del aeropuerto se carga correctamente en la base de datos con unas pocas líneas de código.

cursor.fetchall()

>>> [('2020-04-13', 'Kingsford Smith', 29),
     ('2020-07-10', 'Kingsford Smith', 54),
     ('2020-09-02', 'Kingsford Smith', 18),
     ('2020-10-31', 'Kingsford Smith', 22)]

Ahora vamos a aprender a crear una tabla desde cero y a rellenarla añadiendo valores de ejemplo. Crearemos una tabla studentinfo con id (entero, clave primaria, autoincremento), nombre (texto) y asunto (texto).

Nota: La sintaxis de SQLite es un poco diferente. Se recomienda consultar la hoja de trucos de SQLite para comprender las consultas SQL mencionadas en este tutorial.

cursor.execute("""
                CREATE TABLE studentinfo
                (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                subject TEXT
                )
                 """)

Comprobemos cuántas tablas hemos añadido a la base de datos ejecutando una sencilla consulta SQLite.

cursor.execute("""
                SELECT name
                FROM sqlite_master
                WHERE type='table'
                 """)
cursor.fetchall()

>>> [('airport',), ('studentinfo',)]

Ejecución de consultas

En esta sección, añadiremos valores a la tabla studentinfo y ejecutaremos consultas SQL sencillas. Utilizando INSERT INTO, podemos añadir una única fila a la tabla studentinfo.

Para insertar valores, necesitamos proporcionar una consulta y argumentos de valor a la función execute. La función rellena las entradas "? " con los valores que hemos proporcionado.

query = """
            INSERT INTO studentinfo
            (name, subject)
            VALUES (?, ?)
        """
value = ("Marry", "Math")
cursor.execute(query,value)

Repita la consulta anterior añadiendo varios registros.

query = """
           INSERT INTO studentinfo
           (name, subject)
           VALUES (?, ?)
        """
values = [("Abid", "Stat"),
          ("Carry", "Math"),
          ("Ali","Data Science"),
          ("Nisha","Data Science"),
          ("Matthew","Math"),
          ("Henry","Data Science")]

cursor.executemany(query,values)

Es hora de verificar el registro. Para ello, ejecutaremos una sencilla consulta SQL que nos devolverá las filas cuyo asunto sea Data Science.

cursor.execute("""
                SELECT *
                FROM studentinfo
                WHERE subject LIKE 'Data Science'
                 """)
cursor.fetchall()

>>> [(4, 'Ali', 'Data Science'),
     (5, 'Nisha', 'Data Science'),
     (7, 'Henry', 'Data Science')]

El comando DISTINCT subject se utiliza para mostrar valores únicos presentes en columnas de asunto. En nuestro caso, son Matemáticas, Estadística y Ciencia de Datos.

cursor.execute("SELECT DISTINCT subject from studentinfo")
cursor.fetchall()

>>> [('Math',), ('Stat',), ('Data Science',)]

Para guardar todos los cambios, utilizaremos la función commit(). Sin un commit, los datos se perderán tras el reinicio de la máquina.

conn.commit()

SQL con Pandas

En esta parte aprenderemos a extraer los datos de la base de datos SQLite y convertirlos en un marco de datos Pandas con una sola línea de código. read_sql proporciona algo más que ejecutar consultas SQL. Podemos utilizarlo para establecer columnas de índice, analizar la fecha y la hora, añadir valores y filtrar nombres de columnas. Aprenda más sobre la importación de datos en Python realizando un breve curso DataCamp.

read_sql requiere dos argumentos: una consulta SQL y la conexión al motor SQLite. La salida contiene las cinco primeras filas de la tabla de aeropuertos en las que PercentOfBaseline es mayor que 20.

data_sql_1 = pd.read_sql("""
                        SELECT Date,City,PercentOfBaseline
                        FROM airport
                        WHERE PercentOfBaseline > 20
                        ORDER BY Date DESC
                        """,
                        conn)
print(data_sql_1.head())

         Date          City  PercentOfBaseline
0  2020-12-02        Sydney                 27
1  2020-12-02      Santiago                 48
2  2020-12-02       Calgary                 99
3  2020-12-02  Leduc County                100
4  2020-12-02      Richmond                 86

Realizar análisis de datos en bases de datos relacionales es ahora más fácil gracias a la integración de Pandas. También podemos utilizar estos datos para prever los valores y realizar complejos análisis estadísticos.

La función plot se utiliza para visualizar el histograma de la columna PercentOfBaseline.

data_sql_1.plot(y="PercentOfBaseline",kind="hist");

Del mismo modo, podemos limitar los valores a los 20 primeros y mostrar un gráfico lineal de series temporales.

data_sql_2 = pd.read_sql("""
                        SELECT Date,City,PercentOfBaseline
                        FROM airport
                        WHERE PercentOfBaseline > 20
                        ORDER BY Date DESC
                        LIMIT 20
                        """,
                        conn)


data_sql_2.plot(x="Date",y="PercentOfBaseline",kind="line");

Por último, cerraremos la conexión para liberar recursos. La mayoría de los paquetes lo hacen automáticamente, pero es preferible cerrar las conexiones después de finalizar los cambios.

conn.close()

R Tutorial

Vamos a replicar todas las tareas del tutorial de Python utilizando R. El tutorial incluye la creación de conexiones, la escritura de tablas, la adición de filas, la ejecución de consultas y el análisis de datos con dplyr.

Puesta en marcha

El paquete DBI se utiliza para conectar con las bases de datos más populares, como MariaDB, Postgres, Duckdb y SQLite. Por ejemplo, instale el paquete RMySQL y cree una base de datos proporcionando un nombre de usuario, una contraseña, un nombre de base de datos y una dirección de host.

install.packages("RMySQL")
library(RMySQL)
conn = dbConnect(
                MySQL(),
                user = 'abid',
                password = '1234',
                dbname = 'datacamp_R',
                host = 'localhost'
                )

En este tutorial, vamos a crear una base de datos SQLite proporcionando un nombre y la función SQLite.

library(RSQLite)
library(DBI)
library(tidyverse)

conn = dbConnect(SQLite(), dbname = 'datacamp_R.db')

Creación de bases de datos

Al importar la biblioteca tidyverse, tendremos acceso a los conjuntos de datos dplyr, ggplot y defaults.

dbWriteTable toma data.frame y lo añade a la tabla SQL. Toma tres argumentos: conexión a SQLite, nombre de la tabla y marco de datos. Con dbReadTable, podemos ver toda la tabla. Para ver las 6 filas superiores, hemos utilizado head.

dbWriteTable(conn, "cars", mtcars)
head(dbReadTable(conn, "cars"))

    mpg    cyl    disp    hp    drat    wt    qsec    vs    am    gear    carb
1    21.0    6    160    110    3.90    2.620    16.46    0    1    4    4
2    21.0    6    160    110    3.90    2.875    17.02    0    1    4    4
3    22.8    4    108    93    3.85    2.320    18.61    1    1    4    1
4    21.4    6    258    110    3.08    3.215    19.44    1    0    3    1
5    18.7    8    360    175    3.15    3.440    17.02    0    0    3    2
6    18.1    6    225    105    2.76    3.460    20.22    1    0    3    1

dbExecute nos permite ejecutar cualquier consulta SQLite, así que lo usaremos para crear una tabla llamada idcard.

Para mostrar los nombres de las tablas de la base de datos, utilizaremos dbListTables.

dbExecute(conn, 'CREATE TABLE idcard (id int, name text)')
dbListTables(conn)

>>> 'cars''idcard'

Añadamos una única fila a la tabla idcard y utilicemos dbGetQuery para mostrar los resultados.

Nota: dbGetQuery ejecuta una consulta y devuelve los registros mientras que dbExecute ejecuta una consulta SQL pero no devuelve ningún registro.

dbExecute(conn, "INSERT INTO idcard (id,name)\
                 VALUES(1,'love')")
dbGetQuery(conn,"SELECT * FROM idcard")


id    name
1    love

Ahora añadiremos dos filas más y mostraremos los resultados utilizando dbReadTable.

dbExecute(conn,"INSERT INTO idcard (id,name)\
                VALUES(2,'Kill'),(3,'Game')
               ")
dbReadTable(conn,'idcard')


id    name
1    love
2    Kill
3    Game

dbCreateTable nos permite crear una mesa sin complicaciones. Requiere tres argumentos: conexión, nombre de la tabla y un vector de caracteres o un data.frame. El vector de caracteres consta de nombres (nombres de columna) y valores (tipos). En nuestro caso, vamos a proporcionar un data.frame de población por defecto para crear la estructura inicial.

dbCreateTable(conn,'population',population)
dbReadTable(conn,'population')

country    year    population

A continuación, vamos a utilizar dbAppendTable para añadir valores en la tabla de población.

dbAppendTable(conn,'population',head(population))
dbReadTable(conn,'population')

country    year    population
Afghanistan    1995    17586073
Afghanistan    1996    18415307
Afghanistan    1997    19021226
Afghanistan    1998    19496836
Afghanistan    1999    19987071
Afghanistan    2000    20595360

Ejecución de consultas

Utilizaremos dbGetQuery para realizar todas nuestras tareas de análisis de datos. Intentemos ejecutar una consulta sencilla y luego aprendamos más sobre otras funciones.

dbGetQuery(conn,"SELECT * FROM idcard")

id    name
1    love
2    Kill
3    Game

También puede ejecutar una consulta SQL compleja para filtrar la potencia y mostrar filas y columnas limitadas.

dbGetQuery(conn, "SELECT mpg,hp,gear\
                  FROM cars\
                  WHERE hp > 50\
                  LIMIT 5")


mpg    hp    gear
21.0    110    4
21.0    110    4
22.8    93    4
21.4    110    3
18.7    175    3

Para eliminar tablas, utilice dbRemoveTable. Como podemos ver ahora, hemos eliminado con éxito la tabla idcard.

dbRemoveTable(conn,'idcard')
dbListTables(conn)
>>> 'cars''population'

Para comprender mejor las tablas, utilizaremos dbListFields, que mostrará los nombres de las columnas de una tabla determinada.

dbListFields(conn, "cars")


>>> 'mpg''cyl''disp''hp''drat''wt''qsec''vs''am''gear''carb'

SQL con dplyr

En esta sección, usaremos dplyr para leer tablas y luego ejecutar consultas usando filter, select y collect. Si no quieres aprender sintaxis SQL y quieres realizar todas las tareas usando R puro, entonces este método es para ti. Hemos sacado la tabla de coches, la hemos filtrado por marchas y mpg, y luego hemos seleccionado tres columnas como se muestra a continuación.

cars_results <-
  tbl(conn, "cars") %>%
  filter(gear %in% c(4, 3),
         mpg >= 14,
         mpg <= 21) %>%
  select(mpg, hp, gear) %>%
  collect()
cars_results

mpg    hp    gear
21.0    110    4
21.0    110    4
18.7    175    3
18.1    105    3
14.3    245    3
...   ...   ...

Podemos utilizar el marco de datos filtrado para mostrar un gráfico boxplot utilizando ggplot.

ggplot(cars_results,aes(fill=as.character(gear), y=mpg)) +
  geom_boxplot()

O podemos mostrar un gráfico de puntos de faceta dividido por el número de marchas.

ggplot(cars_results,
  aes(mpg, ..count.. ) ) +
  geom_point(stat = "count", size = 4) +
  coord_flip()+
  facet_grid( as.character(gear) ~ . )

Conclusión

En este tutorial, hemos aprendido la importancia de ejecutar consultas SQL con Python y R, crear bases de datos, añadir tablas y realizar análisis de datos utilizando consultas SQL. También hemos aprendido cómo Pandas y dplyr nos ayudan a ejecutar consultas con una sola línea de código.

SQL es una habilidad imprescindible para todos los trabajos relacionados con la tecnología. Si está empezando su carrera como analista de datos, le recomendamos que complete el itinerario profesional de Analista de datos con SQL Server en un plazo de dos meses. Esta carrera le enseñará todo sobre consultas SQL, servidores y gestión de recursos.

Puede ejecutar gratuitamente todos los scripts utilizados en este tutorial:

Temas

Cursos relacionados de Python y SQL

Certificación disponible

Course

Introducción a las bases de datos en Python

4 hr
95.2K
En este curso, aprenderá los conceptos básicos de las bases de datos relacionales y cómo interactuar con ellas.
See DetailsRight Arrow
Start Course
Ver másRight Arrow
Relacionado

blog

Python vs. R para ciencia de datos: ¿cuál deberías aprender?

Esta guía te ayudará a resolver una de las dudas más frecuentes de quienes desean iniciarse en ciencia de datos, y a elegir entre R y Python.
Javier Canales Luna's photo

Javier Canales Luna

10 min

tutorial

Tutorial de SQLAlchemy con ejemplos

Aprende a acceder y ejecutar consultas SQL en todo tipo de bases de datos relacionales utilizando objetos Python.
Abid Ali Awan's photo

Abid Ali Awan

13 min

tutorial

Tutorial sobre la ejecución de scripts de Python en Power BI

Descubre las distintas formas de utilizar Python para optimizar el análisis, la visualización y el modelado de datos en Power BI.
Joleen Bothma's photo

Joleen Bothma

9 min

tutorial

Ejemplos y tutoriales de consultas SQL

Si quiere iniciarse en SQL, nosotros le ayudamos. En este tutorial de SQL, le presentaremos las consultas SQL, una potente herramienta que nos permite trabajar con los datos almacenados en una base de datos. Verá cómo escribir consultas SQL, aprenderá sobre
Sejal Jaiswal's photo

Sejal Jaiswal

21 min

tutorial

Cómo usar SQL en pandas Usando pandasql Queries

Libere el poder de SQL dentro de pandas y aprenda cuándo y cómo utilizar consultas SQL en pandas utilizando la biblioteca pandasql para una integración perfecta.

tutorial

Tutorial sobre cómo ejecutar scripts en Python

Aprenda cómo puede ejecutar un script Python desde la línea de comandos, y también cómo puede proporcionar argumentos de línea de comandos a su script.
Aditya Sharma's photo

Aditya Sharma

10 min

See MoreSee More