如何从 sql 数据库导出带有多个工作表的 xlsx 文件并使用 python 导出列 [英] How to export from sql database an xlsx file with multiple sheet and exporting columns using python

问题描述

我想使用查询从 sql 数据库中导出包含多张表的 xlsx 文件现在我创建了三个查询每个查询都有一个 SELECT 顺序来处理一些我现在需要的特定数据我想用一个 xlsx 文件从 sql 数据库导出但包含三张我新的如何将数据从 sql 数据库导出为 xlsx 文件但是只有一张纸，这是我的代码

I want to export xlsx file with multiple sheets from sql database by using queries Now I have created a three queries every query have a SELECT order for some specific data all I need now I want to export from sql database with one xlsx file but contains three sheets I new how to export data from sql database as an xlsx file but with one sheet only and this is my code

from sqlalchemy import create_engine
import pandas as pd
import os
import csv
import MySQLdb
from sqlalchemy import types, create_engine
from xlsxwriter.workbook import Workbook


# MySQL Connection
MYSQL_USER      = 'root'
MYSQL_PASSWORD  = 'xxxxxxxxxx'
MYSQL_HOST_IP   = '127.0.0.1'
MYSQL_PORT      = 3306
MYSQL_DATABASE  = 'mydb'
govtracker_table = 'govtracker' # table you want to save

con = MySQLdb.connect(user=MYSQL_USER, passwd=MYSQL_PASSWORD, 
host=MYSQL_HOST_IP, db=MYSQL_DATABASE)
cursor = con.cursor()

export_frm_db_query_ssv = "SELECT id, site_code, site_name, region, 
site_type, tac_name, dt_readiness, rfs, " \
    "rfs_date, acceptance_date_opt, acceptance_date_plan, signed_sites, " 
\
    "as_built_date, as_built_status, cluster_name, 
type_standalone_colocated, " \
    "installed_type_standalone_colocated, status, pending, pending_status, 
" \
                      "problematic_details, ets_tac, region_r, 
sf6_signed_date, " \
                      "sf6_signed_comment, comment_history, on_air_owner, 
pp_owner, " \
                      "report_comment, hu_opt_area_owner, planning_owner, 
po_number, " \
                      "trigger_date, as_built_status_tr FROM %s;" % 
govtracker_table

workbook = Workbook('SSV Progress Tracker_23 May.xlsx')
sheet = workbook.add_worksheet()
for r, row in enumerate(cursor.fetchall()):
for c, col in enumerate(row):
    sheet.write(r, c, col)
workbook.close()

前面的代码只是将我的数据库中的数据提取到一张表中的 xlsx 文件

the previous code is just extracting data from my database to xlsx file in one sheet only

现在我创建了三个查询，每个查询应该在三个不同的工作表中

now I have created three queries every query should be in three different sheets

#SHR Phase 1 sheet SQL query
export_frm_db_query_shr_ph1 = "SELECT id, site_code, site_name, region, 
site_type, tac_name, dt_readiness, rfs, " \
    "rfs_date, huawei_1st_submission_date, te_1st_submission_date, " \
                          "huawei_2nd_submission_date, 
te_2nd_submission_date, " \
                          "huawei_3rd_submission_date, 
te_3rd_submission_date, " \
                          "acceptance_date_opt, acceptance_date_plan, 
signed_sites, " \
    "as_built_date, as_built_status, date_dt, dt_status, " \
    "shr_status, dt_planned, integeration_status, comments_snags WHERE 
site_type = 'Existing' AND " \
                          "site_type = 'New' AND region = 'Cairo' AND 
region = 'Red sea & Sinai' " \
                          "FROM %s;" % govtracker_table

#SHR Phase 2 sheet SQL query
export_frm_db_query_shr_ph2 = "SELECT id, site_code, site_name, region, 
site_type, tac_name, dt_readiness, rfs, " \
    "rfs_date, huawei_1st_submission_date, te_1st_submission_date, " \
                          "huawei_2nd_submission_date, 
