Weekly Coding Challenge

Todas las semanas escogemos un proyecto de la vida real para que construyas tu portafolio y te prepares para conseguir un trabajo. Todos nuestros proyectos están construidos con ChatGPT como co-pilot!

Únete al reto

Podcast: Code Sets You Free

Un podcast de cultura tecnológica donde aprenderás a luchar contra los enemigos que te bloquean en tu camino para convertirte en un profesional exitoso en tecnología.

Escuchar el podcast

SQL

Python

Pandas

Editar en Github

Abrir en Colab

Conexión a Bases de Datos SQL

Conectarse a bases de datos SQL usando Python¶

Como expertos en Machine Learning y dependiendo de cómo esté dispuesta la información, seguramente tengamos que interactuar con bases de datos SQL. Es un flujo muy común el de ingestar datos en Python a través de SQL o bases de datos no relacionales y utilizar Python para analizarlos, graficarlos, definirlos y en última instancia, elaborar un modelo predictivo.

Algunas de las bases de datos SQL más utilizadas con SQLite, PostgreSQL y MySQL. SQLite es la mejor alternativa cuando queremos una base de datos portable, sin tener que usar un servidor por separado para ejecutarla. Es idónea para aplicaciones embebidas. Si queremos algo más escalable a futuro y una base de datos en aplicaciones distribuidas, entonces MySQL es una buena opción, mientras que PostgreSQL es una base de datos oportuna para aplicaciones complejas y se usa en la mayoría de soluciones empresariales.

En esta lección veremos cómo interactúan Python y algunas bases de datos SQL. ¿Por qué deberíamos preocuparnos por conectar Python y una base de datos SQL? Tal vez, como expertos en Machine Learning, necesitemos construir un "ETL pipeline" (tubería ETL) automatizado. Conectar Python a una base de datos SQL nos permitirá usar Python para sus capacidades de automatización. También podremos comunicarnos entre diferentes fuentes de datos. No tendremos que cambiar entre diferentes lenguajes de programación, podremos usar nuestras habilidades de Python para manipular datos de una base de datos SQL. No necesitaremos un archivo CSV.

Conexión a SQLite¶

Python tiene su propio wrapper para realizar una conexión a una base de datos SQLite. Además este paquete viene instalado en la versión base y no es necesario instalar nada para empezar a usarlo. Debido a que la base de datos es sencilla, su código también:

In [ ]:

import sqlite3

# Se genera una base de datos llamada "test.db" en el directorio actual
con = sqlite3.connect("test.db")

### CREATE: Generar una base de datos vacía con 5 variables: ID, NAME, AGE, ADDRESS y SALARY
con.execute("""CREATE TABLE COMPANY
    (ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL)""")

### INSERT: Introducir en la base de datos algún registro
con.execute("""INSERT INTO COMPANY
    VALUES (1, 'Richard', 40, 'Spain', 20000.00)""")

con.execute("""INSERT INTO COMPANY
    VALUES (2, 'Óscar', 25, 'France', 15000.00)""")
con.commit()

### SELECT: Filtrar de entre los valores de la base de datos
#  El cursor permite definir un conjunto de resultados
cursor = con.execute("SELECT * from COMPANY")
for row in cursor: # Itera por todas las filas del filtro resultante
    print(f"ID = {row[0]} NAME = {row[1]} ADDRESS = {row[2]} SALARY = {row[3}]}")

# También se pueden almacenar los resultados en un DataFrame usando Pandas
import pandas as pd
cursor_df = pd.read_sql_query("SELECT * FROM COMPANY", con)

### UPDATE: Actualizar un registro en la base de datos
con.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()

### DELETE: Eliminar uno o varios registros de la base de datos
con.execute("DELETE from COMPANY where ID = 2")
con.commit()

### Finalizar conexión a la base de datos
con.close()

Como se ha podido ver, este paquete proporciona una interfaz muy amigable para crear una base de datos portable y fácil de usar. Con simplemente dos sentencias, con.execute() y con.commit() podremos realizar prácticamente todo lo que necesitemos para interactuar con ella. En contraposición a SQLAlchemy, aquí es necesario saber la sintaxis de SQL.

Conexión a MySQL¶

Como lenguaje primordial para analizar datos, Python también tiene su propio wrapper para realizar una conexión a una base de datos MySQL, PyMySQL. Al tratarse de una base de datos propia, requiere previamente de una instalación en un servidor o sistema operativo y su instanciación para poder cerrar la conexión y poder utilizarla.

Conexión a la base de datos¶

El propio MySQL nos permitirá crear una base de datos con simplemente pulsar a un botón e introducir un nombre. Luego, esta base de datos se puede referenciar al establecer la conexión. Si deseamos utilizar Python para crear una base de datos, simplemente omitiríamos el parámetro en la llamada, como se verá a continuación.

1. Tabla ya creada con anterioridad¶

En este caso introducimos la información como sigue para establecer la conexión.

In [ ]:

import pymysql

con = pymysql.connect(host = "localhost",
    user = "root", 
    password = "pass",
    db = "COMPANY", # Nombre de la tabla que si la hemos creado anteriormente
)

2. Tabla no creada¶

