python - 如何将 SQL 存储过程读入 pandas 数据帧？

Question

谢谢大家的建议。在调用我的函数之前添加self.解决了这个问题。我现在遇到了一个不同的问题，即我使用的存储过程没有正确读入 pandas 数据帧。我没有收到任何错误，tkinter 窗口像它应该那样弹出，但是当 PDF 生成时，行中没有数据，只有列名和我写的其他格式。

我添加print(df)到代码中以检查从数据框读取数据到 PDF 是否存在问题，但print(df)仅返回Empty DataFrame。

from tkinter import *
import pyodbc
import pandas as pd
from reportlab.lib import colors
from reportlab.platypus import *
from reportlab.lib import styles
from reportlab.lib.units import inch

# Create connection 
server = 'XXXXXXX' 
database = 'XXXXXXX' 
username = 'XXXXXXXX' 
password = 'XXXXXXXXX' 

try:
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password+'')
except:
    raise NameError('unable to connect')

#save stored procedure to a global variable
storedProc = 'EXEC [presentation].[pdf_project] '

elements = []

class NewPDF:
    def __init__(self):
        window = tk.Tk()
        window.title("Form Example")
        window = tk.Frame(window)
        window.grid(column=0,row=0, sticky=(tk.N,tk.W,tk.E,tk.S))
        window.columnconfigure(0, weight = 1)
        window.rowconfigure(0, weight = 1)
        window.pack(pady = 100, padx = 100)

        self.tkvar = tk.StringVar(window)
    
        choices = {'2021','2020','2019','2018'}
        self.tkvar.set('2021')
        
        popupMenu = tk.OptionMenu(window, self.tkvar, *choices)
        tk.Label(window, text = "Pick Year").grid(row = 1, column = 1)
        popupMenu.grid(row = 1, column = 2)
        
        tk.Label(window, text = "Customer").grid(row = 2, column = 1)

        self.e1 = tk.Entry(window)
        self.e1.grid(row = 2, column = 2)

        self.param_year = self.tkvar.get()
        self.param_cust = str(self.e1.get())

        B = tk.Button(window, text = "Make PDF", command=self.make_df_and_pdf()).grid(row = 3, column = 1)

        self.tkvar.trace('w', self.change_dropdown())

        window.mainloop()


    def change_dropdown(self, *args):
        print(args)

    def make_df_and_pdf(self):
        #param_year = self.tkvar.get()
        #param_cust = str(self.e1.get())
        params = "'" + self.param_cust + "'," + self.param_year + ""
        querystring = storedProc + params

        df = pd.read_sql_query(querystring, cnxn)
        lista = [df.columns[:,].values.astype(str).tolist()] + df.values.tolist()
        #cust_list = (df['CUSTOMER'].unique())
        #cust_list = cust_list.tolist()

        #print(df)

        styles = getSampleStyleSheet()

        ts = [('ALIGN', (1,1), (-1,1), 'LEFT'),
        ('BOX', (0,0), (3,0), 2, colors.red),
        ('FONT', (0,0), (-1,0), 'Times-Bold'),
        ('GRID', (0,1), (-1,-1), 0.5, colors.grey)] 

        n_table = Table(lista, colWidths = (1.5*inch, 1.5*inch, 1.5*inch, 1.5*inch, 1.5*inch), repeatRows = 1)
        table_style = TableStyle(ts)
        n_table.setStyle(table_style)

        PATH_OUT = "Desktop"
        doc = SimpleDocTemplate(PATH_OUT + 'UserInputPDF_Test.pdf')
        elements.append(Paragraph("CustomerTAT", styles['Title']))
        elements.append(n_table)

        doc.build(elements)




NewPDF()

编辑：存储过程在 SQL 中按其应有的方式运行。这是我的存储过程的代码：

USE [XXXXX]
GO
/****** Object:  StoredProcedure [presentation].[pdf_project]    Script Date: 6/22/2021 4:31:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      XXXXXX
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [presentation].[pdf_project] 
    -- Add the parameters for the stored procedure here
    @CustomerName varchar(50) = '', 
    @Year int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT CUSTOMER, [TAT Whole Days], [SHIP DATE], YEAR([SHIP DATE]) AS YEAR,
    CASE
        WHEN MONTH([SHIP DATE]) IN (1,2,3) THEN 'Q1'
        WHEN MONTH([SHIP DATE]) IN (4,5,6) THEN 'Q2'
        WHEN MONTH([SHIP DATE]) IN (7,8,9) THEN 'Q3'
        ELSE 'Q4'
    END AS QUARTER
    FROM presentation.CustomerTAT
    WHERE (YEAR([SHIP DATE]) = @Year or YEAR([SHIP DATE]) = @Year) 
    AND CUSTOMER = @CustomerName
END

标签： pythonsqlpandasdataframepyodbc