# Set the interpreter for liunx system to run python code
#!/usr/bin/python3
# python declares that the encoding format of the file is UTF-8
# python2 reads files in ASCII encoding by default. If the encoding format is not declared, it may not be able to correctly parse non-ASCII characters (such as Chinese characters).
# python3 starts to support Chinese by default, so you can omit this line of comments.
# -*- coding: utf-8 -*-
#Import Python’s SMTP library for sending emails
import smtplib
#Import the MIMEMultipart class from the email library, used to create multi-part MIME emails
from email.mime.multipart import MIMEMultipart
#Import the MIMEText class from the email library to create MIME emails with text parts
from email.mime.text import MIMEText
#Import the MIMEApplication class from the email library, which is used to create MIME emails with binary files as email attachments.
from email.mime.application import MIMEApplication
#Import the pandas library, which is a powerful library for data analysis and processing
import pandas as pd
#Import the pymysql library, which is a Python library for connecting and operating MySQL databases
importpymysql
# Not used
#import openpyxl
# Define database connection configuration, including host address, user name, password, database name
db_config = {<!-- -->
'host': '192.168.75.148', # Database host address
'user': 'seluser', # Database user name
'password': 'zzz-123-ZZZ', # Database password
'database': 'db1', # Database name
}
# Define SMTP server and port for sending emails
smtp_server = 'smtp.qq.com' # SMTP server address
port = 587 # SMTP server port
# smtp_server_secure = False means not using secure connections (such as TLS)
smtp_server_secure = False
# Define sender and recipient email addresses
sender = '************@qq.com' # Sender's email address
receiver = '*********@qq.com, *********@qq.com' # Recipient email address
# Define the password used for SMTP server login
password = '**********************' # SMTP server login password
# Define SQL query statements for querying data from the database
sql = "SELECT id number, rid room number, name name, love hobby, addr address FROM table1" #Query statement, query the data of id, rid, name, love, addr fields from the table named "table1"
# Use the pymysql library to connect to the MySQL database, **db_config is used to pass database connection parameters
connection = pymysql.connect(**db_config)
try:
with connection.cursor() as cursor: # Use the context manager to manage the database connection and ensure that the connection is closed correctly
cursor.execute(sql) #Execute SQL query statement
result = cursor.fetchall() # Get query results
finally:
connection.close() # Close the database connection in the finally block to ensure that the database connection will be closed regardless of whether an exception occurs.
#Define an empty list to store query results in list form
data = []
for row in result: # Traverse each row of data in the query result
data.append(list(row)) # Convert each row of data into list form and add it to the data list
# Use the pandas library to convert query results into DataFrame objects to facilitate subsequent data processing and analysis.
df = pd.DataFrame(data)
# Use list comprehensions to get all column names and store them in the field_names variable
field_names = [description[0] for description in cursor.description]
# Set column names for the DataFrame object, here use the field_names obtained previously
df.columns = field_names
# Convert the DataFrame object into an HTML format table and set style parameters
# index=False means not to display the index column, border=0 means not to display the border, header=True means to display the header
#The conversion result is stored in the html_content variable
html_content = df.to_html(index=False, border=0, header=True)
# Define the header style of the HTML page, including background color, font size, padding, etc.
html_head = '''<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Table</title>
<style>
body {
background-color:#CCC; /* Set the background color to light gray */
font-size:12px; /* Set the font size to 12px */
font-family:"宋体"; /* Set the font family to 宋体 */
}
table {
margin:5px auto; /* Set the top and bottom margins of the table to 5px, and the left and right margins to auto, so that the table is horizontally centered on the page */
width:60%; /* Set the width of the table to 60% of the page width */
padding:5px; /* Set the padding of the table to 5px */
}
th {
background-color:#76B7BC; /* Set the background color of the header cell to light blue */
font-size:14px; /* Set the font size of the header cell to 14px */
padding:5px; /* Set the padding of the header cell to 5px */
}
tr:nth-child(even) {
background-color:#E6E6FA; /* Set the background color of the even-numbered rows (row 2n) to lavender */
padding:5px 6px; /* Set the padding of the even-numbered row (row 2n) to 5px 6px */
}
tr:nth-child(odd) {
background-color:#FFF; /* Set the background color of odd-numbered rows (row 2n + 1) to white */
padding:5px 6px; /* Set the padding of odd-numbered rows (row 2n + 1) to 5px 6px */
}
</style>
</head>
<body>''' # Define the tail style of the HTML page, that is, the closing tag, including </body> and </html>
html_tail = '''</body>
</html>'' # Define the tail style of the HTML page, that is, the closing tag, including </body> and </html>
# Store the previously generated HTML content in the html_content variable
html_content = html_head + html_content + html_tail
# Print out the content of the email for easy viewing
print('content: ', html_content)
# Open a file named 'a.html' and prepare to write the email content
with open('a.html', 'w') as f:
# Write the email content to the file
f.write(html_content)
# Convert the DataFrame object to a CSV format file and store it in the 'data.csv' file
df.to_csv('data.csv', index=False, encoding='utf-8')
# Create a new Excel file and save it as 'data.xlsx'
df.to_excel('data.xlsx', index=False)
#Create a MIMEMultipart object to build the email body and attachments
msg = MIMEMultipart()
#Set the sender's email address
msg['From'] = sender
# Set recipient email address
msg['To'] = receiver
# Set email subject
msg['Subject'] = 'MySQL query results'
# Set the email body to HTML format, and add the previously generated html_content to the end of the email body
# Note that this is html format, so use MIMEText instead of MIMEText(body, 'plain')
body = 'This is the table attachment of the MySQL query results. Please find the attached data sheet. \
\
' + html_content
msg.attach(MIMEText(body, 'html'))
#Add attachment, the CSV file 'data.csv' generated earlier is used here
try:
with open('data.csv', 'rb') as f:
# Read and convert the file content into a MIMEApplication object for adding to the email as an attachment.
attachment = MIMEApplication(f.read())
#Set the name and type of email attachment so that recipients can correctly identify and open the attachment.
attachment.add_header('Content-Disposition', 'attachment', filename='data.csv')
# Add attachments to emails
msg.attach(attachment)
except IOError as e:
print("Error: Unable to read CSV file - ", e)
#Add attachments in Excel format
try:
with open('data.xlsx', 'rb') as f:
xlsx_attachment = MIMEApplication(f.read())
xlsx_attachment.add_header('Content-Disposition', 'attachment', filename='data.xlsx')
msg.attach(xlsx_attachment)
except IOError as e:
print("Error: Unable to read Excel file - ", e)
# Configure the SMTP server and port, then log in to the SMTP server (enable TLS if a secure connection is required) and prepare to send emails
server = smtplib.SMTP(smtp_server, port)
server.starttls() if smtp_server_secure else server.login(sender, password)
try:
# Use the sender's email address, the recipient's email address and the constructed email object to send the email
server.sendmail(sender, receiver, msg.as_string())
print("sent successfully")
except smtplib.SMTPException as e:
print("Error: Email sending failed - ", e)
# Exit SMTP server connection
server.quit()
