!/usr/bin/env python
2
#coding:utf-8
3
import MySQLdb,sys,xlwt,subprocess,datetime,xlrd,time
4
reload(sys) #######以下两行解决了写excel 时中文字符的问题
5 sys.setdefaultencoding(
‘utf-8‘)
6
class Mysql_Select(object):
7
def __init__(self):
8
pass
9
10
def yes_Format(self):
11 child = subprocess.Popen(
"date +‘%Y-%m-%d %H:%M:%S‘ -d ‘-1 days‘",shell=True,stdout=
subprocess.PIPE)
12 yes_time =
child.stdout.read()
13
return yes_time ############返回前一天的日期，供Mysql 语句使用
14
15
def mysql_Connect_Status(self,date_yesterday):
16 conn = MySQLdb.connect(user=
‘‘,passwd=
‘‘,host=
‘‘,charset=
‘utf8‘)
17 cur =
conn.cursor()
18 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))
19
cur.close()
20
conn.close()
21
return machines_status #############查询生产服务器的Mysql的表
22
23
def excel_Read(self,machines_status):
24 W=xlwt.Workbook(
‘utf8‘) #########创建一个excel写对象
25 ws = W.add_sheet(
‘Sheet1‘) #########增加一个sheet
26 ws.col(0).width = 9999 ##########表格的列属性
27 ws.col(1).width = 6666
28 ws.col(20).width = 6666
29 ws.col(43).width = 6666
30 ws.col(44).width = 6666
31 ws.col(45).width = 6666
32 ws.col(65).width = 9999
33 ws.col(103).width = 6666
34 ws.col(104).width = 6666
35 module_data = xlrd.open_workbook(
‘sales_module.xls‘) #######创建一个excel读对象
36 table =
module_data.sheet_by_index(0) #########读取第一个sheet
37 ncols =
table.ncols #########表的所有列
38
for raws
in xrange(len(machines_status)):
############## 写模板里的固定值；
39
for values
in xrange(ncols):
40
if values
not in [0,1,20,43,44,45,53,54,55,56,57,59,65,103,104
]:
41 ws.write(raws+1,values,label=table.cell(1
,values).value)
42
43
for values
in xrange(ncols):
##############写模板的第一行
44 ws.write(0,values,label=
table.cell(0,values).value)
45
46
for raw
in xrange(len(machines_status)):
###########写订单的数据；
47 ws.write(raw+1,0,label=machines_status[raw][0])
#订单号
48 ws.write(raw+1,1,label=str(machines_status[raw][1]).split(
‘ ‘)[0])
#订单日期
49 ws.write(raw+1,20,label=machines_status[raw][2])
#表头备注
50 ws.write(raw+1,43,label=str(machines_status[raw][1]).split(
‘ ‘)[0])
#表头预发货日期
51 ws.write(raw+1,44,label=str(machines_status[raw][1]).split(
‘ ‘)[0])
#表头预完工日期
52 ws.write(raw+1,45,label=str(machines_status[raw][3]))
#存货编码
53 ws.write(raw+1,53,label=str(machines_status[raw][4]))
#报价
54 ws.write(raw+1,53,label=str(machines_status[raw][4]))
#报价
54 ws.write(raw+1,54,label=str(machines_status[raw][4]))
#含税单价
55 ws.write(raw+1,55,label=str(machines_status[raw][4] - round(machines_status[raw][4]/1.17*0.17,2)))
#金额
_原币_无税
56 ws.write(raw+1,56,label=str(
‘%.2f‘ %(machines_status[raw][4]/1.17*0.17)))
#税额
57 ws.write(raw+1,57,label=str(machines_status[raw][4]))
#价税合计
58 ws.write(raw+1,59,label=str(machines_status[raw][4] - round(machines_status[raw][4]/1.17*0.17,2)))
#无税
_单价
59 ws.write(raw+1,65,label=str(machines_status[raw][0]))
#备注
60 ws.write(raw+1,103,label=str(machines_status[raw][1]).split(
‘ ‘)[0])
#预完工日期
61 ws.write(raw+1,104,label=str(machines_status[raw][1]).split(
‘ ‘)[0])
#预发货日期
62
63 W.save(
‘模板生成.xls‘) ########最后保存并命名
64
65
66
def copy_Remote(self): #####传送到远程服务器
67 child = subprocess.Popen(
"scp -P 7777 模板生成.xls server_net:/share/技术/",shell=True,stdout=
subprocess.PIPE )
68
69 system =
Mysql_Select()
70
if __name__ ==
‘__main__‘:
71 yes_time =
system.yes_Format()
72 machines_status =
system.mysql_Connect_Status(yes_time)
73
system.excel_Read(machines_status)
74 system.copy_Remote()
最后代码完成并生成的excel表如下：
代码部分，还有待优化，加油；
python操作MySQL--实例
标签：for 远程服务器 pen shell set ima object import 表头
