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
UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive
SET DOCID = :2
WHERE CONTRACT_NUM = ':1'
Is wrong because:
':1'
is a string literal, and not a bind variable; and:2
is the first bind variable and :1
is the second bind variable so naming them the other way round does not swap which column they will reference so it is creating extra confusion; you need to swap the input columns and then you can name the bind variables in the appropriate order.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()