During the working process, there are some requests to send daily, monthly and quarterly reports from Sales to System Operators.
Today I will write a tutorial on using Python to connect to the database to export the report file and send the report file via email. Here I use Mysql database, export file format is xls.
1. Prepare
- Install mysql-connector library for mysql database connection: pip install mysql-connector-python
- Install xlwt library for processing excel files: pip install xlwt
- Install the smtplib library for emailing: pip install smtplib
2. Processing code
Create file report_python.py, file content as below.
Import library
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | #!/usr/bin/python # -*- coding: utf8 -*- import mysql.connector import xlwt #import datetime from datetime import date, timedelta import time ###Lib smpp### import smtplib import os.path as op from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email.mime.text import MIMEText from email.utils import COMMASPACE, formatdate from email import encoders |
Function to export xls file from MySQL database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | workbook = xlwt.Workbook(encoding ='utf-8') # workbook is the carrier on which sheet depends. def main(sql,sheet,sheet_name): myconn = mysql.connector.connect(host = "10.144.xxxx.xxxx", user = "xxxx", passwd = "xxxx", database= "xxxx") cursor = myconn.cursor() result = cursor.execute(sql) #Search all results results = cursor.fetchall() # Get the data field name in MYSQL fields = cursor.description sheet = workbook.add_sheet(sheet_name,cell_overwrite_ok=True) # Write field information for field in range(0,len(fields)): sheet.write(0,field,fields[field][0]) # Get and write data segment information row = 1 col = 0 for row in range(1,len(results)+1): for col in range(0,len(fields)): sheet.write(row,col,u'%s'%results[row-1][col]) workbook.save(out_path) |
Email sending function
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 | ####Email## def send_mail(send_from, send_to, subject, message, files=[], server="localhost", port=587, username='', password='', use_tls=True): """Compose and send email with provided info and attachments. Args: subject (str): message title message (str): message body files (list[str]): list of file paths to be attached to email server (str): mail server host name port (int): port number username (str): server auth username password (str): server auth password use_tls (bool): use TLS mode """ msg = MIMEMultipart() msg['From'] = send_from msg['To'] = COMMASPACE.join(send_to) msg['Date'] = formatdate(localtime=True) msg['Subject'] = subject msg.attach(MIMEText(message)) for path in files: part = MIMEBase('application', "octet-stream") with open(path, 'rb') as file: part.set_payload(file.read()) encoders.encode_base64(part) part.add_header('Content-Disposition', 'attachment; filename="{}"'.format(op.basename(path))) msg.attach(part) smtp = smtplib.SMTP(server, port) if use_tls: smtp.starttls() smtp.login(username, password) smtp.sendmail(send_from, send_to, msg.as_string()) smtp.quit() |
Main function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | yesterday=date.today() - timedelta(1) filename = 'report_'+time.strftime("%Y-%m-%d")+'.xls' out_path = '/home/ht_anhln/scripts/file/report_'+time.strftime("%Y-%m-%d")+'.xls' sheet01 = 'report'+time.strftime("%Y-%m-%d") #Get the data whose release time and system time are the same month sql01 = "Câu lệnh sql các bạn cần export dữ liệu từ database" if name == 'main': main(sql01,sheet01,sheet01) ##Body localtime = time.asctime( time.localtime(time.time()) ) Body = "File report ABC ngày: " + time.strftime("%Y-%m-%d") send_mail('anhln12@xxxx.vn', ['anhln12@xxxx.vn','a@xxxx.vn','b@xxxx.vn','c@xxxx.vn'], 'File report hàng ngày', Body,[out_path],'email.xxxx.vn',587,'anhln12@xxxx.vn','Password','True') |
3. Results I set crontab to run reports and send emails every day
In the following article, I will guide you to connect to Oracle Database.