python 查询Mysql并将记录写入到Excel中

#python msyql xlsxwriter

import xlsxwriter
import time
import datetime
import mysql.connector




connect = mysql.connector.connect(user='root', password='123', 
host='192.168.1.1', 
port=3306, 
database='new')


cursor = connect.cursor()


query = ("SELECT requestpay.chargeId as CHARGE_ID,requestpay.imsi AS IMSIS ,requestpay.imei AS IMEIS,requestpay.msg AS MSGS,requestpay.addtime AS ADD_TIME,cmdnam.id AS CMD_ID,cmdnam.cmdName  AS CMD_NAME "
"FROM xc_sp_requestpay requestpay LEFT JOIN xc_sp_cmdnam cmdnam ON requestpay.chargeId = cmdnam.id  "
"WHERE requestpay.addtime between  %s  and %s  and requestpay.stas = 1 "
"ORDER BY requestpay.chargeId ASC")


time1 = (datetime.datetime.now()-datetime.timedelta(hours=2)).strftime('%Y-%m-%d %H:%M:%S')
time2 = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

time3 = (datetime.datetime.now()-datetime.timedelta(hours=2)).strftime('%Y%m%d%H%M%S')
time4 = datetime.datetime.now().strftime('%Y%m%d%H%M%S')


cursor.execute(query,(time1,time2))#传入参数并执行查询。


#Create an new Excel file and add a worksheet.生成文件名称如：20150202131645_20150202151645.xlsx的文件
workbook = xlsxwriter.Workbook(time3+'_'+time4+".xlsx")
worksheet = workbook.add_worksheet();


#设置列宽
worksheet.set_column('A:E',20)


#Add a bold format to use to highlight cells.加粗的格式
bold = workbook.add_format({'bold':True})


#Write some simple text.写标题，并加粗
worksheet.write('A1','IMSIS',bold)
worksheet.write('B1','IMEIS',bold)
worksheet.write('C1','MSGS',bold)
worksheet.write('D1','ADD_TIME',bold)
worksheet.write('E1','CMD_NAME',bold)


#Start form first row after headers，遍历查询记录，写入到Excel中
row = 1
for (CHARGE_ID,IMSIS,IMEIS,MSGS,ADD_TIME,CMD_ID,CMD_NAME) in cursor:
    worksheet.write_string(row,0,str(IMSIS))
    worksheet.write_string(row,1,str(IMEIS))
    worksheet.write_string(row,2,MSGS)
    worksheet.write_string(row,3,ADD_TIME)
    worksheet.write_string(row,4,CMD_NAME)
    row +=1


workbook.close()
cursor.close()
connect.close()