Use Python to write data from MySQL to Excel

Directly on the code:

#!/usr/bin/env python#coding:utf-8import xlwtimport mysqldbimport datetimedatabase =  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  ' Start time:%s '  %  (starttime) #通过SQL得到该表有多少行, If you want to remove the specified data, just add the Where condition later. sql2 =  ' Select count (*)  from bill_test; '; Cursor.execute (SQL2) Count_rows=cursor.fetchone () [0]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, the default is 0x00c8  font is 10  0X00C8 is a 16-digit font.The height = 0x0190font.bold = truestyle.font = font# query gets how many columns of the table 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 () [0]sql =  ' select member_id,  name, tel, phone, dq_datetime, address, parking from bill_test; ' Cursor.execute (SQL) #定义所有的列名, total 7 columns columnname = [' account ', ' name ', ' phone ', ' phone ', ' expiry date ', ' address ', ' Park name ']  # Insert Column name into table, total 7 columns For i in range (len (columnName)):                  sheet.write (0,i,columnname[i],style) #通过循环取出每一行数据, writing to Excel     for i in range (1,count_rows-1):     data =  cursor.fetchone ()     for j in range (0,count_cols-1):     &Nbsp;   sheet.write (I,j,data[j],style)        cursor.close () Database.close () wbk.save (' C:\Users\XUWU\Desktop\data01.xls ')    endtime=datetime.datetime.now () print  ' End time:%s '  %  (endtime) print  ' spents:%s  seconds '  %  (endtime-starttime)

Status of implementation:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8B/C9/wKiom1hYzHiCOf4YAAAjP-FFP_g772.png-wh_500x0-wm_3 -wmp_4-s_3203430715.png "title=" Excel.png "alt=" Wkiom1hyzhicof4yaaajp-ffp_g772.png-wh_50 "/>

This article is from the "Xu Mingjiang blog" blog, make sure to keep this source http://xumingjiang.blog.51cto.com/703960/1884261

Use Python to write data from MySQL to Excel