使用Python从 MySQL写数据到Excel
#!/usr/bin/env python#coding:utf-8
import xlwt
import MySQLdb
import datetime
database = MySQLdb.connect(host='192.168.1.30',user='root',passwd='123456',db='crm')
#设置字符集
database.set_character_set('utf8')
cursor = database.cursor()
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')
starttime = datetime.datetime.now()
print '开始时间：%s' % (starttime)
#通过SQL得到该表有多少行，如果想取出指定的数据，只需要在后面加where条件即可。
sql2 = 'select count(*) from bill_test;';
cursor.execute(sql2)
count_rows=cursor.fetchone()
wbk = xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet = wbk.add_sheet('sheet 1', cell_overwrite_ok=True)
#设置写excel的样式
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'Times New Roman'
#0x0190设置字体为20，默认为0x00C8 字体为10 ，0x00C8为十六进制的数字
font.height = 0x0190
font.bold = True
style.font = font
#查询得到该表有多少列
query_colums="select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='crm' and table_name='bill_test';"
cursor.execute(query_colums)
count_cols = cursor.fetchone()
sql = 'select member_id, name, tel, phone, dq_datetime, address, parking from bill_test;'
cursor.execute(sql)
#定义所有的列名，共7列
columnName = ['账号','名称','电话','手机','到期日期','地址','园区名称']
#将列名插入表格，共7列
for i in range(len(columnName)):
sheet.write(0,i,columnName,style)
#通过循环取出每一行数据，写入excel
for i in range(1,count_rows-1):
data = cursor.fetchone()
for j in range(0,count_cols-1):
sheet.write(i,j,data,style)
cursor.close()
database.close()
wbk.save('C:\Users\XUWU\Desktop\data01.xls')
endtime=datetime.datetime.now()
print '结束时间:%s' % (endtime)
print '用时：%s 秒' % (endtime-starttime)
页:
[1]