通过 ODBC 将 Pandas 输出连接到 Excel 工作表 [英] Connect pandas output to excel sheet via ODBC

问题描述

下面的查询执行以下操作:通过 ODBC 连接到 SQL Server 数据库，运行 SQL 脚本(许多查询以;"分隔)，为两个特定查询结果创建两个数据框，然后将它们导出到其中的两个选项卡一个 Excel 工作簿:

The query below does the following: Connect to a SQL Server db via ODBC, run a SQL script (many queries separated by ";"), create two dataframes for two specific query results, and then export them to two tabs within an excel workbook:

import pyodbc
import pandas as pd
import time

name= 'ouput' + str(time.strftime("%Y-%b-%d__%H_%M_%S",time.localtime()))
print ("Connecting via ODBC")

conn = pyodbc.connect('DSN=Server DB Prod', autocommit=True)

print ("Connected!\n")

inputdir = 'H:\\Queries\\ADS'

#for script in os.listdir(inputdir):
with open(inputdir+'\\' + 'query' +'.sql','r') as inserts:
    sqlScript = inserts.read()
    for statement in sqlScript.split(';'):
        with conn.cursor() as cur:
            cur.execute(statement)

query1="Select * from #leadership"
data1=pd.read_sql_query(query1, conn).sort_values(['channel','terr_code'], ascending=[0,1]).reset_index(drop=True)
#print(data1.head(n=100))
query2="Select * from #ml"
data2=pd.read_sql_query(query2, conn).sort_values(['channel','terr_code','client_name'], ascending=[0,1,1]).reset_index(drop=True)

print('query finished')
conn.close()

writer = pd.ExcelWriter(name+ '.xlsx')
data1.to_excel(writer,'Leadership Summary')
data2.to_excel(writer,'ML Detail')
writer.save()

print("Results were succesfully exported")

但是，我希望能够通过 ODBC 连接到现有的 excel 文件，以便动态更新我的工作簿的选项卡而不丢失格式和图形 - 以实现真正的自动化.任何其他允许相同的解决方案肯定会奏效.

However, I am hoping to instead be able to connect to an existing excel file via ODBC in order to update the tabs of my workbook dynamically and not lose the formatting and graphs- to allow real automation. Any other solution that would allow the same will certainly work.

背景:我正在尝试自动化我在 SQL Server 中运行查询(通过 Python)的过程，并让输出更新现有 Excel 工作表的选项卡 - 我正在考虑通过 ODBC 连接.该工作表具有特定的格式，以及基于数据构建的公式+图表.

Background: I am trying to automate a process where I run a query in SQL Server (via Python), and have the output update the tabs of an existing excel worksheet- I was thinking by connecting via ODBC. That worksheet has specific formatting, and formulas+graphs built based on the data.

注意:我没有写入权限，只能读取，所以我无法通过 ODBC 将最终"SQL 表连接到 excel.我正在从 Python 中的其他来源进行一些额外的数据混合(未显示)，因此无法通过 ODBC 将 SQL 查询连接到 excel.

Note: I dont have write permission, only read, so I cant connect a "final" SQL table to excel via ODBC. I am doing some additional data blending from other sources in Python (not shown), so connecting a SQL query to excel via ODBC will not work.

非常感谢任何帮助.

推荐答案

最好的策略是访问 Excel 工作簿并使用其工具而不是外部工具，以保持所有其他对象不变.因此，请考虑使用 win32com 客户端，然后您可以在其中访问 Excel 对象库，例如其 CopyFromRecordset 方法.

The best strategy is to access the Excel workbook and use its facilities instead of external tools in order to leave all other objects untouched. Hence, consider the win32com client where you can then access the Excel object library such as its CopyFromRecordset method.

使用 Window 的 ADODB API，可以使用 ODBC 连接.此外，不需要 pandas，因为工作表对象用于保存数据.注意:此解决方案仅适用于 Windows 计算机.

And instead of pyodbc as database API, use Window's ADODB API which can use ODBC connections. Also, there is no need for pandas as worksheet objects are used to hold data. NOTE: this solution only works on Windows machines.

import win32com.client as win32

try:
    # INITIALIZE OBJECTS
    xlapp = win32.gencache.EnsureDispatch('Excel.Application')
    ado_conn = win32.gencache.EnsureDispatch('ADODB.Connection')
    ado_rst = win32.gencache.EnsureDispatch('ADODB.Recordset')

    # OPEN CONNECTION
    ado_conn.Open('DSN=Server DB Prod')

    # RUN QUERIES
    with open(inputdir+'\\' + 'query' +'.sql','r') as inserts:
        sqlScript = inserts.read()
        for statement in sqlScript.split(';'):
            ado_conn.Execute(statement)

    # OPEN WORKBOOK AND UPDATE SHEETS
    xlwb = xlapp.Workbooks.Open(r'C:\Full\Path\To\Workbook.xlsx')

    ls = xlwb.Worksheets('Leadership Summary') 
    ls.Cells.ClearContents()
    ado_rst.Open("Select * from #leadership", ado_conn)
    for i in range(ado_rst.Fields.Count):
       ls.Cells(1, i+1).Value = ado_rst.Fields(i).Name     # COLUMNS
    ls.Range("A2").CopyFromRecordset(ado_rst)              # DATA ROWS
    ado_rst.Close()

    ml = xlwb.Worksheets('ML Detail')
    ml.Cells.ClearContents()
    ado_rst.Open("Select * from #ml", ado_conn)         
    for i in range(ado_rst.Fields.Count): 
       ml.Cells(1, i+1).Value = ado_rst.Fields(i).Name     # COLUMNS
    ml.Range("A2").CopyFromRecordset(ado_rst)              # DATA ROWS
    ado_rst.Close()

    ado_conn.Close()
    xlapp.Visible = True        # OPENS WORKBOOK WITH ABOVE CHANGES TO SCREEN

except Exception as e:
    print(e)

finally:
    # RELEASE RESOURCES
    ls = None; ml = None
    ado_rst = None; ado_conn = None
    xlwb = None; xlapp = None

希望这能揭穿那些认为 VBA(也是一种 COM 接口语言)是 Excel 唯一编码语言的人！

这篇关于通过 ODBC 将 Pandas 输出连接到 Excel 工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！