How to update a part of oracle database table using pandas df and cx_Oracle cursor()?

Question

Suppose my Oracle db table myTable has 10 columns and 10 rows of data with data types like below:

   Column         Data_Type
1  Calendarday      Date         *Primary key
2  col1              ...
.                     .
.                     .
.                     .
10 Flag             Number(1,0)

and suppose my pandas dataframe df has 2 columns and 3 rows of data:

Date          Bit
2000-12-21     1
2000-12-22     1
2000-12-23     1

Dtypes are: datetime64[ns] and int64.

Basically i want to update the Flag column of myTable with values from Bit in df where Calendarday is equal to Date. So here, i just want to overwrite 3 Flag records and not the entire column or the entire table. The code i am trying is:

cur = con.cursor()
query = '''insert into myTable (Calendarday, Flag) values (%s, %s);'''
cur.executemany(query, df[['Date', 'Bit']].values.tolist())
con.commit()

But executing this is giving me error:

cur.executemany(query, df[['Date', 'Bit']].values.tolist())
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

I really don't know how to fix it. Do I need to specify the position of columns in myTable or could it be because of date format conversion issue between pandas and Oracle? Please skip suggesting sqlalchemy solutions. Need to do this the cursor way only. Any help would be sincerely appreciated.

Kaushik Nayak · Accepted Answer · 2019-07-11 08:26:52Z

3

You don't want an Insert but a Bulk update,

First convert your Dataframe to a list of tuples. Then run your update query by passing bind parameters.

data = [tuple(x) for x in df[['Bit', 'Date']].values]

query="""UPDATE mytable SET flag = :bit 
          where calendarday = TO_DATE(:dt,'yyyy-mm-dd') 
      """
cur.executemany(query, data)

answered Jul 11, 2019 at 8:26

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prachi Over a year ago

Code is working fine but it still didn't update values inside the db table? @Kaushik

Kaushik Nayak Over a year ago

@Prachi : It does I've tested it. Please check if the values match in your table. Also, do conn.commit()

Sithija Piyuman Thewa Hettige Over a year ago

I have a similar query but i get this error -> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'Action'. im using execute , not executemany. what can be the cause ?

Collectives™ on Stack Overflow

How to update a part of oracle database table using pandas df and cx_Oracle cursor()?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related