Python从SQL Server提取2亿行大数据遇连接超时问题求解

需从SQL Server提取超大规模数据集（超2亿行、数十列），当前实现代码如下：

from pyodbc import connect
from pandas import read_sql

def get_table(query, server,
              database="", driver="SQL Server Native Client 11.0"):
    cnxn_str = (
        "Driver={SQL Server Native Client 11.0};"
        "Server="+server+";"
        "Database="+database+";"
        "Trusted_Connection=yes;"
    )
    cnxn = connect(cnxn_str)
    return read_sql(query, cnxn)

该函数处理数十万行数据正常，但无法返回100万行以上数据，每次处理大数据集时返回如下错误（译自葡萄牙语）：

OperationalError: ('08S01', '[08S01] [Microsoft][SQL Server Native Client 11.0]TCP Provider: A connection attempt failed because a connected component did not respond correctly after a period of time.

注：本地代码采用Windows身份验证连接SQL Server，无需传入用户名或密码。已尝试使用chunksize参数，但结果相同，求高效解决方法？

高效解决方法 #

1. 升级ODBC驱动并优化连接参数 #

旧版SQL Server Native Client 11.0对大数据集支持有限，替换为最新的ODBC Driver 17 for SQL Server，同时在连接字符串中添加超时、数据包大小等优化参数：

from pyodbc import connect
from pandas import read_sql

def get_table(query, server,
              database="", driver="ODBC Driver 17 for SQL Server"):
    cnxn_str = (
        f"Driver={{{driver}}};"
        f"Server={server};"
        f"Database={database};"
        "Trusted_Connection=yes;"
        "Connection Timeout=300;"  # 延长连接超时时间至5分钟
        "Packet Size=32767;"       # 增大数据包大小，减少网络交互次数
        "AutoTranslate=no;"        # 关闭字符自动转换，提升传输性能
    )
    cnxn = connect(cnxn_str)
    # 设置游标超时时间
    cursor = cnxn.cursor()
    cursor.timeout = 300
    # 合理设置chunksize，平衡内存占用与请求次数
    return read_sql(query, cnxn, chunksize=100000)

2. 基于有序字段分批次查询 #

避免一次性请求全量数据，按表中有序字段（如自增ID、时间戳）分片，循环读取：

from pyodbc import connect
from pandas import read_sql, concat

def get_large_table(table_name, server, database="", batch_size=100000):
    cnxn_str = (
        "Driver={ODBC Driver 17 for SQL Server};"
        f"Server={server};"
        f"Database={database};"
        "Trusted_Connection=yes;"
        "Connection Timeout=300;"
    )
    cnxn = connect(cnxn_str)
    
    # 获取分片字段的首尾值（示例用id字段，可替换为时间戳等）
    min_max_query = f"SELECT MIN(id), MAX(id) FROM {table_name}"
    min_id, max_id = read_sql(min_max_query, cnxn).iloc[0]
    
    all_batches = []
    current_id = min_id
    while current_id <= max_id:
        end_id = current_id + batch_size - 1
        batch_query = f"SELECT * FROM {table_name} WHERE id BETWEEN {current_id} AND {end_id}"
        batch_data = read_sql(batch_query, cnxn)
        all_batches.append(batch_data)
        # 可选：每处理几批就写入文件，避免内存溢出
        # batch_data.to_csv(f"batch_{current_id}.csv", index=False, mode='a', header=False)
        current_id = end_id + 1
    
    cnxn.close()
    return concat(all_batches, ignore_index=True)

3. 用pyodbc直接批量读取，绕过pandas中间层 #

pandas的read_sql处理超大数据时存在额外开销，直接使用pyodbc游标分批fetch：

from pyodbc import connect
from pandas import DataFrame, concat

def fetch_large_data(query, server, database="", batch_size=100000):
    cnxn_str = (
        "Driver={ODBC Driver 17 for SQL Server};"
        f"Server={server};"
        f"Database={database};"
        "Trusted_Connection=yes;"
    )
    cnxn = connect(cnxn_str)
    cursor = cnxn.cursor()
    cursor.execute(query)
    
    # 获取列名
    columns = [desc[0] for desc in cursor.description]
    all_batches = []
    
    while True:
        batch = cursor.fetchmany(batch_size)
        if not batch:
            break
        df = DataFrame(batch, columns=columns)
        all_batches.append(df)
        # 可选：实时写入文件，降低内存压力
        # df.to_csv("large_data.csv", mode='a', header=False, index=False)
    
    cnxn.close()
    return concat(all_batches, ignore_index=True)

4. 数据库端直接导出（最高效方案） #

利用SQL Server自带的bcp命令直接将数据导出到本地文件，再用pandas读取文件：

1. 执行bcp命令（Windows命令行）：

bcp "SELECT col1, col2, col3 FROM your_table" queryout "D:\large_data.csv" -S your_server_name -d your_database -T -c -t, -r\n

参数说明：

• -T：使用Windows身份验证
• -c：以纯文本格式导出
• -t,：指定列分隔符为逗号
• -r\n：指定行分隔符为换行

2. pandas读取本地CSV：

from pandas import read_csv

# 分块读取，避免内存溢出
chunk_iter = read_csv("D:\large_data.csv", chunksize=100000)
all_data = concat(chunk_iter, ignore_index=True)

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