Python中SQL查询转DataFrame效率低下的优化方案问询

这种转换耗时远高于查询的情况是否正常？ #

不正常。正常情况下SQL查询耗时应占总时间的大头，数据转DataFrame的开销通常远低于查询耗时。你遇到的问题，大概率是手动用cursor.fetchall()加载数据再构造DataFrame的方式效率低下，尤其当返回数据量较大时，会产生额外的内存拷贝和类型转换开销。

优化方案 #

1. 使用pandas内置的read_sql_query直接读取 #

pandas的read_sql_query针对数据库读取做了底层优化，比手动fetch数据再构造DataFrame高效得多，还能自动处理列名与数据类型推断的优化。修改后的代码如下：

import pandas as pd
import pyodbc
import time

login_info = f"""
    Driver={{IBM DB2 ODBC DRIVER - IBMDBCL1}};
    HostName=xxx;
    Port=xxx;
    Security=xxx;
    Database=xxx;
    UID={xxx};
    PWD={xxx};
"""

conn = pyodbc.connect(login_info)

def execute_sql(query):
    query_start_time = time.time()
    # 直接用read_sql_query读取并返回DataFrame
    df = pd.read_sql_query(query, conn)
    # 统一列名为小写
    df.columns = df.columns.str.lower()
    
    query_rt = "--- Query Runtime: %s seconds ---" % (time.time() - query_start_time)
    total_rt = "--- Total Runtime: %s seconds ---" % (time.time() - query_start_time)
    
    print(query_rt)
    print(total_rt)
    return df

loss_query = '''
select * from xxx limit 1
'''

loss_df = execute_sql(loss_query)

2. 分批读取数据（针对超大数据量） #

如果查询返回的数据量极大，fetchall()一次性加载所有数据到内存会导致内存压力过大，进而拖慢转换速度。可以改用fetchmany()分批读取，再逐步拼接DataFrame：

def execute_sql(query, batch_size=10000):
    query_start_time = time.time()
    cursor = conn.cursor()
    cursor.execute(query)
    
    cols = [c[0].lower() for c in cursor.description]
    query_rt = "--- Query Runtime: %s seconds ---" % (time.time() - query_start_time)
    
    df_list = []
    while True:
        # 分批读取数据
        batch_data = cursor.fetchmany(batch_size)
        if not batch_data:
            break
        df_list.append(pd.DataFrame(batch_data, columns=cols))
    
    # 合并所有批次的DataFrame
    df = pd.concat(df_list, ignore_index=True)
    
    total_rt = "--- Total Runtime: %s seconds ---" % (time.time() - query_start_time)
    print(query_rt)
    print(total_rt)
    return df

3. 手动指定数据类型，避免自动推断开销 #

pandas自动推断数据类型会消耗额外时间，尤其是包含大量复杂类型（如DECIMAL、超长文本）的数据集。可以提前指定列的类型，减少类型推断的开销：

# 示例：根据实际列定义指定类型
dtype_map = {
    'id': 'int32',
    'amount': 'float32',
    'description': 'string'
}

# 使用read_sql_query时指定dtype
df = pd.read_sql_query(query, conn, dtype=dtype_map)
df.columns = df.columns.str.lower()

4. 更换为IBM官方DB2驱动 #

pyodbc是通用ODBC驱动，IBM官方的ibm_db_dbi驱动针对DB2做了深度优化，能提升数据传输和转换的效率。安装命令：pip install ibm_db ibm_db_dbi，代码修改如下：

import ibm_db_dbi

# 替换连接方式
conn = ibm_db_dbi.connect(
    "DATABASE=xxx;HOSTNAME=xxx;PORT=xxx;SECURITY=xxx;UID=xxx;PWD=xxx;",
    "", ""
)

# 后续读取逻辑和read_sql_query一致
df = pd.read_sql_query(query, conn)
df.columns = df.columns.str.lower()

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