pythoncursorexecutemanyora-01036

ORA-01036 error on executemany() oracledb in Python UPDATE query


During the execution of method executemany() the error "ORA-01036: illegal variable name/number arises". Execution of a similar method execute() with the same data is successful.

Сode that ends with an error:

update_data_df = read_docs_df[['CONTRACT_NUM', 'DOCID']].copy()
cur_upd = conn_dwh.cursor()
dataUpdate = [tuple(i) for i in update_data_df.values]
print(dataUpdate)
sqlTxt = "UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive SET DOCID = :2    WHERE CONTRACT_NUM = ':1'"
cur_upd.executemany(sqlTxt, dataUpdate)
updated_docs = cur_upd.rowcount
conn_dwh.commit()
cur_upd.close()

DatabaseError: ORA-01036: illegal variable name/number

print(dataUpdate) output: [('П/0097232-Д8', 7481640), ('П/0117840-Д2', 7131468), ('1/А-БПИ/Нск-Д1', 6819213), ('П/0082021-Д5', 7425529)]

Code worked successfully:

update_data_df = read_docs_df[['CONTRACT_NUM', 'DOCID']].copy()
cur_upd = conn_dwh.cursor()
updated_docs = 0
for row in update_data_df.itertuples():
    sqlTxt = f"UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive SET DOCID = {row.DOCID} WHERE CONTRACT_NUM = '{row.CONTRACT_NUM}'"
    print(sqlTxt)
    cur_upd.execute(sqlTxt)
    updated_docs += cur_upd.rowcount
conn_dwh.commit()
cur_upd.close()

Python 3.9 oracledb 1.4.1


Solution

  • UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive
    SET DOCID = :2
    WHERE CONTRACT_NUM = ':1'
    

    Is wrong because:

    Should be:

    # Swap the columns
    update_data_df = read_docs_df[['DOCID', 'CONTRACT_NUM']].copy()
    
    cur_upd = conn_dwh.cursor()
    dataUpdate = [tuple(i) for i in update_data_df.values]
    print(dataUpdate)
    
    # Don't quote the bind variables and put them in ascending order (or name them).
    sqlTxt = """
    UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive
    SET    DOCID = :1
    WHERE  CONTRACT_NUM = :2"""
    
    cur_upd.executemany(sqlTxt, dataUpdate)
    updated_docs = cur_upd.rowcount
    conn_dwh.commit()
    cur_upd.close()