Python Operation mysql--Instance

There is a period of time, not updated, but this is not their own lazy excuses, hope that they can persist;

Python can operate the MySQL database, need to install the Mysql-python, and to read into the Excel table and generate Excel table, you need to xlrd and XLWT these two Python modules;

1, installation Mysql-python:

Yum-y Install Mysql-python ######## #在python Program import module with the name MySQLdb

2. Installation of Excel two modules:

Pip install xlrd XLWT # # # #pip The default is to use a foreign mirror, slow speed, can be replaced by the domestic mirror, temporary use-I https://pypi.douban.com/simple/

3, the following code in the other modules are built-in modules;

4. The contents of the template for importing Excel are as follows:

The first row all columns have data, the second row contains the data

1#!/usr/bin/env python2#Coding:utf-83ImportMysqldb,sys,xlwt,subprocess,datetime,xlrd,time4Reload (SYS) ###### #以下两行解决了写excel problems with Chinese characters5 sys.setdefaultencoding ('Utf-8')  6classMysql_select (object):7def __init__(self):8Pass  9  10defYes_format (self):One child = subprocess. Popen ("date + '%y-%m-%d%h:%m:%s '-d '-1 days '", shell=true,stdout=subprocess. PIPE)Yes_time =Child.stdout.read ()13returnyes_time ########### #返回前一天的日期 for use with MySQL statements14  15defMysql_connect_status (self,date_yesterday):conn = MySQLdb.connect (user="', passwd="', host="', charset='UTF8') Cur =conn.cursor ()Machines_status = Cur.fetchmany (Cur.execute ("Select Bdass.salesorder.ordernum,bdass.salesorder.salestime,bdas S.machines.name,bdass.salesorder.goodscode, Bdass.salesOrder.salesPrice from Bdass.salesorder,bdass.machines where BDA ss.salesOrder.salesTime > '%s ' and Bdass. Salesorder.machineid = Bdass.machines.clientId"%date_yesterday)) 19cur.close ()20conn.close ()21streturnmachines_status ############ #查询生产服务器的Mysql的表22  23defExcel_read (self,machines_status):W=XLWT. Workbook ('UTF8') ######## #创建一个excel写对象WS = W.add_sheet ('Sheet1') ######## #增加一个sheetWs.col (0). width = 9999 ######### #表格的列属性 Ws.col (1). width = 6666 Ws.col (+). Width = 6666 Ws.col (+). Width = 6666 (ws.col). width = 6666 (ws.col). width = 6666 Ws.col (+). Width = 9999 Ws.col (103). width = 6666 Ws.col (104). width = 6666 Module_data = Xlrd.open_workbook ('Sales_module.xls') ###### #创建一个excel读对象Table =module_data.sheet_by_index (0) ######## #读取第一个sheetPNS Ncols =table.ncols ######## #表的所有列38 forRawsinchXrange (Len (machines_status)):############## Write a fixed value in the template;39 forValuesinchxrange (ncols):40ifValues notinch[0,1,20,43,44,45,53,54,55,56,57,59,65,103,104]: Ws.write (Raws+1,values,label=table.cell (1, values). Value)42  43 forValuesinchXrange (Ncols):############# #写模板的第一行Ws.write (0,values,label=Table.cell (0,values). Value)45  46 forRawinchXrange (Len (machines_status)):########## #写订单的数据;Ws.write (Raw+1,0,label=machines_status[raw][0])#Order NumberWs.write (Raw+1,1,label=str (Machines_status[raw][1]). Split (' ') [0])#Order DateWs.write (Raw+1,20,label=machines_status[raw][2])#Table Header NotesWs.write (Raw+1,43,label=str (Machines_status[raw][1]). Split (' ') [0])#table Header Pre-shipment DateWuyi Ws.write (Raw+1,44,label=str (Machines_status[raw][1]). Split (' ') [0])#table header Pre-completion dateWs.write (Raw+1,45,label=str (machines_status[raw][3))#Stock CodeWs.write (Raw+1,53,label=str (machines_status[raw][4))#QuoteWs.write (Raw+1,53,label=str (machines_status[raw][4))#QuoteWs.write (Raw+1,54,label=str (machines_status[raw][4))#Price incl. taxWs.write (Raw+1,55,label=str (Machines_status[raw][4]-round (machines_status[raw][4]/1.17*0.17,2)))#Amount_ Original Currency _ No taxWs.write (Raw+1,56,label=str ('%.2f'% (machines_status[raw][4]/1.17*0.17)))#TaxWs.write (Raw+1,57,label=str (machines_status[raw][4))#Total Price TaxWs.write (Raw+1,59,label=str (machines_status[raw][4)-round (machines_status[raw][4]/1.17*0.17,2))#No Tax_ PriceWs.write (Raw+1,65,label=str (machines_status[raw][0))#NotesWs.write (Raw+1,103,label=str (Machines_status[raw][1]). Split (' ') [0])#Pre-completion dateWs.write (Raw+1,104,label=str (Machines_status[raw][1]). Split (' ') [0])#Pre-shipment Date62  W.save ('template generation. xls') ####### #最后保存并命名64  65  66defcopy_remote (self): # # # #传送到远程服务器The child = subprocess. Popen ("scp-p 7777 template generation. xls server_net:/share/Technology/", shell=true,stdout=subprocess. PIPE)68  system =Mysql_select ()70if __name__=='__main__': Yes_time =System.yes_format ()Machines_status =system.mysql_connect_status (yes_time)73System.excel_read (machines_status)System.copy_remote ()




The final code is completed and the resulting Excel table is as follows:

Code part, still need to optimize, refueling;

Python Operation mysql--Instance