python MySQL 写入excel

#!/usr/bin/env python#coding=utf-8import xlwtimport xlrdimport MySQLdbimport osos.chdir("E:\work")def lancome15_write_rows():    #将字段写入到EXCEL新表的第一行    L = ['Id', 'Amount of money', 'Purchase times']    for ifs in range(len(L)):         sheet.write(0,ifs,L[ifs])    wbk.save('lancome15_result_8.9.csv')    sql = "SELECT DISTINCT category FROM lancome15_order_online"     cursor.execute(sql)    numrows = int(cursor.rowcount)    L = []    for i in range(numrows):        g = cursor.fetchone()        sheet.write(0,i+3,g[0])        L.append(g[0])    wbk.save('lancome15_result_8.9.csv')    print Ldef lancome15_write_cols():    sql = "SELECT DISTINCT uid FROM lancome15_order_online"     cursor.execute(sql)    numrows = int(cursor.rowcount)    sum = 0    for i in range(numrows):        g = cursor.fetchone()        sum += 1        sheet.write(i+1,0,g[0])    wbk.save('lancome15_result_8.9.csv')    print sumdef lancome15_read_excel():    workbook = xlrd.open_workbook(r'E:\work\lancome15_result_8.9.csv')    sheet1 = workbook.sheet_by_index(0) # sheet索引从0开始    rows = sheet1.row_values(0)     cols = sheet1.col_values(0)     return rows[3::],cols[1::]def lancome15_result_func():    result = lancome15_read_excel()    row_name = result[0]     col_name = result[1]     for i in range(len(col_name)):        sql1 = "SELECT SUM(ogn*price), COUNT(oid) FROM lancome15_order_online where uid = '%s'" % (col_name[i])        cursor.execute(sql1)        g = cursor.fetchone()                   sheet.write(i+1,1,g[0])        sheet.write(i+1,2,g[1])        for j in range(len(row_name)):             sql2 = 'SELECT COUNT("%s") FROM lancome15_order_online where uid = "%s" AND category = "%s"' \              % (row_name[j], col_name[i], row_name[j])             cursor.execute(sql2)            f = cursor.fetchone()             sheet.write(i+1,j+3,f[0])    wbk.save('lancome15_result_8.9.csv')if __name__ == "__main__":    conn=MySQLdb.connect("localhost","root","111111","work")    cursor=conn.cursor()    wbk = xlwt.Workbook()    sheet = wbk.add_sheet('test1',cell_overwrite_ok=True)    print "Begin"    lancome15_write_rows()    lancome15_write_cols()    lancome15_read_excel()    lancome15_result_func()    print "Over"

今天的工作需要从MySQL表提取出以下内容：用户ID、购买金额、购买次数和各个类别的购买次数，将最终结果写入excel表。程序中的lancome15_result_func函数运行极其缓慢，后经请教他人得知MySQL未创建索引的缘故。