使用Python cx_Oracle导出Oracle数据至Excel：非法字符及性能优化求助

一、解决IllegalCharacterError错误 #

错误提示中的ALTER SESSION SET CONTAINER=EWP0342_SAD_OAS_RHI属于数据内容中包含的字符串，openpyxl对工作表内容有字符限制（比如控制字符、部分特殊字符），可通过清理数据中的非法字符解决：

1. 定义字符清理函数，移除openpyxl不允许的控制字符：

import re

def clean_illegal_chars(value):
    if isinstance(value, str):
        # 移除ASCII控制字符（保留制表符、换行符、回车符）
        return re.sub(r'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]', '', value)
    return value

2. 将清理函数应用到整个DataFrame：

# 读取数据后、写入Excel前执行
data2 = data2.applymap(clean_illegal_chars)

3. 执行Excel写入操作：

data2.to_excel(os.getcwd()+'/reports/RHI_ewp22.xlsx', index=False, engine="openpyxl")

二、优化15万+条记录的提取速度 #

1. 避免SELECT *，只查询需要的列 #

减少不必要的数据传输，将Select *替换为具体列名，比如：

query2 = "Select EVENT_TIMESTAMP, USERNAME, SQL_TEXT from unified_audit_trail where EVENT_TIMESTAMP >= TO_DATE(:from_date,'dd-mm-yyyy')"

2. 使用绑定变量替代字符串拼接 #

原代码的f-string拼接存在SQL注入风险，且Oracle无法缓存执行计划，改用绑定变量提升查询性能：

query2 = "Select ... from unified_audit_trail where EVENT_TIMESTAMP >= TO_DATE(:from_date,'dd-mm-yyyy')"
data2 = pd.read_sql(query2, connection, params={'from_date': from_date})

3. 调整cx_Oracle游标数组大小 #

设置cursor.arraysize为较大值（如10000），让每次从数据库提取更多数据，减少网络交互次数：

connection = cx_Oracle.connect("your_connection_string")
cursor = connection.cursor()
cursor.arraysize = 10000  # 批量提取大小

4. 分块读取并写入Excel #

避免一次性加载15万条数据到内存，使用chunksize参数分块处理，同时降低内存占用：

import pandas as pd

output_path = os.getcwd()+'/reports/RHI_ewp22.xlsx'
writer = pd.ExcelWriter(output_path, engine="openpyxl")

# 分块读取数据，每块10000条
for idx, chunk in enumerate(pd.read_sql(query2, connection, params={'from_date': from_date}, chunksize=10000)):
    # 清理当前块的非法字符
    chunk = chunk.applymap(clean_illegal_chars)
    # 仅在第一块写入表头
    chunk.to_excel(writer, index=False, header=(idx == 0))

writer.close()

5. 确保查询字段有索引 #

检查EVENT_TIMESTAMP字段是否创建索引，若未创建，可在Oracle中执行：

CREATE INDEX idx_unified_audit_event_ts ON unified_audit_trail(EVENT_TIMESTAMP);

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