Unicode issues with Pandas and SQLAlchemy using Oracle Database

Question

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

No, I didn't. Code works well with python2.7, but I have to use python3.6 from now onwards. — N9909, Dec 16 '20 at 23:54
Try forcing the encoding when reading the CSV. Try with encoding='utf-8', encoding='latin1', encoding='iso-8859-1' or encoding='cp1252'... — Paulo Marques, Dec 17 '20 at 00:38
sure. I have tested with encoding='utf-8', but no luck. I 'll try with other ones and let you know. — N9909, Dec 17 '20 at 01:00
Have you got NLS_LANG set? Or used https://stackoverflow.com/a/53738233/4799035 (probably with UTF-8 for general usage)? Or maybe you can upgrade to cx_Oracle 8 which uses UTF-8 by default. — Christopher Jones, Dec 17 '20 at 01:38
Hi Chris, I have passed the connct_args while creating the engine with UTF-8. "encoding": "UTF-18","nencoding": "UTF-8".Its worked.Thanks for your help ! — N9909, Dec 17 '20 at 02:31
Does the following solution work for you? [Answer to how to use `charset` and `encoding` in `create_engine` of SQLAlchemy (to create pandas dataframe)?](https://stackoverflow.com/a/49169477/10521959) — Yaakov Bressler, Dec 17 '20 at 16:42

score 1 · Accepted Answer · answered Dec 18 '20 at 00:30

(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"
    }
)

Unicode issues with Pandas and SQLAlchemy using Oracle Database

1 Answers1