Selecting columns that have accented names using cx_Oracle

Question

I have a similar problem to the one that Didina Deen had more than 1 year ago (Handling accents in Oracle from Python). I need to obtain information from an Oracle table with column names that contain accents. The table has many columns, so I would like to filter in the query. Right now I have to do a 'Select *' and take the +100 columns, to take only the column that I need by specifying its position.

import cx_Oracle

my_query = "SELECT descripción_oferta FROM big_table WHERE IDENTIFICADOR = 'XXXXXX'"
dsn = cx_Oracle.makedsn(oracle_host, oracle_port, service_name=oracle_servicename)
con = cx_Oracle.connect(oracle_user, oracle_password, dsn)
cur.execute(my_query)

UnicodeEncodeError: 'ascii' codec can't encode character '\xf3' in position 180: ordinal not in range(128)

I have access to the table for sure, I can select other columns that don't have accents, so it must be this. I have tried with 'utf-8', 'latin1', ... encodings, without success.

In case you wonder, I am not the great mind that had the idea of giving columns names with accents...

The OP in that question seem to have solved their problem, have you tried that? https://stackoverflow.com/a/40016537/7998591 — Kaushik Nayak, Jul 20 '18 at 10:06

score 0 · Answer 1 · answered Jul 21 '18 at 16:21

The simplest way to deal with this is to specify the encoding when creating the connection, as follows:

conn = cx_Oracle.connect(oracle_user, oracle_password, dsn, encoding="UTF-8")

I just created a table with the same column name you specified and with encoding specified as noted above, it works fine. If not, you get the error you saw.

Selecting columns that have accented names using cx_Oracle

1 Answers1