如何用SQL、Python、Numpy、Pandas及ODBC提取超百万行数据

核心思路 #

利用SQL Server的OFFSET ... FETCH NEXT分页语法，每次获取10万行数据，循环执行直到累计达到100万行或无更多数据返回。同时优化数据收集方式，避免频繁调用numpy.append带来的性能损耗。

具体实现步骤 #

• 用分页SQL替代固定TOP查询，每次偏移已获取的行数，确保数据不重复、不遗漏
• 循环执行查询，用计数器跟踪累计行数，达到100万或无数据时自动停止
• 先用Python列表暂存数据，最后一次性转换为Numpy数组，提升效率
• 处理边界情况：当数据库总行数不足100万时，自动终止循环不报错

完整代码示例 #

import pyodbc
import numpy as np
import pandas as pd
import sys

# 数据库连接参数（请替换为实际值）
server = "your_server_address"
database = "your_database_name"
username = "your_username"
password = "your_password"

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server +
                      ';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD=' + password + ';Authentication=ActiveDirectoryInteractive')
cursor = cnxn.cursor()

batch_size = 100000
max_total_rows = 1000000
total_rows = 0
data_list = []  # 用列表暂存数据，比numpy.append更高效

with open('C:\\xampp\\htdocs\\dataout.txt', 'w') as f:
    sys.stdout = f
    while total_rows < max_total_rows:
        # 分页查询语句：跳过已获取的行数，获取下一批数据
        sql = f"""
            SELECT * FROM vYield
            ORDER BY Serial  -- 必须指定排序字段，保证分页顺序稳定
            OFFSET {total_rows} ROWS
            FETCH NEXT {batch_size} ROWS ONLY
        """
        cursor.execute(sql)
        row = cursor.fetchone()
        
        if not row:
            break  # 无更多数据，终止循环
        
        batch_count = 0
        while row:
            # 提取需要的三个字段
            data_list.append([row[27], row[8], row[19]])
            row = cursor.fetchone()
            batch_count += 1
        
        total_rows += batch_count
        print(f"已累计获取 {total_rows} 行数据")

# 转换为Numpy数组并按Serial排序
table_array = np.array(data_list)
table_array = table_array[table_array[:, 0].argsort()]

# 生成Pandas DataFrame
df = pd.DataFrame(table_array, columns=['Serial', 'Test', 'Fail'])

# 恢复标准输出（可选）
sys.stdout = sys.__stdout__

关键说明 #

• 分页排序：必须指定ORDER BY字段（示例中用Serial），否则每次分页的结果顺序可能混乱，导致数据重复或缺失
• 效率优化：Python列表收集数据的方式避免了numpy.append每次重新分配内存的开销，处理大量数据时速度更快
• 原SQL错误分析：你之前尝试的SQL存在语法错误（DECLARE块中SELECT COUNT(*)后缺少闭合分号），且TOP(@count)无法绕过单次10万行的限制，分页语法是更可靠的方案

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