1

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

Christopher Jones
11.1k7 gold badges32 silver badges65 bronze badges
asked Dec 16, 2020 at 23:00
7
  • Did you CHARACTER SET the oracle database? Commented Dec 16, 2020 at 23:32
  • No, I didn't. Code works well with python2.7, but I have to use python3.6 from now onwards. Commented Dec 16, 2020 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'... Commented Dec 17, 2020 at 0:38
  • 1
    Have you got NLS_LANG set? Or used 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. Commented Dec 17, 2020 at 1:38
  • 1
    Does the following solution work for you? Answer to how to use charset and encoding in create_engine of SQLAlchemy (to create pandas dataframe)? Commented Dec 17, 2020 at 16:42

1 Answer 1

1

(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"
 }
)
answered Dec 18, 2020 at 0:30
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.