After the ETL process runs, use Python to send mail

In the target library, if you have a table with 0 rows, use Python to send the message

#-*-coding:utf-8-*-#AUTHOR:ZJC#Description:send Monitor info to someone.#date:20170718ImportContextlibImportPymysqlImportSmtplib fromEmail.mime.textImportMimetext#Monitoring Information VariablesTable_schema_list = ('db-name1','db-name2') column_list="table_schema,table_name,table_type,table_rows,table_comment"#Mailbox Information VariablesMail_host ="smtp.xxxxx.com"  #Set you mail host#defined as a list to implement global variable functionalityMail_subject = ["XX-MONITOR"]mail_sender_name="[email protected]"Mail_sender_password="******"mail_receivers= ["[email protected]","[email protected]"]mail_message_begin="""<p>etl result information </p> <table border= "1" > <tr> <th>table_schema</th> <th>table_name</th> <th>table_rows</th> <TH>TABLE_COMMENT&L T;/th> </tr>"""Mail_message_end="""</table>"""#Post Office send functiondefsendmailto (message): Msg= Mimetext (Message,'HTML','Utf-8') msg['Subject'] =Mail_subject[0]Try: Server=Smtplib. SMTP () server.connect (mail_host) server.login (Mail_sender_name, Mail_sender_password) server.sendmail (Mail_sender_name, Mail_receivers, msg.as_string ()) Server.close ()#print ("Mail sent successfully")        returnTrueexceptSmtplib. Smtpexception:#print ("Error: Unable to send mail")        Raise#Define database connection, Context Manager, automatically close connection after connection@contextlib. ContextManagerdefMySQL (host='10.0.8.*', user="', password='*****', DB="', port=3306, charset='UTF8MB4'): Conn= Pymysql.connect (Host=host, Port=port, User=user, Passwd=password, Db=db, charset=charset) Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor)Try:    yieldcursorfinally: Conn.commit () Cursor.close () Conn.close ( )#Querying MYSQL metadata informationdefquery_schema_table_info (): Resultlist= []    #Connect to the databaseWith MySQL () as cursor:#Read a single recordsql ="Select"+ column_list +"From information_schema. TABLES"               "WHERE Table_schema in"+Str (table_schema_list)#print (SQL)cursor.execute (SQL) forRowinchCursor.fetchall (): Resultlist.append (Row)returnresultlist#determine if the data extraction is normal based on the query resultsdefdeal (): Mess=""Zero_table_count=0 result_list=Query_schema_table_info () forRinchresult_list:#Overall situation            ifr["table_rows"] ==0:mess+="<tr bgcolor= ' Red ' >"                        +"<td>"+ r["Table_schema"] +"</td>"                        +"<td>"+ r["table_name"] +"</td>"                        +"<td>"+ STR (r["table_rows"]) +"</td>"                        +"<td>"+ r["table_comment"] +"</td>"                        +"</tr>"            Else: Mess+="<tr>"                        +"<td>"+ r["Table_schema"] +"</td>"                        +"<td>"+ r["table_name"] +"</td>"                        +"<td>"+ STR (r["table_rows"]) +"</td>"                        +"<td>"+ r["table_comment"] +"</td>"                        +"</tr>"            ifr["table_rows"] ==0:zero_table_count+ = 1#if more than 0 tables are empty, then there is a problem with the data    ifZero_table_count > 0: mail_subject[0]= "-MONITOR error occurred"    #print (Mess)    #print (mail_subject[0])    returnmess#print (Query_schema_table_info ())if __name__=='__main__': Message= Mail_message_begin + deal () +mail_message_end Sendmailto (MESSAGE)

===================

Put it to the server to run a possible error:

Error One:

Unicodedecodeerror: ' ASCII ' codec can ' t decode byte 0xe9 in position 0:ordinal not in range (128)

1. Reason

because by default, Python uses ASCII encoding as follows:

Python-c "Import sys; Print sys.getdefaultencoding () "ASCII

and when Python converts between encodings, Unicode is used as an "intermediate encoding", but Unicode is the largest So long, so here when trying to put ASCII The encoded string is converted into " Intermediate Encoding the Unicode due to exceeding its range, the above error has been reported.

2. Solutions

1) First: Here we will change the python default encoding mode to utf-8, we can avoid the occurrence of the above problem, in the specific way, we in the python Add the following code to the front of the file:

Import'utf-8'if sys.getdefaultencoding ()! =  Defaultencoding:    Reload (SYS)    sys.setdefaultencoding (defaultencoding)

2) The second type: we Add a sitecustomize.py file under the/usr/lib/python2.7/site-packages/directory , The contents are as follows:

Import sys sys.setdefaultencoding (' utf-8 ')

After the ETL process runs, use Python to send mail