Python从Access导数据至Oracle时触发ORA-01036错误求助

cx_Oracle不支持ODBC风格的?占位符，它要求使用位置占位符（如:1、:2）或命名占位符（如:COD）。你当前用的?是pyodbc（Access连接）的占位符格式，Oracle无法识别，因此抛出ORA-01036错误。

将INSERT语句中的?替换为cx_Oracle支持的占位符格式即可，以下是修改后的完整代码：

import pyodbc
import cx_Oracle

# Set up the Microsoft Access connection
access_conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\MyFolder\ACC_BASE.MDB;'
    )
access_conn = pyodbc.connect(access_conn_str)

# Define the Oracle SQL connection string
oracle_conn_str = cx_Oracle.makedsn("MyConnection", "MyPort", "MySID")

# Create a connection to the Oracle SQL database
oracle_conn = cx_Oracle.connect(user="MyUser", password="MyPassword", dsn=oracle_conn_str)

# Create a cursor for each connection
access_cursor = access_conn.cursor()
oracle_cursor = oracle_conn.cursor()

# Execute the select statement to extract data from the Access table
access_cursor.execute('SELECT * FROM ACC_TABLE')

# Loop through the rows of the Access table and insert them into the Oracle SQL table
# 使用cx_Oracle支持的位置占位符 :1 :2 :3 :4
for row in access_cursor.fetchall():
    oracle_cursor.execute(
        'INSERT INTO ORACLE_TABLE (COD, LEV, AZET, HUES) VALUES (:1, :2, :3, :4)',
        [row[0], row[1], row[2], row[3]]
    )

# Commit the changes to the Oracle SQL table
oracle_conn.commit()

# Close the cursors and connections
access_cursor.close()
access_conn.close()
oracle_cursor.close()
oracle_conn.close()

如果待插入的数据量较大，逐行插入的效率极低，建议使用executemany进行批量插入，示例代码如下：

# 替换原有的逐行插入代码块
rows = access_cursor.fetchall()
# 批量插入所有数据
oracle_cursor.executemany(
    'INSERT INTO ORACLE_TABLE (COD, LEV, AZET, HUES) VALUES (:1, :2, :3, :4)',
    rows
)

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