cx_Oracle and UTF8

[Harald Armin Massa]

Hello,

I am looking for a method to convince cx_Oracle and oracle to encode
it's replies in UTF8.

For the moment I have to...

cn=cx_Oracle.connect("user","password", "database")
cs=cn.Cursor()

cs.execute("select column1, column2, column3 from table")

for row in cs.fetchall():
t=[]
for i in range(0,len(row)):
if hasattr(row,"encode"):
t.append(row.encode("utf8"))
else:
t.append(row)
print t

Guess I am to much accustomed to postgresql which just allows "set
client_encoding='utf8'...

Harald

 

[Diez B. Roggisch]

Hello,

I am looking for a method to convince cx_Oracle and oracle to encode
it's replies in UTF8.

For the moment I have to...

cn=cx_Oracle.connect("user","password", "database")
cs=cn.Cursor()

cs.execute("select column1, column2, column3 from table")

for row in cs.fetchall():
t=[]
for i in range(0,len(row)):
if hasattr(row,"encode"):
t.append(row.encode("utf8"))
else:
t.append(row)
print t

Guess I am to much accustomed to postgresql which just allows "set
client_encoding='utf8'...

You can do that in Oracle, too. It's called NLS (national language support),
and it is like a locale-setting in python/C. I'm too lazy to google right
now, but you should be able to alter the current connection's session to
deliver strings in whatever encoding is supportde (utf-8 being amongst
these).

Diez

 

[Harald Armin Massa]

Dietz,

thank you for your answer.

It's called NLS (national language support),
and it is like a locale-setting in python/C. I'm too lazy to google right

Sad thing: I allready googled that and had to learn: you CAN definitely
change some parameters, that is sort order and language for error
messages with
alter session set NLSREGION and set NLSLANGUAGE

The only part about the charset is with NLSLANG, which could be set to
German_Germany.UTF8

BUT ... NLSLANG is no per-session parameter, not setable per alter
session, it needs to get set within the environment to make SQLPLUS
recognize it. (and, I do of course use python not sqlplus=

In another of the WWW I learned that NLSLANG has to be set on per
connection basis; not on per cursor / session basis; so my primary
suspect is cx_Oracle.Connection ... but those objects to not have a
visible method with any "encoding" in it.

Harald

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Dietz,

thank you for your answer.

It's called NLS (national language support),
and it is like a locale-setting in python/C. I'm too lazy to google right

Sad thing: I allready googled that and had to learn: you CAN definitely
change some parameters, that is sort order and language for error
messages with
alter session set NLSREGION and set NLSLANGUAGE

The only part about the charset is with NLSLANG, which could be set to
German_Germany.UTF8

BUT ... NLSLANG is no per-session parameter, not setable per alter
session, it needs to get set within the environment to make SQLPLUS
recognize it. (and, I do of course use python not sqlplus= [...]

This is handled by the OCI, which both your Pyhton interface and SQLPLUS
use. You should be able to do something like:

import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import cx_Oracle
con = cx_Oracle.connect("me/secret@tns")
cur = con.cursor()
cur.execute("select foo from bar")
print cur.fetchone()[0]

HTH,

-- Gerhard

 

[Harald Armin Massa]

Gerhard,

thanks, that

import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import cx_Oracle
con = cx_Oracle.connect("me/secret@tns")

really helped. At least now the query returns something encoded
differently. I dared not to believe that there is no "direct encoding
change api" without touching the environment.

Now all that is left is to find out if Oracle indeed has the same
opinion what UTF8 should be like.

Thank you very much,

Harald

 

[Gene Cash]

Gerhard,

thanks, that

import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import cx_Oracle
con = cx_Oracle.connect("me/secret@tns")

really helped. At least now the query returns something encoded
differently. I dared not to believe that there is no "direct encoding
change api" without touching the environment.

Nope. That's the method that the Oracle software itself uses... it
checks the environ var.

Now all that is left is to find out if Oracle indeed has the same
opinion what UTF8 should be like.

It should be. I've used this for Japanese-language customer names, and
according to them, it worked fine.

FYI: The first part before the period determines the language of prompts
and error messages. The second part determines data encoding.

-gc