如何从SQL数据库的XLSX文件导出多个表和列出口使用python(How to export fr

我想用我现在已经创建了三个查询每次查询都对某些特定数据的SELECT命令查询导出从SQL数据库中的多张XLSX文件中的所有我现在需要我想从SQL数据库与一个XLSX文件导出，但包含三个张新我如何从SQL数据库中的数据导出为XLSX文件，但只有一个表，这是我的代码

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文件中的一张纸上只

我现在已经创建了三个查询，每个查询应该在三个不同的表

#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

现在我需要从MyDatabase的这个三个查询三个表来导出一个XLSX文件，该文件的每个查询的表

最后一件事，现在我希望把列在导出XLSX文件，但与改变他们的名字，只是添加颜色，此列，更改文本样式，以及如何重新命名，而不是默认名称的工作表名称

我希望这将是很清楚

Answer 1:

您可以使用熊猫xlsxwriter，就像这样：

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已准备就绪类型：

writer1.save()

保存它。

如果您想格式化数据帧的头部，你应该输入：

import pandas.io.formats.excel

然后键入：

pandas.io.formats.excel.header_style

为了格式的Excel文件，你可以探索openpyxl模块（我没有现成的解决方案），或者你可以导入win32com.client和格式片材VBA宏（这是很容易和有效的方式）。

记住，你需要编辑之前保存您的作家这样（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)

为方便重命名列，您可以使用下面的代码：

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

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

文章来源: How to export from sql database an xlsx file with multiple sheet and exporting columns using python