Unicode / cx_Oracle problem

[Diez B. Roggisch]

Richard Schulman schrieb:
>>>  cursor.execute("""INSERT INTO mean (mean_id,mean_eng_txt)
>>>      VALUES (:id,:mean)""",id=id,mean=mean)
>>> ...
>>>  "cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data
>>> type unicode"
>>>
>>> But when I try putting a codecs.BOM_UTF16_LE in various plausible
>>> places, I just end up generating different errors.
> 
> Diez:
>> Show us the alleged plausible places, and the different errors. 
>> Otherwise it's crystal ball time again.
> 
> More usefully, let's just try to fix the code above. Here's the error
> message I get:
> 
> NotSupportedError: Variable_TypeByValue(): unhandled data type unicode
> 
> Traceback (innermost last):
> 
> File "c:\pythonapps\LoadMeanToOra.py", line 1, in ?
>   # LoadMeanToOra reads a UTF-16LE input file one record at a time
> File "c:\pythonapps\LoadMeanToOra.py", line 23, in ?
>   cursor.execute("""INSERT INTO mean (mean_id,mean_eng_txt)
> 
> What I can't figure out is whether cx_Oracle is saying it can't handle
> Unicode for an Oracle nvarchar2 data type or whether it can handle the
> input but that it needs to be in a specific format that I'm not
> supplying.

What does

print repr(mean)

give you?

It _looks_ to me (don't have an orcacle available right now) as if it is 
  a unicode object. That you have to consider as some abstract string 
representation. Which means it has to be encoded in some way before sent 
over the wire. There might exist db-api bindings that can deal with 
them, by applying a default encoding or somehow figuring out what 
encoding the DB expects. But I don't see any references to unicode in 
pep 249, so I presume you can't rely on that - which seems to be the 
case here.

The oracle NLS is a sometimes tricky beast, as it sets the encoding it 
tries to be clever and assigns an existing connection some encoding, 
based on the users/machines locale. Which can yield unexpected results, 
such as "Dusseldorf" instead of "Düsseldorf" when querying a german city 
list with an english locale.

So - you have to figure out, what encoding your db-connection expects. 
You can do so by issuing some queries against the session tables I 
believe - I don't have my oracle resources at home, but googling will 
bring you there, the important oracle term is NLS.

Then you need to encode the unicode string before passing it - something 
like this:

mean = mean.encode("latin1")

That should help.

Diez