python导出mysql查询结果到excel_python脚本-导出MySQL数据到excel

#!/usr/bin/env python

#coding=utf8

import xlwt

import MySQLdb

import datetime

host = 'localhost'

user = 'scott'

passwd = 'oracle'

db = 'test'

port = 3307

unix_socket = '/data/mysql3307/tmp/3307.sock'

sql_exec_30 = 'select * from test.t3'

sql_exec_40 = 'select * from test.t4'

sql_exec_50 = 'select * from test.t1'

outputdir = '/root/liangguojun/csv.xls'

conn=MySQLdb.connect(host,user,passwd,db,port,unix_socket,charset='utf8')

cursor=conn.cursor()

wbk = xlwt.Workbook()

my_style = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:mm:SS')

def exp_xls (sql_exec,my_sheet,lie=3,lie2=4):

count = cursor.execute(sql_exec)

cursor.scroll(0,mode='absolute')

results = cursor.fetchall()

fields = cursor.description

sheet = wbk.add_sheet(my_sheet,cell_overwrite_ok=True)

for ifs in range(0,len(fields)):

sheet.write(0,ifs,fields[ifs][0])

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])

for var in range(1,len(results)+1):

sheet.write(var,lie,results[var-1][lie],my_style)

if lie2 ==0:

exit;

for var in range(1,len(results)+1):

sheet.write(var,lie2,results[var-1][lie2],my_style)

exp_xls(sql_exec_30,'30')

exp_xls(sql_exec_40,'40')

exp_xls(sql_exec_50,'50')

wbk.save(outputdir)