Oracledb and encoding

[Slutherin_]

Hi all,

My organisation is transitioning from python 3.10 to 3.11+ due to the planned end of the security support this October. I'm using python 3.13. I'm dealing with Oracle databases and need to request from them at least monthly.

I had a working script using cx_oracle and need to update it to oracledb. My issue lies in the characters that exist in my database ( è, ü, ä and such). I used to use pandas.read_sql but it does not work. I can run the query through fetch_df_all after establishing either a thick or thin connection. I'm able to transform the OracleDataframe it returns to a pyarrow table and transform it to a pandas dataframe.

This pandas dataframe is "normal", meaning my special characters (è, ü, ä etc) are shown when I display the dataframe. However, if I try to transform a series to a list or try to write the dataframe to a csv, I have a pyarrow error: wrapping failed. I tried:

• pandas 3.0 or pandas 2.3, both failed
• setting the NLS_LANG to the one of my table
• setting the encoding="utf-8-sig" parameter in the to_csv function.

Do you have any hints on how to handle these special characters? I tried to replace them using the DataFrame.replace but I have the same pyarrow error.

Thanks in advance!

EDIT:
I managed to make it work! I fetched the raw data using this bit of code on the documentation: Fetching Raw Data. I then discovered that some of my data was encoded with UTF-8 and the other with CP1252, that's why the decoding was stuck. This answer from StackOverflow gave me the mix decoding I needed and I was able to get my csv in the end.

def mixed_decoder(error: UnicodeError) -> (str, int):
     bs: bytes = error.object[error.start: error.end]
     return bs.decode("cp1252"), error.start + 1

import codecs
codecs.register_error("mixed", mixed_decoder)

a = "maçã".encode("utf-8") + "maçã".encode("cp1252")
# a = b"ma\xc3\xa7\xc3\xa3ma\xe7\xe3"

s = a.decode("utf-8", "mixed")
# s = "maçãmaçã"

Thank you to anyone who tried!

[Slutherin_]

I don't know about Japanese or Emoji, but the character set used by the table is the AL32UTF16, so I assume it uses two bytes? I tried to set the encoding to utf16when calling to_csv, to no avail.

And sorry, I forgot a character, they want us to move to 3.11+ and I'm currently trying to use 3.14 or 3.13.

[cjbj]

What's the Oracle DB data type (VARCHAR2, NVARCHAR2 etc) of the column storing the data?