CODEWITHSUNDEEP
pythonpandasoracle-databasesqlalchemy
N9909
N9909

Reputation: 247

Unicode issues with Pandas and SQLAlchemy using Oracle Database

I'm trying to read csv file using pandas and write it into oracle database

import pandas as pd
import sqlalchmey types, create_engine

df = pd.read_csv("abc.csv")
df.dtypes
Name object
CName object
price float64

df.head(2)
Name    CName  price
Gary    ALS    13.0
John    Ülka   19.9

engine = create_engine('oracle://+ xxx + xxx + 'connection string')
connection = engine.connect()
df.to_sql(name='test-tbl', con=connection, index=False, if_exists='append')

 File "/apps/anaconda/4.6.14/envs/TST/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1468, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/apps/anaconda/4.6.14/envs/TST/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 154, in reraise
    raise value
  File "/apps/anaconda/4.6.14/envs/TST/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1224, in _execute_context
    cursor, statement, parameters, context
  File "/apps/anaconda/4.6.14/envs/TST/lib/python3.6/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 1080, in do_executemany
    cursor.executemany(statement, parameters)
UnicodeEncodeError: 'ascii' codec can't encode character '\xea' in position 23: ordinal not in range(128)

Currently I am using python3.6 to test the above code, seeing the errors in above.Is any one can help to fix this ?

package versions : pandas-1.0.3 cx_oracle-6.4.1 sqlalchemy-1.3.5

CSV file format in UTF-8

Upvotes: 1

Views: 635

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10711

(Copying the comments for visibility as the solution)

The solution was to use something like:

import cx_Oracle
e = create_engine(
    "oracle+cx_oracle://un:pw@connstr...",
    connect_args={
        "encoding": "UTF-8",
        "nencoding": "UTF-8"
    }
)

Upvotes: 1

Related Questions