Python从SQL Server取数存CSV：输入框传值后空文件问题求助

问题概述 #

通过pymssql连接SQL Server的Leaf数据库开发GUI程序，输入Customer Code参数执行查询并导出CSV，但程序无报错却生成空文件，无数据输出。

核心问题与修复方案 #

1. Entry组件对象获取错误

原代码中customer_box = Entry(window, width=50).grid(row = 10, column =1)会将grid()方法的返回值（None）赋值给customer_box，导致后续无法获取输入框内容。需拆分创建与布局代码：

# 先创建Entry对象，再调用grid布局
customer_box = Entry(window, width=50)
customer_box.grid(row = 10, column =1)

2. SQL参数绑定与IN子句写法错误

原SQL语句中Customer_code IN('*values')的写法不符合pymssql参数绑定规则，且未正确传递输入的客户代码：

• pymssql使用%s作为参数占位符
• 若支持多客户代码输入（逗号分隔），需拆分输入内容并生成对应数量的占位符
• 正确获取输入框文本：customer_box.get()

修改后的SQL语句与参数处理：

def download():
    # 获取输入的客户代码，按逗号拆分（支持多个代码输入）
    input_codes = customer_box.get().strip().split(',')
    # 生成对应数量的占位符
    placeholders = ', '.join(['%s'] * len(input_codes))
    
    sql_query = """
        SELECT DISTINCT 
            customer_code, customer_name, channel_description, category, brand, 
            brandform, subbrandform_name, SUM(retailing) AS sales, 
            MONTH(Document_Date) AS Month, YEAR(Document_Date) AS Year 
        FROM <tablename> 
        WHERE document_date BETWEEN '2022-6-1' AND '2022-6-30' 
          AND Customer_code IN ({}) 
        GROUP BY 
            customer_code, customer_name, channel_description, category, brand, 
            brandform, subbrandform_name, MONTH(Document_Date), YEAR(Document_Date)
    """.format(placeholders)
    
    # 执行查询，传入拆分后的参数列表
    mycursor.execute(sql_query, input_codes)
    result = mycursor.fetchall()
    
    # 若有结果再生成CSV，避免空文件
    if result:
        # 添加列名到DataFrame，提升CSV可读性
        columns = [desc[0] for desc in mycursor.description]
        df = pd.DataFrame(result, columns=columns)
        df.to_csv(r'C:\Users\User\Desktop\Output\CustomerWiseData.csv', index=False)
    else:
        # 可添加提示：无匹配数据
        print("无符合条件的数据")

3. 其他优化点

• 确保<tablename>替换为实际的数据库表名
• 添加结果为空的判断，避免生成无意义的空CSV
• 为DataFrame添加列名，让导出的CSV更易读

修改后的完整代码 #

import pymssql
import pandas as pd
from tkinter import Tk, Label, Entry, Button, mainloop

# 数据库连接
conn = pymssql.connect(
    host=r'192.*.*.*',
    user=r'server\Administrator',  # 修正原代码'sever'拼写错误为'server'
    password=r'***',
    database='Leaf'
)
mycursor = conn.cursor()

# GUI窗口初始化
window = Tk()

def close():
    window.destroy()
    # 关闭数据库连接
    conn.close()

# 界面元素
label_title = Label(window, text="Enter Customer Code", font="TimesRoman 20")
label_title.grid(row=0, column=1)

label1 = Label(window, text="Customer Code", font="TimesRoman 13")
label1.grid(row=10, column=0, padx=10, sticky="w")

customer_box = Entry(window, width=50)
customer_box.grid(row=10, column=1)

def download():
    input_codes = customer_box.get().strip().split(',')
    if not input_codes:
        print("请输入客户代码")
        return
    
    placeholders = ', '.join(['%s'] * len(input_codes))
    sql_query = """
        SELECT DISTINCT 
            customer_code, customer_name, channel_description, category, brand, 
            brandform, subbrandform_name, SUM(retailing) AS sales, 
            MONTH(Document_Date) AS Month, YEAR(Document_Date) AS Year 
        FROM <tablename> 
        WHERE document_date BETWEEN '2022-6-1' AND '2022-6-30' 
          AND Customer_code IN ({}) 
        GROUP BY 
            customer_code, customer_name, channel_description, category, brand, 
            brandform, subbrandform_name, MONTH(Document_Date), YEAR(Document_Date)
    """.format(placeholders)
    
    try:
        mycursor.execute(sql_query, input_codes)
        result = mycursor.fetchall()
        if result:
            columns = [desc[0] for desc in mycursor.description]
            df = pd.DataFrame(result, columns=columns)
            df.to_csv(r'C:\Users\User\Desktop\Output\CustomerWiseData.csv', index=False)
            print("数据导出成功")
        else:
            print("无匹配的数据")
    except Exception as e:
        print(f"查询出错: {str(e)}")

# 按钮
but_download = Button(window, text="Download", font="TimesRoman 13", command=download)
but_download.grid(row=15, column=0, padx=10, pady=20, ipadx=20)

but_exit = Button(window, text="Exit", font="TimesRoman 13", command=close)
but_exit.grid(row=15, column=1, padx=10, pady=20, ipadx=20)

mainloop()

额外注意事项 #

• 修正了原代码中user=r'sever\Administrator'的拼写错误（sever→server）
• 添加了异常捕获，便于排查潜在错误
• 程序退出时关闭数据库连接，避免资源泄漏

内容的提问来源于stack exchange，提问作者Sathiyam Medicals