使用pyodbc从SQL Server取数存入Pandas过慢的优化咨询

我帮你梳理几个实用的优化方案，亲测能解决这类小数据集却加载慢的问题：

1. 手动构建DataFrame（最直接的提速方法） #

既然你已经发现fetchall()是耗时大头，那我们可以绕开pyodbc默认的结果处理逻辑，用numpy直接转换结果再喂给pandas——numpy的数组操作比原生Python列表高效得多。

另外记得把字符串拼接查询改成参数化查询，既安全又能让数据库复用执行计划：

import pyodbc
import pandas as pd
import numpy as np
import time

cnxn = pyodbc.connect("DSN=ISTPRD02; Trusted_Connection=yes;")
ontem = '20180521'
# 参数化查询，用?占位符避免SQL注入
query = """SELECT LOJA, COUNT(DISTINCT RA) 
           FROM VENDAS_CONTRATO(NOLOCK) 
           WHERE DT_RETIRADA_RA = ? AND SITUACAO IN ('ABERTO', 'FECHADO') 
           GROUP BY LOJA"""

start = time.time()
crsr = cnxn.cursor()
crsr.execute(query, (ontem,))
# 从游标描述里提取列名
columns = [col[0] for col in crsr.description]
# 转成numpy数组再构建DataFrame
data = np.array(crsr.fetchall())
ra_ontem = pd.DataFrame(data, columns=columns)
end = time.time()
print("Tempo: ", end - start)

2. 用SQLAlchemy作为中间层 #

Pandas和SQLAlchemy的集成经过专门优化，有时候比直接用pyodbc连接更高效，尤其是在结果集的转换环节：

首先安装SQLAlchemy（如果还没装）：

pip install sqlalchemy

然后修改代码：

import pandas as pd
from sqlalchemy import create_engine
import time

# 构建SQLAlchemy连接字符串（基于你的DSN）
conn_str = "mssql+pyodbc://@ISTPRD02?Trusted_Connection=yes"
engine = create_engine(conn_str)

ontem = '20180521'
query = """SELECT LOJA, COUNT(DISTINCT RA) 
           FROM VENDAS_CONTRATO(NOLOCK) 
           WHERE DT_RETIRADA_RA = ? AND SITUACAO IN ('ABERTO', 'FECHADO') 
           GROUP BY LOJA"""

start = time.time()
# 用read_sql并传入参数
ra_ontem = pd.read_sql(query, engine, params=(ontem,))
end = time.time()
print("Tempo: ", end - start)

3. 调整PyODBC的游标参数 #

PyODBC默认的cursorarraysize很小（通常是1），这意味着它会逐行读取结果，哪怕数据已经在本地缓存。调大这个参数可以让一次性读取更多数据，减少本地处理的开销：

import pyodbc
import pandas as pd
import time

cnxn = pyodbc.connect("DSN=ISTPRD02; Trusted_Connection=yes;")
# 设置游标每次读取的行数，这里设为1000（远超你的189行，不影响）
cnxn.setattr(pyodbc.SQL_ATTR_CURSOR_ARRAY_SIZE, 1000)

ontem = '20180521'
query = """SELECT LOJA, COUNT(DISTINCT RA) 
           FROM VENDAS_CONTRATO(NOLOCK) 
           WHERE DT_RETIRADA_RA = ? AND SITUACAO IN ('ABERTO', 'FECHADO') 
           GROUP BY LOJA"""

start = time.time()
ra_ontem = pd.read_sql_query(query, cnxn, params=(ontem,))
end = time.time()
print("Tempo: ", end - start)

为什么这些方法有效？ #

• 手动转numpy：避开了pyodbc将结果转成Python原生列表的额外开销，pandas处理numpy数组的速度天生更快。
• SQLAlchemy：它的结果集处理逻辑专门做了优化，和pandas的适配性更好。
• 调整游标参数：减少了本地循环处理的次数，一次性读取全部数据，大幅降低fetchall()的耗时。

你可以挨个测试这些方案，大概率能把加载时间从20+秒降到1秒以内。

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