如何在Python Pandas DataFrame中使用Oracle序列SQ_test插入数据

问题描述 #

我在Oracle数据库中已创建名为SQ_test的序列，希望将Python Pandas中的DataFrame数据插入Oracle时，用该序列填充DataFrame的ID列。现有DataFrame结构如下：

目前已编写获取序列值的函数，但不清楚如何将该序列应用到DataFrame中。获取序列值的函数如下：

def get_change_id():
    id = cursor.var(cx_Oracle.NUMBER)
    sql_query_sq = """
        BEGIN 
            :next_id := SQ_TEST.nextval; 
        END; 
    """
    cursor.execute(sql_query_sq, {"next_id":id})
    change_id = id.getvalue()
    return change_id

解决方案 #

方法一：逐行替换ID值 #

直接利用Pandas的apply方法，对DataFrame的每一行调用序列生成函数替换原有ID：

# 替换DataFrame中的ID列
df['ID'] = df.apply(lambda _: get_change_id(), axis=1)

注意：这种方式会逐行与数据库交互，当DataFrame数据量较大时性能较差，仅适合小数据集场景。

方法二：批量获取序列值（推荐） #

针对大数据量场景，建议一次性获取与DataFrame行数相等的序列值，再整体赋值给ID列，减少数据库交互次数：

def get_batch_sequence_values(n):
    # 一次获取n个连续的序列值
    sql = """
        SELECT SQ_TEST.nextval FROM dual CONNECT BY level <= :n
    """
    cursor.execute(sql, {"n": n})
    # 提取所有序列值并返回列表
    return [row[0] for row in cursor.fetchall()]

# 批量获取序列值并替换ID列
df['ID'] = get_batch_sequence_values(len(df))

插入Oracle数据库示例 #

替换完ID列后，可使用以下方式将DataFrame数据插入Oracle：

# 方式1：使用sqlalchemy配合to_sql（需提前安装sqlalchemy）
from sqlalchemy import create_engine

# 构建数据库连接引擎，替换为你的数据库信息
engine = create_engine('oracle+cx_oracle://用户名:密码@主机地址:端口/服务名')
# 将数据追加到目标表中
df.to_sql('目标表名', engine, if_exists='append', index=False)

# 方式2：使用cx_Oracle批量插入
sql_insert = """
    INSERT INTO 目标表名 (ID, Name, NN) VALUES (:1, :2, :3)
"""
# 将DataFrame转换为列表元组格式
data = [tuple(row) for row in df.values]
cursor.executemany(sql_insert, data)
# 提交事务
connection.commit()

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