python导出mysql查询结果到excel_python查询mysql数据并导入到excel表中

#!/usr/bin/python

# -*- coding: utf-8 -*-

import pymysql

import sshtunnel

import xlsxwriter

def get_sshData2(sql):

with sshtunnel.SSHTunnelForwarder(

ssh_address_or_host='********',

ssh_port=***,

ssh_username='***',

ssh_password='******',

remote_bind_address=('*****',3306)

) as tunnel:

conn=pymysql.connect(host='***',user='***', passwd='***', db='***', port=tunnel.local_bind_port, charset='utf8');

cursor = conn.cursor();

cursor.execute(sql);

# 使用 fetchone() 方法获取单条数据.

results = cursor.fetchall()

print(results)

# 关闭数据库连接

conn.close()

# 返给结果给函数调用者。

return results

def writer_data_to_excel():

sql = "select from_unixtime(reg_time, '%Y-%m-%d')as dd,count(*) from deayou_users where from_unixtime(reg_time, '%Y-%m')='2018-08' and internal_status=0 group by dd ";

results = get_sshData2(sql)

#新建文档

workbook=xlsxwriter.Workbook('11.xlsx')

#创建sheet页

worksheet1=workbook.add_worksheet('注册表')

#设置列宽

worksheet1.set_column("A:A", 14) # 设定A列列宽为40

#添加标题

title=['日期','注册人数']

worksheet1.write_row('A1',title)

#遍历结果集中的每个元素，将数据写入新建的sheet页中

for i in range(len(results)):

for j in range(len(results[i])):

worksheet1.write(i+1,j,results[i][j])

#查询第二条语句

sql1 = "select from_unixtime(addtime, '%Y-%m-%d')as dd,sum(mount/100) from d_order where from_unixtime(addtime, '%Y-%m')='2018-08' and borrow_nid>0 and user_Id not in (349,4833) group by dd ";

results1 = get_sshData2(sql1)

# 创建sheet页

worksheet2 = workbook.add_worksheet('转化表')

# 设置列宽

worksheet2.set_column("A:A", 14) # 设定A列列宽为40

# 添加标题

title1 = ['日期', '转化金额']

worksheet2.write_row('A1', title1)

# 遍历结果集中的每个元素，将数据写入新建的sheet页中

for i in range(len(results1)):

for j in range(len(results1[i])):

worksheet2.write(i + 1, j, results1[i][j])

#查询第三条语句

sql2 = "SELECT FROM_UNIXTIME(yestime,'%Y-%m-%d')dd,SUM(mount/100) FROM d_recover WHERE STATUS=1 AND FROM_UNIXTIME(recover_yestime,'%Y-%m')='2018-08' GROUP BY dd ";

results2 = get_sshData2(sql2)

# 创建sheet页

worksheet3 = workbook.add_worksheet('下载表')

# 设置列宽

worksheet3.set_column("A:A", 14) # 设定A列列宽为14

# 添加标题

title2 = ['日期', '下载人数']

worksheet3.write_row('A1', title2)

# 遍历结果集中的每个元素，将数据写入新建的sheet页中

for i in range(len(results2)):

for j in range(len(results2[i])):

worksheet3.write(i + 1, j, results2[i][j])

workbook.close()

#调用上面的方法写入

writer_data_to_excel()