Error while inserting rows into db expecting string or bytes object

Question

I'm trying to insert a dataframe, derived from a csv, into an Oracle table and am getting the above message.

try:
    dfTuple = [tuple(x) for x in dfOrders.values]

    sqlTxt = 'INSERT INTO SCHEMA.MYTABLE\
                (Field1, Field2, Field3, Field4, Field5)\
                VALUES (:1, :2, :3, :4, :5)'
    
    cursor.executemany(sqlTxt, [x for x in dfTuple])

    rowCount = cursor.rowcount
    print("number of inserted rows =", rowCount)

    connection.commit()

except Exception as err:
    print('Error while inserting rows into db')
    print(err) 
finally:
    if(connection):
       cursor.close()
       connection.close()

I've tried all types of variations of ( { [ with VALUES and none seem to work. In the DB Fields 1-5 are INT, INT, VARCHAR, DATE, VARCHAR... do I need to somehow convert the dataframe (comes through as all objects) into each correct datatype first?

This is what dfTuple looks like, with the original dfOrders.dtypes showing all as an object:

Don't make us guess what the full error actually is. Please update the question and add the full error trace message. — John Gordon
– John Gordon, Commented Oct 4, 2023 at 18:55
That's the error. All it says is: Error while inserting rows into db expecting string or bytes object with no reference to a line or anything. — BP12
– BP12, Commented Oct 4, 2023 at 19:01
Oh, I see. That's because you're catching the exception and just printing the bare message, which does not display the full traceback. Please remove the try/except statements and just allow the exception to happen. — John Gordon
– John Gordon, Commented Oct 4, 2023 at 19:04
There's no need for the list comprehension [x for x in dfTuple]. Just use dfTuple. — Barmar
– Barmar, Commented Oct 4, 2023 at 19:11
The traceback goes to cursor.executemany (ty for the reminder to turn that part off). Unfortunately trying cursor.executemany(sqlTxt, dfTuple) yields the same result: TypeError: expecting string or bytes object — BP12
– BP12, Commented Oct 4, 2023 at 20:06

BP12 · Accepted Answer · 2023-10-06 19:17:31Z

0

Turns out the date field needed to be converted first, and all NaN's replaced with 0, after creating the dataframe in order to have the df inserted into the table:

dfOrders['OrderDate']= pd.to_datetime(dfOrders['OrderDate'], format='%Y-%m-%d') 
dfOrders = dfOrders.drop_duplicates()
dfOrders.replace(np.nan, 0, inplace=True)

answered Oct 6, 2023 at 19:17

BP12

6110 bronze badges

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

Collectives™ on Stack Overflow

Error while inserting rows into db expecting string or bytes object

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related