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

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

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.

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

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.

Comparacion

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.

Codigo Final

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