Cx_Oracle UPDATE database table with Pandas dataframe input

user_10228923 0
Viewed 428

I have an issue with updating an existing table on the database with a pandas dataframe of values with multiple columns, but I only wanted to update the database with some of those columns, based on conditions of other columns.

An example:

import pandas as pd
import cx_Oracle

d = {'a': ['first', 'second'], 'b': [3, 4], 'c': ['2021-01-01', '2021-01-02'], 'd':[1.1,2.2], 'e':[5,6] }
df = pd.DataFrame(data=d)

#Update to the database
data = list(df.itertuples(index=False, name=None))
DBCONN = cx_Oracle.connect(db_username,db_password,dsn)
conn = DBCONN
cursor = conn.cursor()
query_add_data = """UPDATE my_table SET d = :d, c = :c WHERE a = :a AND b = :b"""
#updating the rows
cursor.executemany(query_add_data, data)
conn.commit()

I got the following error:

DatabaseError: ORA-01036: illegal variable name/number

Any ideas what I am doing wrong?

pandas oracle sql python
1 Answers

The solution was to prepare and subset my dataframe, and specify the column order of the dataframe to match exactly the order as my sql query. For example, I recreate my dataframe as before:

import pandas as pd
import cx_Oracle

d = {'a': ['first', 'second'], 'b': [3, 4], 'c': ['2021-01-01', '2021-01-02'], 'd':[1.1,2.2], 'e':[5,6] }
df = pd.DataFrame(data=d)

Column e is unused in the sql query, so I drop it.

df = df.drop(['e'], axis=1)

Then I set the column order to match the sql query below.

cols = ['d', 'c', 'a', 'b']
df = df[cols]

With the following same bit of code to update the database table:

data = list(df.itertuples(index=False, name=None))
DBCONN = cx_Oracle.connect(db_username,db_password,dsn)
conn = DBCONN
cursor = conn.cursor()
query_add_data = """UPDATE my_table SET d = :d, c = :c WHERE a = :a AND b = :b"""
#updating the rows
cursor.executemany(query_add_data, data)
conn.commit()

This executes fine without errors. It was not obvious to me and took me some time to figure this out. I hope it helps others.

user_102289230
Related