te_2nd_submission_date, " \
                          "huawei_3rd_submission_date, 
te_3rd_submission_date, " \
                          "acceptance_date_opt, acceptance_date_plan, 
signed_sites, " \
    "as_built_date, as_built_status, date_dt, dt_status, " \
    "shr_status, dt_planned, integeration_status, comments_snags WHERE 
site_type = 'Existing' AND " \
                          "site_type = 'New' AND region = 'Delta' " \
                          "FROM %s;" % govtracker_table

#SHR Phase 3 sheet SQL query
export_frm_db_query_shr_ph3 = "SELECT id, site_code, site_name, region, 
site_type, tac_name, dt_readiness, rfs, " \
    "rfs_date, huawei_1st_submission_date, te_1st_submission_date, " \
                          "huawei_2nd_submission_date, 
te_2nd_submission_date, " \
                          "huawei_3rd_submission_date, 
te_3rd_submission_date, " \
                          "acceptance_date_opt, acceptance_date_plan, 
signed_sites, " \
    "as_built_date, as_built_status, date_dt, dt_status, " \
    "shr_status, dt_planned, integeration_status, comments_snags WHERE 
site_type = 'Cow' " \
                          "site_type = 'Indoor' " \
                          "FROM %s;" % govtracker_table

现在我需要通过这三个查询在一个 xlsx 文件中的三个工作表中从我的数据库中导出，这是一个工作表中的每个查询

now I need to export from mydatabase by this three queries in three sheets in one xlsx file that's every query in a sheet

现在最后一件事我想将列放在导出的 xlsx 文件中，但要更改它们的名称，只需为此列添加颜色并更改文本样式以及如何重命名工作表名称而不是默认名称

final thing now I want to put columns in the exported xlsx file but with changing their names and just adding colors to this columns and change the text style and how to rename the sheet name instead of the default name

我希望这足够清楚

推荐答案

你可以使用pandas xlsxwriter，像这样:

You can use pandas xlsxwriter, like this:

writer1 = pd.ExcelWriter('xlsx file name', engine='xlsxwriter')
your_dataframe1['column based conditions'].to_excel(writer1, sheet_name='name of sheet 1', index=False, startrow=0)
your_dataframe2['column based conditions'].to_excel(writer1, sheet_name='name of sheet 2', index=False, startrow=0)
your_dataframe3.to_excel(writer1, sheet_name='name of sheet 3', index=False, startrow=0) #no conditions here

只要确保您的数据集是数据帧类型.当你的 excel 准备好时，输入:

Just make sure your data set is dataframe type. When your excel is ready type:

writer1.save()

保存它.

如果你想格式化数据帧头，你应该导入:

If you want to format dataframe header you should import:

import pandas.io.formats.excel

并输入:

pandas.io.formats.excel.header_style

要格式化excel文件，您可以探索openpyxl模块(我没有现成的解决方案)，或者您可以使用VBA宏导入win32com.client和格式表(这是非常简单有效的方法).

To format excel files you can explore openpyxl module (I don't have ready solutions) or you can import win32com.client and format sheet with VBA macro (this is pretty easy and efficent way).

记住你需要在编辑之前保存你的作者 (writer1.save())

Remember you need to save your writer before edit this way (writer1.save())

import win32com.client

workbook1 = xl.Workbooks.Open(Filename='file path' + 'file_name.xlsx', ReadOnly=1)
xl.Application.Run('PERSONAL.XLSB!personal macro name')
workbook1.Close(True)

要轻松重命名列，您可以使用以下代码:

To rename columns easily you can use following code:

dataframe = dataframe.rename(index=str, columns={'ID' : 'col2','CATEGORY' : 'col1','DATE' : 'col3'})

dataframe = dataframe[['col1','col2','col3']] 

这篇关于如何从 sql 数据库导出带有多个工作表的 xlsx 文件并使用 python 导出列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！