Optimizar Inserciones de DataFrame a Bases SQL con Python

Normalmente, ocupamos algún motor de bases de datos SQL para guardar el procesamiento de nuestros datos o simplemente es donde acaba nuestro flujo de ETL.

En mi caso, me encontré con el tema de que los tiempos de inserción que eran demasiado grandes, por lo que me puse en marcha a buscar una solución, entonces aquí les muestro una explicación muy corta, pero concisa de los procesos que se ocuparon para la solución de este problema.

¿Cómo funcionan las conexiones a bases de datos en Python?

En Python, el acceso a bases de datos se encuentra definido a modo de estándar en las especificaciones de DB-API y que puedes leer en la PEP 249. Esto significa que independientemente de la base de datos que utilicemos, los métodos y procesos de conexión, lectura y escritura de datos en Python, siempre serán los mismos, más allá del conector o la base de datos que usemos.

Ejemplos de Conectores de Bases SQL

MySQL

- MySQL-python : está escrito en C

- mysql-connector-python : está escrito en Python

- PyMySQL :que está escrito en Python, siendo una especie de reemplazo de MySQL-python

SQL Server

- pymssql : Pymssql es un software respaldado por la comunidad y está escrito en Python

PostgreSQL

- psycopg2

- PyGreSQL : está escrito en Python

Tipos de módulos de conexión

Para desarrollar un proyecto en Python en el que se quiera implementar el uso "Bases de Datos SQL", se tienen distintos módulos que pueden ser útiles, por ejemplo "PYODBC" que es uno de ellos.

ODBC:

Open DataBase Connectivity (ODBC) es un estándar de acceso a las bases de datos desarrollado por SQL Access Group (SAG) en 1992. El objetivo de ODBC es hacer posible el acceder a cualquier dato desde cualquier aplicación, sin importar qué sistema de gestión de bases de datos (DBMS) almacené los datos.

PYODBC:

Pyodbc es un módulo de Python de código abierto que simplifica el acceso a las bases de datos "ODBC" desde Python, implementando el uso de la DB API 2.0 de una forma conveniente para Python. Pyodbc también es considerado como un controlador SQL para Python.

Después de esta breve introducción a las conexiones a SQL empecemos

Data Set, Documentación y Proceso

Base de Datos Obtenida desde Kaggle cuenta con 2240 registros

Librerías que utilizaremos:

Conexión Estándar Mediante el uso de un Cursor de PyMySQL

Primero veamos como se ve una conexión normal a una base de datos utilizando, el conector pymysql.

Como se utiliza PyMySQL

Para poder insertar el DataFrame ocuparemos el cursor de pymysql.

Mediante un ciclo for cada fila en el DataFrame se convertirá en una tupla y a esa tupla le construiremos una query para ingresar datos en SQL.

Ejecutaremos cada línea del dataframe con el cursor para ir ingresando línea por línea los datos en la tabla.


*Si no tenemos la tabla creada en la base de datos, tendríamos que escribir la query para crearla.

Tiempo de ingesta de 4480 registros con este método 11.6 Segundos

Conexión con SQLAlchemy y Pandas

SQLAlchemy es un ORM, pero primero respondamos la siguiente pregunta.

¿Qué es un ORM?

ORM u Object Relational Mapper es un software o herramienta que asigna su código a una base de datos sin que usted haga uso de un conector de base de datos directamente, ya que es abstracción del proceso de conexión de la base de datos. Esto, a su vez, nos permite conectarnos a cualquier base de datos relacional sin cambiar demasiado el código.

(La abstracción es el proceso de poner a disposición las características de una herramienta o programa para uso directo sin que nos preocupemos por cómo funciona).

Pandas y SQLAlchemy te quieren y hacen todo por ti

A qué me refiero con esto

- Si no está creada la tabla, la tabla, entonces es creada en el momento de la ejecución

- Utiliza un chucksize para insertar de golpe un rango de filas

- No te preocupas por la creación de Querys

- Con el método "multi" ejecuta varias ingestas a la vez

- Con if_exists te da opciones para agregar o borrar los datos si la tabla ya está creada y tiene datos


Tiempo de ingesta de 4480 registros con este método 3.01 Segundos

Con lo que hemos aprendido ya tenemos una mejora con SQLAlchemy - Pandas, pero esto se puede mejorar aún más?...

PyODBC entra en acción

Conexión con Pyodbc

Validamos que tenemos instalados los Drivers ODBC

Con la conexión pyodbc y sqlalchemy podemos acceder al tuning de sqlalchemy que es "fast_executemany" utilizando el event de ejecución del cursor, comprobaremos si el executemany está desactivado, si ese es el caso, lo activamos fast_executemany puede aumentar el rendimiento de las executemany operaciones al reducir en gran medida el número de viajes de ida y vuelta al servidor.

Tiempo de ingesta de 4480 registros con este método 1.32 Segundos

Comparacion

Pero como lo que no medimos no lo podemos comprobar y corregir, ahora veremos como crece el tiempo de inserción de cada uno de los métodos conforme crece el tamaño del DataFrame

Creamos una función en la cual eleve x**2 veces el tamaño del DataFrame en cada iteración de un total 5, registraremos en cada una de las iteraciones el tiempo que tarda en insertar el DataFrame cada uno de los métodos y los resultados los graficamos.

Como se puede observar, el tiempo de inserción de los demás métodos crece exponencialmente,

mientras que el método de pyodbc se mantiene casi paralelo al eje x.

Con esto podemos comprobar que Pyodbc es nuestro campeón de esta competencia

Codigo Final

Puedes encontrar el proyecto dando clic en cualquiera de las imágenes