python-3.x - 我们可以在使用熊猫数据框 python 时在 SQL 查询中传递参数吗

Question

我必须编程计算每个客户的联系人数量的“日期异常”，每个客户的合同数量各不相同。通过以下 3 个步骤，我正在为每个联系人正确计算日期异常，最后附加所有数据框并将其写入 excel 文件，其中包含表单（根据合同）和所有合同的合并表单。 代码如下： Step1: # 连接数据库

import pandas as pd
import numpy  as np
import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=XXXXXXXX;'
                      'Database=XXXXXXX;'
                      'Trusted_Connection=yes;')

Step 2: sql_query = pd.read_sql_query("Select DISTINCT  ContractName , clientcode, TypeOfFile (CASE WHEN SentToBBDate > BBLoadedDate then 'ERROR - SENT DATE GREATER THAN LOAD DATE' WHEN ReceivedDate > SentToBBDate then 'ERROR - RECEIVED DATE GREATER THAN SENT DATE'  WHEN ReceivedDate is NULL then 'ERROR - FILE NOT RECEIVED' ELSE 'NORMAL' END) AS Error_Message from DataDashboard Where  **ContractName like '%***%**' AND  ClientCode LIKE '%AAA%' Order by TypeOfFile", conn)
first = sql_query.to_csv("firstDashboard.csv")


Step 3:
first= pd.read_csv("C:\\Users\\pd\\Desktop\\firstDashboard.csv")
first = Cigna.iloc[: , 1:]
first.drop(first[(first['ERROR_MESSAGE'] == 'NORMAL')].index, inplace=True)
first.to_excel("first_DateReport.xlsx", index=False)

第 2 步和第 3 步我在同一个笔记本（Jupyter）中重复每个合同。并附加所有数据框并将结果写入一个excel文件的不同工作表中的excel文件合同中。 这是代码

writer = pd.ExcelWriter("C:\\Users\\pd\\Desktop\\Consolidated_DateReport.xlsx")
sheets_in_writer=['Conso_DateReport', 'First_DateReport','Second_DateReport']
data_frame_for_writer=[Appenddf,First, Second]

for i,j in zip(data_frame_for_writer,sheets_in_writer):
    i.to_excel(writer,j,index=False)


### Assign WorkBook
workbook=writer.book
# Add a header format
header_format = workbook.add_format({'bold': True, 'size':11,
                                                      'valign': 'top','fg_color': '#c7e7ff','border': 1})


for worksheet in writer.sheets.values():
            row_count = len(df.index)
            column_count = len(df.columns)
            
### Apply same format on each sheet being saved
for i,j in zip(data_frame_for_writer,sheets_in_writer):
    for col_num, value in enumerate(i.columns.values):
        writer.sheets[j].write(0, col_num, value, header_format)
        writer.sheets[j].freeze_panes(1,0)
        writer.sheets[j].autofilter(0, 0, row_count-1, column_count-2) 
        writer.sheets[j].set_column(0, 7 - 1, 25)
        
writer.save()

如何使用循环/函数传递 Step2 和 3 中的值并避免编写相同的代码次数（11 次）。在第 2 步中，ContractName 应该在 SQL 查询中得到更改（其中 ContractNmae 像 '%AAA%）并且在第 3 步中它应该得到更改，例如第二个然后第三个到 11。任何帮助将不胜感激。

标签： python-3.x