Flask连接SQL Server制仪表盘时遇500错误及ValueError求助

问题现象 #

• 服务器返回500内部错误
• 触发ValueError: NaTType does not support timetuple
• 已确认数据无空值，移除日期列后问题仍存在

用户代码 #

import pandas as pd
import pyodbc
from flask import Flask, render_template, jsonify
app = Flask(__name__)

# SQL Server Connection Details
conn_str = (
    "DRIVER={SQL Server};"
    "SERVER=xyz;"
    "DATABASE=xyz;"
    "UID=xyz;"
    "PWD=xyz;"
)

# Fetch Data from SQL Server
def fetch_data():
    try:
        conn = pyodbc.connect(conn_str)
        query = """
            SELECT TicketDate, Technician, Open_Tickets, Closed_Tickets, Created_Today, Closed_Today, Created_Hourly
            FROM Technician_Ticket_Stats
        """
        df = pd.read_sql(query, conn)
        conn.close()
        # Debugging logs
        print("Fetched data successfully:")
        print(df.head()) 
        df['TicketDate'] = df['TicketDate'].astype(str)  # Convert date for JSON
        return df.to_dict(orient="records")
    except Exception as e:
        print("Error fetching data:", e)  
        return []
@app.route("/")
def index():
    return render_template("index.html")
@app.route("/get_data")
def get_data():
    try:
        data = fetch_data()
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
if __name__ == "__main__":
    app.run(host='127.0.0.1', port=8050, debug=True)

错误分析 #

NaTType错误说明pandas将TicketDate列解析出了缺失时间值（NaT），即使原SQL数据无空值，也可能是：

1. pyodbc驱动对SQL Server日期类型的解析异常
2. pandas读取时的隐性类型转换错误
3. astype(str)转换时，NaT无法被正常处理触发异常

解决方案 #

1. 预处理NaT值再转换 #

在将日期转为字符串前，先替换或过滤NaT：

# 替换NaT为空字符串或指定默认日期
df['TicketDate'] = df['TicketDate'].fillna('').astype(str)
# 或者直接删除含NaT的行
df = df.dropna(subset=['TicketDate'])
df['TicketDate'] = df['TicketDate'].astype(str)

2. SQL层直接返回字符串格式日期 #

绕开pandas的日期解析，让SQL Server直接输出字符串：

SELECT CONVERT(VARCHAR(10), TicketDate, 23) AS TicketDate,
       Technician, Open_Tickets, Closed_Tickets, Created_Today, Closed_Today, Created_Hourly
FROM Technician_Ticket_Stats

注：23是SQL Server的日期格式代码，对应YYYY-MM-DD，可根据需求调整。

3. 升级依赖并调整读取方式 #

• 升级pyodbc到最新版本：

pip install --upgrade pyodbc

• 替换pd.read_sql为pd.read_sql_query，部分场景下处理逻辑更稳定：

df = pd.read_sql_query(query, conn)

4. 完善错误捕获与资源管理 #

修改fetch_data函数，确保连接正确关闭，并添加调试信息定位NaT来源：

def fetch_data():
    conn = None
    try:
        conn = pyodbc.connect(conn_str)
        query = """
            SELECT TicketDate, Technician, Open_Tickets, Closed_Tickets, Created_Today, Closed_Today, Created_Hourly
            FROM Technician_Ticket_Stats
        """
        df = pd.read_sql(query, conn)
        print("Fetched data successfully:")
        print(df.head())
        # 打印NaT数量，确认问题来源
        print("NaT count in TicketDate:", df['TicketDate'].isna().sum())
        df['TicketDate'] = df['TicketDate'].fillna('').astype(str)
        return df.to_dict(orient="records")
    except Exception as e:
        print("Error fetching data:", e)
        return []
    finally:
        if conn:
            conn.close()

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