CODEWITHSUNDEEP
pythonpandasoracle-databasesqlalchemycx-oracle
Biplab1985
Biplab1985

Reputation: 151

Writing to Oracle: TypeError: expecting string or bytes object

I have been breaking my head on this. I am trying to push 65000+ rows with 51 columns to oracle DB but i end up receiving a type error. is there a way i can find out on which column this error is coming from so that i can debug.

Another question - Can a Datatype "Object" in python dataframe be read a 'Number' Dtype in Oracle?

Traceback (most recent call last):

  File "c:\users\so-go- activating strategic people capability - deliverable files\ finance\codes-to_use\s1_3_supply_forecasting_input_revamped.py", line 160, in <module>
    hcar.to_sql('HISTORICAL_HCAR', engine, if_exists='append', index=False,schema='HIM_PA_EXTERN_PROD_FIN',dtype=dtyp)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\pandas\core\generic.py", line 2605, in to_sql
    sql.to_sql(

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\pandas\io\sql.py", line 589, in to_sql
    pandas_sql.to_sql(

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\pandas\io\sql.py", line 1398, in to_sql
    table.insert(chunksize, method=method)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\pandas\io\sql.py", line 830, in insert
    exec_insert(conn, keys, chunk_iter)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\pandas\io\sql.py", line 747, in _execute_insert
    conn.execute(self.table.insert(), data)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
    return meth(self, multiparams, params)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\engine\base.py", line 1514, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\engine\base.py", line 1256, in _execute_context
    self.dialect.do_executemany(

  File "C:\ProgramData\Anaconda3-2020.11\lib\site-packages\sqlalchemy\dialects\oracle\cx_oracle.py", line 1182, in do_executemany
    cursor.executemany(statement, parameters)

TypeError: expecting string or bytes object

Upvotes: 2

Views: 2917

Answers (1)

kfinity
kfinity

Reputation: 9091

Unfortunately, Oracle won't tell you which column is causing the error. So this is a Python / cx_Oracle question, not really an Oracle one. And I assume when you say "dataframe" that you mean a Pandas dataframe, and not PySpark/Dask/Veux/etc.

There's several similar questions about this error with Pandas dataframes. The issue is usually that Pandas dataframe columns have a dtype, but the rows don't all have to match that type - object columns will allow different types in each row.

# example - an int, a float, and a str in the same column
pd.DataFrame([12, np.NaN, 'hi'], columns=['ABC'])

When you (or sqlalchemy) use executemany(), all the rows have to have the same matching set of column types.

You can check the types in a single column by using:

df['ABC'].map(type)

And so you can check all the columns in a dataframe at once with something like:

df.applymap(type).nunique()

Which shows the number of types that each column contains. Any column > 1 will probably cause this error. Fix it using df['ABC'].astype(str) or df['ABC'].fillna('') before sending to Oracle.

Upvotes: 4

Related Questions