Un inconveniente que me suelo encontrar al trabajar con sensores remotos es el hecho de monitorear las mediciones y recolectar los datos de forma sencilla. Si bien existen herramientas geniales como ThingSpeak que nos permiten ver los datos en un gráfico; muchas veces trabajamos con equipos que solo implementan un protocolo MQTT y lo necesitamos para operar.
Para ello arme un pequeño programa en Python que permite conectarse a un servidor broker, suscribirse a uno o mas tópicos del servidor e ir registrando en una planilla de cálculo os datos de estos dispositivos remotos.
Además del nombre del servidor, toma el nombre con el que se va a guardar el archivo Excel y la cantidad de registros que deseamos almacenar; si no indicamos este ultimo parámetro la librería solo tomara 10 registros.
Serán los primeros 10 que lleguen, es decir si le indicamos que se suscriba a dos tópicos: TOPIC1 y TOPIC2, y recibimos 9 mensajes de TOPIC1 y solo 1 de TOPIC2 esos serán los 10 registros que se almacenaran en la planilla.
Antes de comenzar a utilizarlo será necesario tener instalado Python en la pc además de dos librerías:
1. xlwt Para el manejo de la planilla de cálculo, se instala por medio de pip:
pip install xlwt
2. paho.mqtt brinda la posibilidad de conectarnos a un servidor broker, también lo instalamos por medio de pip:
pip install paho.mqtt
Luego que se haya completado el paso anterior, podemos editar el archivo example.py para adecuarlo a nuestro uso; solo necesitamos modificar en dos puntos:
from MQTT2Excel import MQTT2Excel
broker_server = "AQUI INDICAMOS EL SERVIDOR BROKER"
m2e = MQTT2Excel(broker_server,"demo1.xls")
m2e.setRecordsNumber(50) #indicamos que registre 50 lecturas (la suma de todos los mensajes de los topicos)
m2e.addTopics("TOPICO1") #Indicamos el nombre del topico al que queremos suscribirnos
# Si tuvieramos mas simplemente agregamos una linea con el nombre del segundo topico
# m2e.addTopics("TOPICO2")
m2e.start()
De esta forma el programa tomara 50 lecturas y las almacenara en una planilla excel junto con la fecha y hora en que fue recibido el mensaje.
Luego simplemente es cuestion de ejecutar el programa example.py (pueden cambiarle el nombre si lo desean)
Comenzaremos a ver los datos que llegan desde el dispositivo.
Al cabo de un tiempo tendremos completos los 50 registros (salvo que cambiemos la cantidad que requerimos) y el programa se detendrá.
En ese punto ya dispondremos de nuestro archivo excel.
Como se puede apreciar es bastante sencillo de utilizar, quedan algunas cosas por pulir por ejemplo parametrizar si el servidor broker requiere un usuario y clave (aunque solo basta agregar dos lineas); y llegado el caso detectar el tipo de dato que se recibe para formatear la celda, pero no es un problema mayor en mi caso.
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.fecha
El truco en la consulta para obtener todos los registros de la semana pasada es la condición que utilizamos:
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:
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.
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.
