Python MySQLdb 查询生成 Excel 文件

来源：互联网发布：redhat yum安装mysql 编辑：程序博客网时间：2024/06/05 17:17
# encoding=utf-8import os, sys, string, types, datetimeimport MySQLdbimport xlwtezxf = xlwt.easyxfreload(sys)sys.setdefaultencoding('utf-8')enddate = raw_input('请输入截止日期(YYYY-MM-DD):'.encode('gbk'))try:    d = enddate.split('-')    datetime.date(int(d[0]), int(d[1]), int(d[2]))except Exception, e:    print '输入日期格式异常！'.encode('gbk')    sys.exit()# 参数设置SERVER = '192.168.0.100'USERNAME = 'root'PASSWD = 'root'DB = 'test'# 当前路径path = os.getcwd().decode('gbk').encode('utf-8')rfile = u'测试_%s.xls' %enddate# 定义格式heading_xf = ezxf('font: bold on; align: wrap on, vert centre, horiz center;'        'borders: left thin, right thin, top thin, bottom thin;'        'pattern: pattern solid,fore_colour sky_blue;') # 表头格式kinds = 'int text text text date money int money money int int int'.split() # 字段类型col_width_px = (50, 60, 220, 320, 80, 100, 60, 100, 80, 60, 60, 80) # 每列列宽（像素）kind_to_xf_map = {        'date': ezxf('borders: left thin, right thin, top thin, bottom thin;', num_format_str='yyyy-mm-dd'),        'int': ezxf('borders: left thin, right thin, top thin, bottom thin;', num_format_str='##0'),        'money': ezxf('borders: left thin, right thin, top thin, bottom thin;', num_format_str='#,##0.00'),        'text': ezxf('borders: left thin, right thin, top thin, bottom thin;')        }data_xfs = [kind_to_xf_map[k] for k in kinds]# 连接数据库try:    conn = MySQLdb.connect(host = SERVER, user = USERNAME, passwd = PASSWD, db = DB, charset = 'utf8')except :    sys.exit()# 获取 cursor 对象进行操作#cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) # 使用字典cursor取得结果集cursor = conn.cursor()conn.cursor# 查询 sqlsql = """ SELECT                  number AS '编号', province AS '省份'          FROM                  test          WHERE                  date < %s """# 获取查询结果#cursor.execute("SET NAMES 'utf8'")cursor.execute(sql, (enddate,))data = cursor.fetchall()# 创建工作簿及表单book = xlwt.Workbook('utf-8')sheet = book.add_sheet(u'测试', cell_overwrite_ok=True)# 表字段名信息写入header = cursor.descriptionrowx = 0for colx, value in enumerate(header):    sheet.write(rowx, colx, value[0], heading_xf)    sheet.col(colx).width = int(col_width_px[colx] * 36.568) # 设置列宽 1px = 36.568# 数据写入if data:    for rec in data:        rowx += 1        for colx, value in enumerate(rec):            sheet.write(rowx, colx, value, data_xfs[colx])# 写入最终文件book.save(os.path.join(path, rfile))# 关闭资源cursor.close()conn.close()
0 0