cx_Oracle and UTF8

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[i],"encode"):
t.append(row[i].encode("utf8"))
else:
t.append(row[i])
print t

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

Harald

re: cx_Oracle and UTF8

Harald Armin Massa wrote:
[color=blue]
> 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[i],"encode"):
> t.append(row[i].encode("utf8"))
> else:
> t.append(row[i])
> print t
>
> Guess I am to much accustomed to postgresql which just allows "set
> client_encoding='utf8'...[/color]

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

re: cx_Oracle and UTF8

Dietz,

thank you for your answer.
[color=blue]
> It's called NLS (national language support),
>and it is like a locale-setting in python/C. I'm too lazy to google right[/color]

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

re: cx_Oracle and UTF8

Harald Armin Massa wrote:[color=blue]
> Dietz,
>
> thank you for your answer.
>[color=green]
>>It's called NLS (national language support),
>>and it is like a locale-setting in python/C. I'm too lazy to google right[/color]
>
> 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= [...][/color]

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

re: cx_Oracle and UTF8

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