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é preparada la información, seguramente tengamos que interactuar con bases de datos SQL. Es un flujo muy común el de consumir 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 son 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 y 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:
import sqlite3
import pandas as pd
# Conéctate a la base de datos o créala si no existe
con = sqlite3.connect("test.db")
### CREATE: Generar una tabla llamada COMPANY con 5 columnas: 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: Insertar registros en la tabla COMPANY
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 e imprimir algunos de los valores de la tabla
# El cursor permite definir un conjunto de resultados
cursor = con.execute("SELECT * from COMPANY")
for row in cursor: # Iterar a través de todas las filas del filtro resultante
print(f"ID = {row[0]} NAME = {row[1]} ADDRESS = {row[2]} SALARY = {row[3]}")
# Los resultados también se pueden almacenar en un DataFrame usando Pandas
cursor_df = pd.read_sql_query("SELECT * FROM COMPANY", con)
### UPDATE: Actualizar un registro en la tabla COMPANY
con.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()
### DELETE: Eliminar un registro de la tabla COMPANY
con.execute("DELETE from COMPANY where ID = 2")
con.commit()
### Terminar la 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. Base de datos creada con anterioridad¶
En este caso introducimos la información como sigue para establecer la conexión.
import pymysql
con = pymysql.connect(host = "localhost",
user = "root",
password = "pass",
db = "my_database", # Nombre de la base de datos si la hemos creado antes
)
2. Base de datos no creada¶
En este caso simplemente eliminamos el argumento db de la llamada y ejecutamos la sentencia de creación. 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():
import pymysql
# Conectar al servidor MySQL
con = pymysql.connect(host='localhost',
user='root',
password='password')
# Crear un objeto cursor
cur = con.cursor()
# Ejecutar consulta SQL para crear una nueva base de datos
database_name = 'my_database'
cur.execute(f"CREATE DATABASE {database_name}")
# Cambiar a la base de datos recién creada
cur.execute(f"USE {database_name}")
# Crear una nueva tabla
cur.execute("""
CREATE TABLE IF NOT EXISTS COMPANY (
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)
""")
# Cerrar cursor y conexión cuando hayamos terminado
cur.close()
con.close()
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.
### INSERT: Insertar registros en la tabla COMPANY
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
# El cursor permite definir un conjunto de resultados
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 tabla COMPANY
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()
### DELETE: Eliminar un registro de la tabla COMPANY
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. Base de datos ya creada con anterioridad¶
En este caso introducimos la información como sigue para establecer la conexión.
import psycopg2
con = psycopg2.connect(host = "localhost",
user = "root",
password = "pass",
db = "my_database", # Nombre de la tabla que si la hemos creado anteriormente
)
2. Base de datos no creada¶
En este caso simplemente eliminamos el argumento db de la llamada y ejecutamos la sentencia de creación. Para ello utilizamos la sentencia cur.execute() y con.commit():
import psycopg2
# Conectar al servidor PostgreSQL
con = psycopg2.connect(
host="localhost",
user="root",
password="pass"
)
# Crear un objeto cursor
cur = con.cursor()
# Crear una nueva base de datos
cur.execute("CREATE DATABASE your_database_name")
con.commit()
# Crear la tabla COMPANY
cur.execute("""CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(50),
SALARY REAL)""")
con.commit()
# Cerrar el cursor y la conexión
cur.close()
con.close()
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().
### INSERT: Insertar registros en la tabla COMPANY
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 tabla COMPANY
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
con.commit()
### DELETE: Eliminar un registro de la tabla COMPANY
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.