Estos días estuve que trabajando en preparar algunos reportes semanales; la idea es automatizarlos para que todos los lunes genere el reporte (partiendo de una consulta a la base de datos) cree un excel y lo envié por correo a las personas interesadas. Básicamente ahorrarme trabajo.
Utilice dos librerias de Python:
- mysql.connector Para conectarnos con la base de datos, mysql en este caso. Se puede instalar por medio de pip , realizando:
pip install mysql-connector-python - xlwt Para generar el archivo en excel. También se puede instalar con pip: pip install xlwt
El programa lo arme con cinco archivos, cuatro clases para manejar los datos y las acciones y uno que maneja el hilo principal; la idea es que lo llame cron de forma semana.
Para poder mostrar como funciona, vamos a adaptarlo a un ejemplo, supongamos que tenemos una base de datos donde se registran los movimientos del stock de productos:
El reporte consiste en todos los movimientos que se realizaron en esta semana (pueden aplicarlo a cualquier periodo, con un simple cambio).
Para ello utilizaremos un sencilla consulta sql a la base:
SELECT
movimientos.fecha,
productos.producto_id,
productos.descripcion,
movimientos.tipo_movimiento,
movimientos.cantidad
FROM movimientos
INNER JOIN productos ON movimientos.producto_id = productos.producto_id
WHERE WEEKOFYEAR(movimientos.fecha) = (WEEKOFYEAR(curdate())-1)
ORDER BY productos.descripcion,movimientos.fechaEl truco en la consulta para obtener todos los registros de la semana pasada es la condición que utilizamos:
WEEKOFYEAR(movimientos.fecha) = (WEEKOFYEAR(curdate())-1)Devuelve el nro. de la semana del año y le pedimos que sea igual a la semana anterior. De esta forma obtenemos todos los registros necesarios para el reporte.
Movimiento.py
Movimiento.py sera el primer archivo que crearemos, su función sera la de almacenar un registro (recuerdan el patrón MVC?). Será una clase cuyo constructor tendrá como argumento un array con los datos del movimiento y lo almacenara dentro de atributos propios. Tendrá lo siguiente:
class Movimiento(object):
def __init__(self,lista):
self.fecha = lista[0]
self.producto_id = lista[1]
self.producto = lista[2]
self.tipo_movimiento = lista[3]
self.cantidad = str(lista[4])
def listar(self):
print (self.fecha,self.producto_id,self.producto,self.tipo_movimiento,self.cantidad)MovimientosToExcel.py
Como dije anteriormente, deseamos generar el reporte en un archivo excel, esta sera la función de MovimientosToExcel.py; además del constructor tendrá dos métodos, uno para agregar registros/filas y otro para guardar la planilla en un archivo.
import xlwt
from datetime import datetime
class MovimientosToExcel:
def __init__(self):
self.wb = xlwt.Workbook()
self.ws = self.wb.add_sheet('Planilla de Movimientos ',cell_overwrite_ok=True)
self.ws.write(0, 0, 'Listado de Movimientos')
columnas = ["Fecha",
"Producto ID",
"Producto",
"Tipo Movimiento",
"Cantidad"]
c = 0
for columna in columnas:
self.ws.write(1, c, columna)
c = c + 1
self.fila = 2
def agregarItem(self,item):
self.ws.write(self.fila, 0, item.fecha)
self.ws.write(self.fila, 1, item.producto_id)
self.ws.write(self.fila, 2, item.producto)
self.ws.write(self.fila, 3, item.tipo_movimiento)
self.ws.write(self.fila, 4, item.cantidad)
self.fila = self.fila + 1
def guardarPlanilla(self,nombreArchivo):
self.wb.save(nombreArchivo)
print ("Generado")Describo brevemente que hace cada método:
- Constructor: instancia Workbook, de manera que podemos comenzar a crear la planilla, crea la cabecera de la tabla, para ello utiliza la vble. columnas donde están los datos de las columnas.
- agregarItem(self,item): recibe un argumento; ese argumento sera una instancia de Movimientos.py, y traslada cada atributo del mismo a una fila de la tabla, incremente en 1 la cantidad de filas.
- guardarPlanilla(nombreArchivo) Valga la rebundancia: guarda el workbook en un archivo, el nombre del archivo esta dado por el argumento (si, ‘
nombreArchivo’)
En este punto podemos por algunas lineas, escribir un pequeño programa de prueba para validar que todo funcione:
from Movimiento import Movimiento
from MovimientosToExcel import MovimientosToExcel
#Creamos un registro de prueba
item = []
item.append("2019-04-04")
item.append(1)
item.append("Producto A")
item.append("A")
item.append(5)
#Creamos una instancia de Movimiento con el registro de prueba
movimiento1 = Movimiento(item)
movimiento1.listar()
#Instanciamos MovimientoToExcel y le agregamos el mov1
m2e = MovimientosToExcel()
m2e.agregarItem(movimiento1)
#Le pedimos a m2e que cree el archivo planilla.xls con los datos que tenemos.
m2e.guardarPlanilla("planilla.xls")Luego de ejecutarlo, van a encontrar un nuevo archivo en la carpeta llamado planilla.xls, si lo abrimos con excel tenemos:
De esta manera tenemos un mecanismo sencillo para crear la planilla; nos queda alimentarlo con datos de la base.
reporteSemanal.py
Acá es donde llevaremos el hilo principal, consultaremos a la base de datos, obtendremos el listado de registros y crearemos el archivo excel. Para esto utilizaremos la consulta sql comentada previamente. Lo realizamos de la siguiente manera:
from datetime import datetime, timedelta
import mysql.connector
from mysql.connector import errorcode
from Movimiento import Movimiento
from MovimientosToExcel import MovimientosToExcel
config = {
'user': 'root',
'password': '',
'host': 'localhost',
'database': 'stock',
'raise_on_warnings': True,
}
cursor = 0
try:
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT \
DATE_FORMAT(movimientos.fecha,'%d-%m-%Y') as fecha, \
productos.producto_id, \
productos.descripcion, \
movimientos.tipo_movimiento, \
movimientos.cantidad \
FROM \
movimientos \
INNER JOIN productos ON movimientos.producto_id = productos.producto_id \
WHERE WEEKOFYEAR(movimientos.fecha) = (WEEKOFYEAR(curdate())-1) \
ORDER BY productos.descripcion,movimientos.fecha"
cursor.execute(query)
m2e = MovimientosToExcel()
for fila in cursor:
print(fila)
movimiento = Movimiento(fila)
movimiento.listar()
m2e.agregarItem(movimiento)
m2e.guardarPlanilla("reporte_semanal.xls")
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
cnx.close()
Como pueden ver es relativamente sencillo una vez que lo tenemos separados en partes:
- Creamos m2e, una instancia de Movimientos2Excel, por medio del cual creamos nuestro archivo excel.
- Consultamos a la base y recorremos los registros.
- Dentro del bloque de código de la iteración, creamos una instancia de Movimiento que recibe como argumento el registro completo; luego esa instancia la usamos para alimentar al metodo m2e.agregarItem().
- Luego que recorremos todos los registros, guardamos el archivo excel.
En este punto si ejecutamos reporteSemanal.py vamos a tener un archivo excel con todos los movimientos pertenecientes a la semana anterior.
El envio por correo lo vamos a ver en el siguiente articulo para que sea tan largo.
Pueden encontrar todo el ejemplo de este articulo en github.
https://github.com/gsampallo/automated_report_python
A continuación dejo un link al video de youtube donde se muestra como funciona:
Cuestión 1: A fin de que el ejemplo no sea tan complicado, la credencial que se utiliza para conectarse a mysql están dentro del programa, esto no es buena practica.
Cuestión 2: Si van a probar el ejemplo, tengan a bien de modificar la fecha de los registros de ejemplo o agregar nuevos registros dentro del periodo de tiempo que necesitan.