Python is simple and practical. When there is a need to use it will be more direction, big can not be from the grammar, cycle and other basic view.
As a result of work needs, every day to pull a report to the business colleagues, first use SSIS to do a package deployment to the server, the daily regular pull report to send mail to colleagues. In order to learn python, we have rewritten a copy.
Connect to database from installation python--Generate excel-send mail, step by step try it out, I'm really happy with the success. Specially written down.
ImportSmtplibImportEmail.mime.multipartImportEmail.mime.textImportEmail.mime.baseImportOs.pathImportcx_oracleImportXLWTImport Time#Generate Excel file nameV_curr_time = Time.strftime ('%y%m%d%h%m%s', Time.localtime (Time.time ())) V_file_name='Starbucks Activity Report-'+ V_curr_time +'. xls'#connecting to a databaseconn = Cx_oracle.connect ("mobileapp2","mobileapp2","RAC01") Cur1=conn.cursor ()#Group Query Statement, if it is multi-line end need to add a backslash connectionV_sql ="SELECT xxx" "From xxx"\ "WHERE xxx"Print(V_sql) cur1.execute (v_sql) rows=Cur1.fetchall () v_cnt=len (rows)#To generate an Excel filebook=XLWT. Workbook () Sheet1=book.add_sheet ('Sheet1')#writes the column name as a row of dataSheet1.write (0,0,'Bank Trading Hours') sheet1.write (0,1,'Phone number') sheet1.write (0,2,'Amount') sheet1.write (0,3,'name')#when multiple columns of data are detected, a cell-to-cell write is required, or the four columns are written to a cell in Excel. forIinchRange (len (rows)): forJinchRange (4): #print (Rows[i][j]) #print ("--------")Sheet1.write (i+1, J,rows[i][j]) book.save (v_file_name) cur1.close () conn.close ()#Mail informationfrom ="[email protected]" to="[email protected]"file_name=V_file_nameserver= Smtplib. SMTP ("mail.ccc.com") Server.login ("AAAA","Password")#only if the SMTP server requires authentication #constructs a Mimemultipart object as a root containerMain_msg =Email.mime.multipart.MIMEMultipart ()#constructs a Mimetext object as a message display and attaches to the root container#v_str = "Starbucks activity report, data Total" + v_cnt + "line"Text_msg = Email.mime.text.MIMEText ("Financial app Starbucks activities, yesterday's first investment data, please find, thank you. ") Main_msg.attach (text_msg)#constructs a Mimebase object as the file attachment content and attaches to the root containerContype =V_file_namemaintype, subtype= Contype.split (' ') ## Read the contents of the file and format itdata = open (file_name,'RB') file_msg=email.mime.base.MIMEBase (MainType, subtype) file_msg.set_payload (Data.read ()) Data.close () Email.encoders.encode_base64 (file_msg)## Set Attachment headerbasename =os.path.basename (file_name) File_msg.add_header ('content-disposition', 'Attachment', filename =basename) Main_msg.attach (file_msg)#set root Container propertiesmain_msg[' from'] =frommain_msg[' to'] =tomain_msg['Subject'] ="Financial app:story #889:: 7.17 Starbucks Activity Report"main_msg['Date'] =email.utils.formatdate ()#get the full text after formattingFulltext =main_msg.as_string ()#send mail with SMTPTry: Server.sendmail (from, to, Fulltext)Print("sent successfully")exceptException as E:Print("Send failed") Print(str (e))finally: Server.quit ()
python3.4.3 Connect Oracle to generate reports and send messages