Quando il cliente ti chiede di poter ricevere un report, in merito alle chiamate ricevute sul centralino Asterisk, e nella tua testa si fa largo l’idea di riuscire a risolvere semplicemente e con poco, sappi che stai sognando e che da lì a poco, avrai un brusco risveglio.
Estrarre i dati da database, non era un problema, si trattava di scrivere una stored procedure da eseguire in tempi pianificati dal Crontab Linux, la complicazione vera era rappresentata dalla modalità di pianificazione, che prevedeva la produzione del report in orari diversi per giorni diversi, con dati relativi a un intervallo di tempo anteriore, rispetto alla data di esecuzione, sempre variabile.
Come spesso accade, in ambito informatico, esistono molti modi per ottenerne il medesimo risultato; cercando di essere efficace ma, soprattutto, in ottica di riutilizzo del codice, ho pensato a questa soluzione.
Lo script Python, mostrato di seguito, esegue la stored procedure Mysql, producendo il report in formato Excel che viene inoltrato via email.
# ---------------------------------------------------------------------------------- # - Script file name : backtothefuture.py # - Author : Nicola Montemurro # - DNS administrator : Nicola Montemurro - Tel. xxx, Mobile: xxx # - Create : 21.03.2024 # - Last Update : 24.03.2024 # - Description : # - Position : /usr/local/script # - note : NON modificare senza AUTORIZZAZIONE dell'AMMINISTRATORE # -----------------------------------------------------------------------------------
# Back to the Future: The expression refers to the time when one has
# to stop (over) thinking about the things they could, or not,
# have done in the past so that what happened wouldn’t have happened. #!/usr/bin/env python3 # -*- coding: utf-8 -*- import argparse import datetime from datetime import datetime, date, timedelta import mysql.connector from mysql.connector import (connection) from mysql.connector import Error import smtplib from email.message import EmailMessage import csv ”’ RUN MODE: 1: 2: 3: ”’ class Datetime64Converter(mysql.connector.conversion.MySQLConverter): “”” A mysql.connector Converter that handles datetime64 types “”” mysqlconfig = { ‘user’: ‘mysqluser’, ‘password’: ‘password’, ‘host’: ‘172.17.6.2’, ‘database’: ‘database’, ‘raise_on_warnings’: True } smtphost = ‘172.17.7.10’ sender = “sender@dominio.it” recipients = “utente1@dominio.it, utente2@dominio.com” csvfilename=’output.csv’ ########### CODE MySQLStoredProcedure=’mysql-storedprocedure-name’ isconnected = False now = datetime.today() #print(now) def connectToMysql(): try: conn = mysql.connector.connect(**mysqlconfig) conn.set_converter_class(Datetime64Converter) except mysql.connector.Error as error: print(format(error)) finally: # mysql_isconnected = mysqlconn.is_connected pass return conn def disconnectFromMysql(conn): try: conn.disconnect conn.close except mysql.connector.Error as error: print(format(error)) return conn def callStoredProcedure(conn): past = calcdate(days[0], hours[0], mins[0]) args = (past, now) #2024-03-21 20:00:00 cursor = conn.cursor() try: cursor.callproc(MySQLStoredProcedure, args) results = [] for result in cursor.stored_results(): #results.append(result.fetchall()) results = result.fetchall() # debug #print(results) except mysql.connector.Error as error: print(format(error)) finally: cursor.close return results def buildcsv(chunks, filename): # https://docs.python.org/3/library/csv.html try: fhandle = open(filename, ‘w’, encoding=’utf-8′, newline=”) headers = [‘col1’, ‘col2’, ‘col3’, ‘col4’, ‘col5’, ‘col6′] # create csv write object writer = csv.writer(fhandle, delimiter=’;’, quotechar='”‘, dialect=’excel’, quoting=csv.QUOTE_NONE) writer.writerow(headers) for line in chunks: writer.writerow(line) except Exception as e: print(e) finally: # close file fhandle.close() def composemsg(content, attachment): msg = EmailMessage() msg[‘Subject’] = ‘oggetto email del giorno: ‘ + str(now) msg[‘From’] = sender msg[‘To’] = recipients msg.set_content(str(content)) with open(attachment, ‘rb’) as f: content = f.read() msg.add_attachment(content, maintype=’text’, subtype=’plain’, filename=attachment) return msg def sendmsg(content, attachment): # https://docs.python.org/3/library/smtplib.html emsg = composemsg(content, attachment) try: with smtplib.SMTP(smtphost, 25) as s: s.set_debuglevel(0) s.ehlo() s.ehlo_or_helo_if_needed #s.starttls() s.sendmail(sender, recipients.split(‘,’),emsg.as_string()) s.close except smtplib.SMTPException as error: print(format(error)) except smtplib.SMTPServerDisconnected as error: print(format(error)) except smtplib.SMTPResponseException as error: print(format(error)) except smtplib.SMTPSenderRefused as error: print(format(error)) except smtplib.SMTPRecipientsRefused as error: print(format(error)) except smtplib.SMTPDataError as error: print(format(error)) except smtplib.SMTPConnectError as error: print(format(error)) except smtplib.SMTPHeloError as error: print(format(error)) except smtplib.SMTPNotSupportedError as error: print(format(error)) except smtplib.SMTPAuthenticationError as error: print(format(error)) def calcdate(idays, ihours, imins): backtothefuture = now – timedelta(days = float(idays)) backtothefuture -= timedelta(hours = float(ihours)) backtothefuture -= timedelta(minutes = float(imins)) #print(backtothefuture) return backtothefuture parser = argparse.ArgumentParser() parser.add_argument(“-d”, “–days”, type=int, default=[0], nargs=1, required=False, help=”subtract DAYS days from today’s date”) parser.add_argument(“-H”, “–hours”, type=int, default=[0], nargs=1, required=False, help=”subtract HOURS hours from today’s date”) parser.add_argument(“-m”, “–mins”, type=int, default=[0], nargs=1, required=False, help=”subtract MINS mins from today’s date”) args=parser.parse_args() days=(args.days) hours=(args.hours) mins=(args.mins) def main(): MySQLConnection = connectToMysql() isconnected = MySQLConnection.is_connected if isconnected: spres = callStoredProcedure(MySQLConnection) buildcsv(spres, csvfilename) sendmsg(spres, csvfilename) disconnectFromMysql(MySQLConnection) #print(spres) if __name__ == “__main__”: main()
Di seguito viene mostrato un esempio di uso da Crontab
# # # ### INTERVALLO DI INIZIO A PARTIRE DALLE 17:00 del sabato alle 8:00 del lunedi (39H oppure 1d 15H) 0 8 * * 1 /usr/bin/python3 /usr/local/scripts/backtothefuture.py --days 1 --hours 15 ### INTERVALLO DI INIZIO A PARTIRE DALLE 17:00 fino alle 8:00 (mar - sab) 0 8 * * 2-6 /usr/bin/python3 /usr/local/scripts/backtothefuture.py --hours 15 ### INTERVALLO DI INIZIO A PARTIRE DALLE 8:00 alle 13:00 (lun-sab) 0 13 * * 1-6 /usr/bin/python3 /usr/local/scripts/backtothefuture.py -H 5 ### INTERVALLO DI INIZIO A PARTIRE DALLE 13:00 ALLE 15:00 (lun-sab) 0 15 * * 1-6 /usr/bin/python3 /usr/local/scripts/backtothefuture.py -H 2 ### INTERVALLO DI INIZIO A PARTIRE DALLE 15:00 ALLE 17:00 (lun-sab) 0 17 * * 1-6 /usr/bin/python3 /usr/local/scripts/backtothefuture.py -H 2 # # #
