首先。我们需要去服务器配置远程链接mysql的账户。然后用字典base64保存
config = {
"host":base64.decodestring("MTIzLIyLjEx"),
"user":base64.decodestring("dXN0"),
"passwd":base64.decodestring("d3TIz"),
"db":base64.decodestring("Z2gwMjl4abWRhdGE="),
"charset":"utf8"
}
|
下一步使用MySQLdb包来操作mysql,链接数据库。条件查询，按用户输入渠道关键词查询获取数据。
conn = MySQLdb.connect(host=config["host"],user=config["user"],passwd=config["passwd"],db=config["db"],charset=config["charset"])
cursor = conn.cursor()
whereis = qd
count = cursor.execute("selecst id,email,name,hometel,ill,address,date from dede_yuyue where email='"+whereis+"'")
print(u"获取数据："+str(count)+u" 条")
print(u"数据报表正在生成中............")
#重置游标位置
cursor.scroll(0,mode='absolute')
#搜取所有结果
results = cursor.fetchall()
|
最后我们将数据写入Excel报表。这里我们使用xlwt包来操作excel。
#获取MYSQL里的数据字段
fields = cursor.description
#将字段写入到EXCEL新表的第一行
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('test1',cell_overwrite_ok=True)
for ifs in range(0,len(fields)):
field = fields[ifs][0]
if field =="id":
field = u"编号"
if field =="email":
field = u"渠道来源"
if field =="name":
field = u"姓名"
if field =="hometel":
field = u"电话"
if field =="ill":
field = u"症状描述"
if field =="address":
field = u"IP地址"
if field =="date":
field = u"提交时间"
sheet.write(0,ifs,field)
_col = sheet.col(ifs)
_col.width = 306*(int(ifs)+10)
ics=1
jcs=0
for ics in range(1,len(results)+1):
for jcs in range(0,len(fields)):
sheet.write(ics, jcs, results[ics-1][jcs])
|
完整示例代码如下：
#!/usr/bin/python
# _*_ coding:utf-8 _*_
# author:Robinn
import xlwt
import MySQLdb
import sys
import time
import os
import base64
# qd = raw_input(u"请输入渠道名称\n")
print(u"请输入渠道名称！")
qd = sys.stdin.readline()
qd = qd.replace("\n", "")
print(u"正在连接数据源,请稍等............")
import sys
defaultencoding = 'utf-8'
if sys.getdefaultencoding() != defaultencoding:
reload(sys)
sys.setdefaultencoding(defaultencoding)
config = {
"host":base64.decodestring("MTIzLIyLjEx"),
"user":base64.decodestring("dXN0"),
"passwd":base64.decodestring("d3TIz"),
"db":base64.decodestring("Z2gwMjl4abWRhdGE="),
"charset":"utf8"
}
conn = MySQLdb.connect(host=config["host"],user=config["user"],passwd=config["passwd"],db=config["db"],charset=config["charset"])
cursor = conn.cursor()
whereis = qd
count = cursor.execute("selecst id,email,name,hometel,ill,address,date from dede_yuyue where email='"+whereis+"'")
print(u"获取数据："+str(count)+u" 条")
print(u"数据报表正在生成中............")
#重置游标位置
cursor.scroll(0,mode='absolute')
#搜取所有结果
results = cursor.fetchall()
#获取MYSQL里的数据字段
fields = cursor.description
#将字段写入到EXCEL新表的第一行
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('test1',cell_overwrite_ok=True)
for ifs in range(0,len(fields)):
field = fields[ifs][0]
if field =="id":
field = u"编号"
if field =="email":
field = u"渠道来源"
if field =="name":
field = u"姓名"
if field =="hometel":
field = u"电话"
if field =="ill":
field = u"症状描述"
if field =="address":
field = u"IP地址"
if field =="date":
field = u"提交时间"
sheet.write(0,ifs,field)
_col = sheet.col(ifs)
_col.width = 306*(int(ifs)+10)
ics=1
jcs=0
for ics in range(1,len(results)+1):
for jcs in range(0,len(fields)):
sheet.write(ics, jcs, results[ics-1][jcs])
import sys
type = sys.getfilesystemencoding()
print(u"数据已经生成完毕,数据报表正在打开,请稍等............")
wbk.save(u""+qd+u'.xls')
time.sleep(2)
print(u"数据报表已经打开.数据文件保存在当前程序目录中,请查看程序所在目录...")
os.system(qd.decode('utf-8').encode(type)+".xls")
|
最后我们可以生成控制台程序。使用py2e x e转换为e x e程序方便在windows上面使用。
也可以使用WxPython来做成GUI程序。同样我们需要用py2exe库来完成生成e x e。
首先封装一个setup.py程序。
#!/usr/bin/python # _*_ coding:utf-8 _*_ # author:Robinn from distutils.core import setup import py2exe setup(console=['Main.py']) |
然后使用下面命令行来生成e x e程序：
Python setup.py py2e x e |
注：本文内容均系原创。如需转载分享请标明出处。