python利用pandas高效从mysql读取数据写入excel多sheet

最新推荐文章于 2024-05-12 05:03:09 发布

rockstics

最新推荐文章于 2024-05-12 05:03:09 发布

阅读量1k

点赞数 1

分类专栏： python 文章标签： python pandas mysql

本文链接：https://blog.csdn.net/rockstics/article/details/126643346

版权

python 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

import os
from sqlalchemy import create_engine
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
import pymysql
import pandas as pd
import datetime

tdy = datetime.date.today()

table_name = 'gitlab_project_commits_week'
sheet1_name = "有多少个项目提交，分别提交多少次"
sheet2_name = "每个项目有哪些用户提交"
sheet3_name = "每个人提交了多少次代码，在那些项目提交"



sql1 = f'''select a.*,web_url from (select id,name,branch_name,count(id) as count from {table_name}  group by id,branch_name) a join (select id,web_url from gitlab_projects ) b WHERE a.`id` = b.`id` ;'''

sql2 = f'''select id,name,author_name from {table_name} group by id,author_name;'''
sql3 = f'''select id,author_name,name,count(id) as count from {table_name}  group by author_name,id;'''

sql_rela_sheet = {
    sheet1_name: sql1,
    sheet2_name: sql2,
    sheet3_name: sql3
}

    #sqlalchemy 作为数据库连接引擎
def export_to_excel():
	engine = create_engine('mysql+pymysql://root:123456@192.168.30.11:3306/gitlab_info')
    writer = pd.ExcelWriter(f'./Gitlab周提交信息-{tdy}.xlsx')
    print("写入数据")
    for sheet, sql in sql_rela_sheet.items():
    	##获取数据
        df = pd.read_sql_query(sql, engine)
        result_data = pd.DataFrame.from_records(df)
        ##将数据写入excel
        result_data.to_excel(writer, sheet_name=sheet, index=False)
    writer.save()
    print("写入完毕！")

##pymysql方式获取数据并写入excel
# cur_dir = os.path.dirname(__file__)
# conn = pymysql.connect(user='root', password='123456', host='192.100.30.111', database='gitlab_info')
# cursor = conn.cursor()
# cursor.execute(sql1)
# col_head = cursor.description   
# columns = [column[0] for column in col_head]  ##获取游标描述也就是查询结果的列标题，作为excel表的第一行
# print(columns)
# tl = cursor.fetchall()
# print(tl)
# res = pd.DataFrame(tl, columns=columns,)
# res.to_excel(os.path.join(cur_dir, f"Gitlab周提交信息-{tdy}.xlsx"), sheet_name=sheet1_name, startcol=0, index=False)


if __name__ == '__main__':
    export_to_excel()