En este caso simplemente eliminamos el argumento db de la llamada y ejecutamos la sentencia de creación, detallando las columnas y sus características. Para ello utilizamos la sentencia cur.execute() (este paquete requiere que se defina un cursor para cada llamada) y después la seleccionamos con con.select_db():

In [ ]:

con = pymysql.connect(host = "localhost",
    user = "root", 
    password = "pass"
)
cur = con.cursor()

cur.execute("""CREATE TABLE COMPANY
    (ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL)""")

con.select_db("COMPANY")

Uso de la base de datos¶

La ejecución de las sentencias que ya conocemos sobre la base de datos es igual que en el caso de SQLite, con las funciones cur.execute() y con.commit() pero con la diferencia de que la ejecución se realiza con el cursor y no con la conexión propia.

In [ ]:

### INSERT: Introducir en la base de datos algún registro
cur.execute("""INSERT INTO COMPANY
    VALUES (1, 'Richard', 40, 'Spain', 20000.00)""")

cur.execute("""INSERT INTO COMPANY
    VALUES (2, 'Óscar', 25, 'France', 15000.00)""")
con.commit()

### SELECT: Filtrar de entre los valores de la base de datos
cur.execute("SELECT * from COMPANY")
rows = cur.fetchall()
for row in rows: # Itera por todas las filas del filtro resultante
    print(f"ID = {row[0]} NAME = {row[1]} ADDRESS = {row[2]} SALARY = {row[3]}")

# También se pueden almacenar los resultados en un DataFrame usando Pandas
import pandas as pd
cursor_df = pd.read_sql("SELECT * FROM COMPANY", con)

### UPDATE: Actualizar un registro en la base de datos
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()

### DELETE: Eliminar uno o varios registros de la base de datos
cur.execute("DELETE from COMPANY where ID = 2")
con.commit()

### Finalizar conexión a la base de datos
cur.close()
con.close()

Conexión a PostgreSQL¶

Como tercera alternativa, para conectarse a PostgreSQL usando Python también existe una librería, Psycopg2. Es el wrapper más conocido y utilizado aunque hay otros que también lo son. Este paquete mantiene la esencia de las sentencias de PyMySQL, requiriendo para su uso los objetos de conexión y de cursor. La conexión es también muy simple y podemos o bien crear una base de datos con Python o conectarnos directamente a ella a través del paquete.

Conexión a la base de datos¶

El propio PostgreSQL nos permitirá crear una base de datos con simplemente pulsar a un botón e introducir un nombre. Luego, esta base de datos se puede referenciar al establecer la conexión. Si deseamos utilizar Python para crear una base de datos, simplemente omitiríamos el parámetro en la llamada, como se verá a continuación.

1. Tabla ya creada con anterioridad¶

En este caso introducimos la información como sigue para establecer la conexión.

In [ ]:

import psycopg2

con = psycopg2.connect(host = "localhost",
    user = "root", 
    password = "pass",
    database = "COMPANY", # Nombre de la tabla que si la hemos creado anteriormente
)

2. Tabla no creada¶

En este caso simplemente eliminamos el argumento database de la llamada y ejecutamos la sentencia de creación, detallando las columnas y sus características. Para ello utilizamos la sentencia cur.execute() y con.commit():

In [ ]:

con = psycopg2.connect(host = "localhost",
    user = "root", 
    password = "pass"
)
cur = con.cursor()

cur.execute("""CREATE TABLE COMPANY
    (ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL)""")
con.commit()

Uso de la base de datos¶

La ejecución de las sentencias que ya conocemos sobre la base de datos es igual que en el caso de MySQL, con las funciones cur.execute() y con.commit() pero con la diferencia de que la ejecución se realiza con el cursor y no con la conexión propia.

In [ ]:

### INSERT: Introducir en la base de datos algún registro
cur.execute("""INSERT INTO COMPANY
    VALUES (1, 'Richard', 40, 'Spain', 20000.00)""")

cur.execute("""INSERT INTO COMPANY
    VALUES (2, 'Óscar', 25, 'France', 15000.00)""")
con.commit()

### SELECT: Filtrar de entre los valores de la base de datos
cur.execute("SELECT * from COMPANY")
rows = cur.fetchall()
for row in rows: # Itera por todas las filas del filtro resultante
    print(f"ID = {row[0]} NAME = {row[1]} ADDRESS = {row[2]} SALARY = {row[3]}")
con.commit()

# También se pueden almacenar los resultados en un DataFrame usando Pandas
import pandas as pd
cursor_df = pd.read_sql_query("SELECT * FROM COMPANY", con)

### UPDATE: Actualizar un registro en la base de datos
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()

### DELETE: Eliminar uno o varios registros de la base de datos
cur.execute("DELETE from COMPANY where ID = 2")
con.commit()

### Finalizar conexión a la base de datos
cur.close()
con.close()

A pesar de que en esta lección se haya ejemplificado el código para los tres paquetes, es remarcable que la sintaxis es prácticamente igual, sobre todo para el caso de PyMySQL y Psycopg2. Estos códigos te servirán de guía para saber rápidamente cómo poder interactuar con las bases de datos relacionales más utilizadas en el mundo del análisis de los datos.