Generar un reporte en Excel con Python

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:


1
2
3
4
5
6
7
8
9
10
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.fecha

El truco en la consulta para obtener todos los registros de la semana pasada es la condición que utilizamos:


1
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:


1
2
3
4
5
6
7
8
9
10
11
12
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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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:

  1. Creamos m2e, una instancia de Movimientos2Excel, por medio del cual creamos nuestro archivo excel.
  2. Consultamos a la base y recorremos los registros.
  3. 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().
  4. 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.

¿Cómo crear un bot de Telegram?

Hace unos días tuve programe un bot de Telegram para registrar los asistentes a un evento: la idea es sencilla, aquellos participantes buscaban el bot; cuando se conectaran al mismo respondía un mensaje con el comando y que parámetros debían usar para registrarse. Al recibir el comando con los parámetros, verificaba que los parámetros estén bien, los guardaba en una base de datos y les respondía que fueron registrados.

A continuación les detallo los pasos para crear un sencillo bot de Telegram que responda un comando.

1 – Crear el bot en Telegram

El primer paso es crear un bot desde Telegram, para ello se recurre a un bot llamado BotFather, este bot nos permite crear un bot y obtener un API Token que luego utilizaremos desde nuestro programa.

También nos brinda link de la API de los bots
https://core.telegram.org/bots para que podamos ver que funciones tenemos.

Utilizando el comando /newbot y siguiendo los pasos que nos indica BotFather creamos el nuevo bot y nos brinda el Token.

Para ahorrar tiempo utilice la version web de Telegram, es más sencillo realizar las capturas y las pruebas.

Debemos indicar un nombre para el bot y luego un usuario, Telegram nos pide que el usuario del bot termine con la palabra bot.

2 – Librería, python-telegram-api

Necesitamos alguna libreria para que nos sirva de interface con la API de Telegram, afortunadamente esta libreria ya existe en Pÿthon y se llama python-telegram-bot, la instalamos en nuestra pc utilizando pip:

pip install python-telegram-bot

3 – Programando nuestro bot

La forma mas sencilla es adaptar uno de los ejemplos provistos por Telegram, el ejemplo que utilice se llama echobot2, cuya funcion simplemente a frase ingresada por el usuario responder con la misma frase.

Sugiero que se descarguen el archivo en su maquina, y lo tengan abierto en el editor. Lo primero que debemos hacer es indicar cual es nuestro token; para ello dentro de la función main() reemplazamos «TOKEN» por el nuestro.


1
 updater = Updater("INGRESAR AQUI EL TOKEN", use_context=True)

Guardamos el cambio. Ahora podremos ejecutar el programa y posteriormente buscar en Telegram nuestro bot, lo hacemos utilizando el nombre que le dimos.

Como se comento antes, el bot simplemente responde a cada frase del usuario con la misma frase. Vamos modificarlo para que solo responda cuando se escriba «manzana verde», y responda con la frase «prefiero comer pizza».

Para ello si buscamos dentro de la función main(), encontraremos una linea con lo siguiente:


1
dp.add_handler(MessageHandler(Filters.text, echo))

Básicamente lo que hace es procesar todos los mensajes, indicando cual sera la función que realizara esta tarea, en este caso se llama echo.

Si buscamos la función echo, vemos que es bastante sencilla, recibe dos parámetros update y context; y dentro de la función tiene una sola linea:


1
update.message.reply_text(update.message.text)

update.message.text es el texto del mensaje que escribio el usuario, y
update.message.reply_text() es la función que permite al bot responder en la conversación.

Comentamos esa linea y la modificamos de la siguiente manera:


1
2
3
4
#update.message.reply_text(update.message.text)

if(update.message.text.upper().find("MANZANAS VERDES") > 0 ):
    update.message.reply_text("Prefiero comer pizza")

Guardamos, volvemos a ejecutar nuestro bot y cuando realizamos una prueba ocurre lo siguiente:

El siguiente paso seria crear un comando para que ejecute una acción especifica. Crearemos un comando llamado sumar, que reciba dos parámetros y los sume. Para esto debemos volver a ir a la función main(), y donde estan definidos los diferentes handler, crearemos uno nuevo, justo debajo de los ya existentes:


1
2
3
dp.add_handler(CommandHandler("start", start))
dp.add_handler(CommandHandler("help", help))
dp.add_handler(CommandHandler("sumar", sumar))

Aquí le indicamos que para el comando «sumar» llame a la función «sumar» (no estuvimos muy creativos). Debemos definir sumar, de la siguiente manera:


1
2
3
4
5
6
7
8
9
10
def sumar(update, context):
    try:
        numero1 = int(context.args[0])
        numero2 = int(context.args[1])

        suma = numero1 + numero2
        update.message.reply_text('La suma es '+str(suma))

    except (IndexError, ValueError):
        update.message.reply_text('Por favor utiliza dos numeros')

Desde context podemos obtener los argumentos, lo utilizamos dentro de un bloque de try para poder responder en caso que no se envíen numero y falle la conversión a enteros, en ese caso respondemos que solo utilice números. En el caso que los podamos convertir los sumamos y devolvemos la suma con update.message.reply_text().

Como ven es muy sencillo crear un bot de Telegram que realice una tarea simple.

El repositorio con el ejemplo es el siguiente:
https://github.com/gsampallo/micropython_demo_bot

Recuerden, que el token se revoco, por lo que sera necesario que Uds. creen un nuevo bot y utilicen su propio token.