python多次查询mysql，结果插入到一个excel的不同sheet中，求救

Question

coding:utf8

import sys
import threading

import time

reload(sys)
sys.setdefaultencoding('utf8')

# 注册人数  申请人数

import xlwt
import MySQLdb

import sendPortEmail

def export(host, user, password, dbname, table_name, outputpath):

conn = MySQLdb.connect(host, user, password, dbname, charset='utf8')
cursor = conn.cursor()

# 查询数据库，希望加入多个sheet，比如register，apply，都是来自不同查询语句的结果
sheetname = ['register','apply']
count = cursor.execute(
    "SELECT DATE_FORMAT(create_date,'%Y%m%d') days,COUNT(DISTINCT app_user.id) as 'register' FROM app_user GROUP BY days;")
print count

# 重置游标的位置
cursor.scroll(0, mode='absolute')
# 搜取所有结果
results = cursor.fetchall()

# 获取MYSQL里面的数据字段名称
fields = cursor.description
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('table_' + table_name, cell_overwrite_ok=True)

# 写上字段信息
for field in range(0, len(fields)):
    sheet.write(0, field, fields[field][0])
# 获取并写入数据段信息
row = 1
col = 0
for row in range(1, len(results) + 1):
    for col in range(0, len(fields)):
        sheet.write(row, col, u'%s' % results[row - 1][col])

workbook.save(outputpath)

# 日志输入报表生成时间
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())) + ':Create .xls success!')
# 释放数据连接
cursor.close()
conn.close()

# 结果测试

if name == "__main__":

export(hostname, username, password, dbname, tablename, outputpath)

最后希望加上一个定时器，可以定时跑这个代码，求大神指点

Answer 1

• 这个问题最后还是采用了插入到多个exce中，然后打包压缩，邮件发送给运营人员。
• 定时器采用的是linux系统里面的 crontab 机制。

Answer 2

做个计划任务好了呀

Answer 3

我写的工具,运行一下试试,py3

# -*- coding: utf-8 -*-
import xlrd
import xlwt


def read_from_xls(fp, sheet=None):
    workbook = xlrd.open_workbook(fp)
    if isinstance(sheet, int):
        tables = [workbook.sheet_by_index(sheet)]
    elif isinstance(sheet, str):
        tables = [workbook.sheet_by_name(sheet)]
    else:
        tables = [workbook.sheet_by_name(sn) for sn in workbook.sheet_names()]
    data = []
    for table in tables:
        _table = []
        keys = table.row_values(0)
        for i in range(1, table.nrows):
            vals = table.row_values(i)
            _table.append(dict(zip(keys, vals)))
        if _table:
            data.append(_table)
    return data


def write_xls(fp, data, keys_single_sheet=None, pure_data=False, encoding='GBK'):
    workbook = xlwt.Workbook(encoding=encoding or 'utf-8')
    if isinstance(data, list):
        data = {'sheet': data}
    for sheet_name, items in data.items():
        sheet = workbook.add_sheet(sheet_name)
        keys = keys_single_sheet or items[0].keys()

        head_line_numer = 0
        if not pure_data:
            for idx, k in enumerate(keys):
                sheet.write(0, idx, k)
            head_line_numer = 1
            
        for ldx, item in enumerate(items):
            for idx, key in enumerate(keys):
                sheet.write(ldx+head_line_numer, idx, item[key])
    workbook.save(fp)


def write_xls_beta(fp, data, pure_data=False, encoding='GBK'):
    pass


if __name__ == '__main__':

    data = {
        'sheet1': [
            {
                'name': 'dick',
                'age': 23
            },
            {
                'name': 'dick',
                'age': 22
            },
            {
                'name': 'dick',
                'age': 21
            }
        ],
        'sheet2': [
            {
                'name': 'dick1',
                'age': 231
            },
            {
                'name': 'dick2',
                'age': 232
            },
            {
                'name': 'dick3',
                'age': 233
            }
        ]
    }

    write_xls(fp='test.xlsx', data=data)