Python3 + mysql queries data and sends emails to the group via excel attachments and python3 emails to the group

Python3 + mysql queries data and sends emails to the group via excel attachments and python3 emails to the group

This example shares the code of the python3 mass mailing excel attachment for your reference. The details are as follows:

Connect to and query mysql, import it to an excel file, and send emails and attachments in a regular group.
Pymysql, smtplib, and xlwt are mainly used.

#1. Import Module

Import pymysql # Python3 mysql module, Python2 is mysqldbimport osimport datetime # timed sending, and date import shutil # file operation import smtplib # mail module from email. mime. text import MIMETextfrom email. mime. multipart import MIMEMultipartfrom email. header import Headerimport timeimport xlwt # Write Data in excel

#2. Connect and query mysql

Def eMysql (mysql_host, mysql_port, mysql_user, mysql_password, mysql_db, SQL): try: db = pymysql. connect (host = mysql_host, port = mysql_port, user = mysql_user, password = mysql_password, db = mysql_db, charset = 'utf8') # connect to the database for encoding utf8, otherwise, print ("mysql connected. ") # connect to the database cursor = db. cursor () # Use the cursor () method to obtain the operation cursor cursor.exe cute (SQL) # Run the SQL statement print ('SQL executed') results = cursor. fetchall () # return results print ('results' executed') db. close () # close the database connection print ('mysql CLOSED ') before T: print (' SQL failed ')

#3. Write Data to an excel file

Def eWrite (fLocate, results, file_sheet, file_subject, style0): try: if OS. path. exists (fLocate): OS. remove (fLocate) # If the file exists, delete f = xlwt. workbook (encoding = 'utf-8') # Open the excel file fs = f. add_sheet (file_sheet) # sheet name subject = list (file_subject) # list-based for I in range (len (subject )): # Find the date column if 'date' in subject [I]: col_num = I for I in range (len (subject): # sheet title fs. write (0, I, subject [I]) for I in range (len (results): # sheet data, the format of the date column is date for j in range (len (results [0]): if j = col_num: fs. write (I + 1, j, results [I] [j], style0) else: fs. write (I + 1, j, results [I] [j]) for I in range (10): # The cell width is fs. col (I ). width = 3333 print ("write finished") f. save (fLocate) failed t: print ("write failed ")

#4. send an email

Def eSend (sender, explorer, username, password, smtpserver, subject, e_content, file_path, file_name): try: # mail header message = MIMEMultipart () message ['from'] = sender # Send message ['to'] = ",". join (receiver) # receiver message ['subobject'] = Header (Subject, 'utf-8') message. attach (MIMEText (e_content, 'plain ', 'utf-8') # Body of the email # construct the attachment att1 = MIMEText (open (file_path + file_name, 'rb '). read (), 'base64', 'utf-8 ') att1 ["Content-Type"] = 'application/octet-stream' att1 ["Content-Disposition"] = "attachment; filename =" + file_name message. attach (att1) # execute smtp = smtplib. SMTP () smtp. connect (smtpserver) # connect to the smtp server. login (username, password) # log on to smtp. sendmail (sender, receiver, message. as_string () # send smtp. quit () print ("SEND") failed t: print ("send failed ")

#5. Configuration and execution

While True: # configuration #__ time _____ ehour = 5 # scheduled hour emin = 21 # scheduled minute esec = 41 # scheduled second current_time = time. localtime (time. time () # current time date cur_time = time. strftime ('% H % m', time. localtime (time. time ())) # current time str #__ mysql _____ mysql_host = mysql_host # login host mysql_port = mysql_port # login port mysql_user = mysql_user # login name mysql_password = mysql_password # login password mysql_db = login # Database SQL = SQL. encode ('utf-8') # SQL query statement code #__ email _____ sender = sender # sender's email receiver = ['2017 @ qq.com '] # recipient's email address, multiple (list form) groups are allowed. # username = username # sender name password = password # smtp password. qq assigns you a string, 163 set smtpserver = smtpserver # Mail Server subject = "Hey, here's something interesting" # mail title e_content = '{0: ^ 27} \ n {1: ^ 27} \ n {2: ^ 25} \ n {3: ^ 25 }'. format ('I', '/\', '(-----)', '(--------)') # mail body #__ file _____ file_path = "D: /"# File Location file_name =" shit.xls "# file name fLocate = file_path + file_name # file path file_subject = 'gave ', 'you', 'A', 'piece ', 'Of ', 'shit. '# sheet title file_sheet =' OK '# sheet Name style0 = xlwt. XFStyle () style0.num _ format_str = 'yyyy-MM-DD '# operation if (current_time.tm_hour = ehour) and (current_time.tm_min = emin) and (current_time.tm_sec = esec )): print ("START") results = eMysql (mysql_host, mysql_port, mysql_user, mysql_password, encrypted, SQL) eWrite (fLocate, results, file_sheet, file_subject, style0) eSend (sender, handler, username, password, smtpserver, subject, e_content, file_path, file_name) print (cur_time) time. sleep (1)

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.