PyODBC批量查询数据库表时脚本中途终止问题排查

我正在编写一个循环程序，遍历数据库中的29张表，将查询结果存储为DataFrame后写入Excel工作簿。但执行时脚本总会中途停止，停止时机不固定，有时仅执行1次查询，通常执行5-6次后就终止，请问可能的原因是什么？

原代码 #

import pyodbc
import pandas as pd

conn = pyodbc.connect("DSN=XXXX")
conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-16-le')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-16-le')
conn.setencoding(encoding='utf-16-le')
TABLES = ['Table1', 'Table2', ..., 'Table29']

def query_multiple_tables(tables: list[str]) -> list[pd.DataFrame]:
    dataframes = []
    for table in tables:
        QUERY = f"SELECT * FROM [db].[dbo].[{table}]"
        col_crsr = conn.cursor()
        data_crsr = conn.cursor()
        
        cols = [row.column_name for row in col_crsr.columns(table=f"{table}")]
        data = pd.DataFrame.from_records(data_crsr.execute(QUERY).fetchmany(20), columns=cols)
        
        print(f"Extracting table: {table}")
        dataframes.append(data)

        col_crsr.close()
        data_crsr.close()
    
    return dataframes

def write_to_excel(dataframes: list[pd.DataFrame]) -> None:
    
    try:
        with pd.ExcelWriter("Data.xlsx", mode='a', if_sheet_exists='replace') as writer:
            for i, dataframe in enumerate(dataframes):
                dataframe.to_excel(writer, sheet_name=f'{i}')
                print(f"Append successful for: {i}")
    except FileNotFoundError:
        with pd.ExcelWriter("Data.xlsx", mode='w') as writer:
            for i, dataframe in enumerate(dataframes):
                dataframe.to_excel(writer, sheet_name=f'{i}')
                print(f"Initial write successful for: {i}")

data = query_multiple_tables(TABLES)
write_to_excel(data)

可能的原因及修复方案 #

• 数据库连接资源耗尽
  每次循环创建两个独立游标，即便调用了close()，pyodbc的游标销毁可能不及时，多次循环后会耗尽连接池资源，导致数据库拒绝新请求。另外全局连接未设置超时，长时间无响应会触发数据库端的连接超时。
  修复：复用单个游标，减少资源创建；给连接添加超时参数（比如timeout=30）；脚本结束后主动关闭连接。

• 未捕获的异常导致脚本崩溃
  循环中的查询、列名获取操作没有异常捕获机制，一旦某张表出现权限问题、表名错误、数据解码失败等情况，脚本会直接终止，表现为“中途停止”。
  修复：在循环内添加try-except块，捕获pyodbc.Error和通用异常，打印错误信息后继续执行后续表的处理。

• 编码解码错误
  强制设置utf-16-le编码，如果某张表的字段包含该编码无法解析的字符，会触发隐性解码错误，直接终止脚本。
  修复：解码时添加errors='replace'参数忽略错误字符，或尝试改用更通用的utf-8编码。

• Excel写入阶段的资源冲突
  如果Excel文件被其他程序占用，写入时会触发异常终止脚本；另外mode='a'追加模式在某些版本的pandas中存在兼容性问题。
  修复：确保Excel文件未被打开；写入时也添加异常捕获，避免影响整个流程。

优化后的代码示例 #

import pyodbc
import pandas as pd

# 添加连接超时，避免长时间无响应
conn = pyodbc.connect("DSN=XXXX", timeout=30)
# 解码时添加错误处理，避免编码问题终止脚本
conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-16-le', errors='replace')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-16-le', errors='replace')
conn.setencoding(encoding='utf-16-le')
TABLES = ['Table1', 'Table2', ..., 'Table29']

def query_multiple_tables(tables: list[str]) -> list[pd.DataFrame]:
    dataframes = []
    # 复用单个游标，减少资源开销
    crsr = conn.cursor()
    for table in tables:
        try:
            QUERY = f"SELECT * FROM [db].[dbo].[{table}]"
            # 获取列名
            cols = [row.column_name for row in crsr.columns(table=f"{table}")]
            # 执行查询并读取数据
            crsr.execute(QUERY)
            data = pd.DataFrame.from_records(crsr.fetchmany(20), columns=cols)
            
            print(f"Extracting table: {table}")
            dataframes.append(data)
        except pyodbc.Error as e:
            print(f"数据库操作错误（表{table}）: {e}")
        except Exception as e:
            print(f"未知错误（表{table}）: {e}")
    # 关闭游标
    crsr.close()
    return dataframes

def write_to_excel(dataframes: list[pd.DataFrame]) -> None:
    try:
        with pd.ExcelWriter("Data.xlsx", mode='a', if_sheet_exists='replace') as writer:
            for idx, dataframe in enumerate(dataframes):
                # 用表名作为sheet名，更直观
                dataframe.to_excel(writer, sheet_name=f'Sheet_{TABLES[idx]}')
                print(f"追加成功: {TABLES[idx]}")
    except FileNotFoundError:
        with pd.ExcelWriter("Data.xlsx", mode='w') as writer:
            for idx, dataframe in enumerate(dataframes):
                dataframe.to_excel(writer, sheet_name=f'Sheet_{TABLES[idx]}')
                print(f"初始写入成功: {TABLES[idx]}")
    except Exception as e:
        print(f"Excel写入错误: {e}")

data = query_multiple_tables(TABLES)
write_to_excel(data)
# 脚本结束后主动关闭连接
conn.close()

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