无法使用cx-Oracle插入Unicode [英] Cannot Insert Unicode Using cx-Oracle

问题描述

我在将unicode插入Oracle模式中时遇到问题，我认为数据库是Oracle 11g实例，但目前还不确定.我在OS X 10.6.8上使用python 2.6.1(这是python的系统版本)，并且正在使用从sourceforge.net下载的cx-Oracle驱动程序模块版本5.1，并将其构建并安装到virtualenv 1.6.1实例中网站包可见.我的脚本如下

I am having an issue inserting unicode into an Oracle schema, I think the database is an Oracle 11g instance but am not certain at this point. I'm using python 2.6.1 on OS X 10.6.8 (this is the system verison of python) and am using the cx-Oracle driver module version 5.1 downloaded from sourceforge.net, built and installed to a virtualenv 1.6.1 instance with site packages visible. My script is as follows

  import cx_Oracle

  connection = cx_Oracle.connect(
      "<name>/<password>@<host>/<service-name>"
      )
  cursor = connection.cursor()
  result = cursor.execute(u"create table UNICODE_TEST (id NUMBER(6), text NCLOB not NULL)")

  raw_text = open("test.txt",'r').read()
  if isinstance(raw_text,str):
      raw_text = raw_text.decode("utf_8")

  statement = u"insert into UNICODE_TEST (id, text) values (1,'%s')" % raw_text
  result = cursor.execute(statement)

我创建一个连接，创建游标，执行一个语句以创建一个具有ID和文本字段(类型为NUMBER和NCLOB)的测试表. 我打开一个文件，其中包含我所知道的以UTF-8编码的文本，然后将字符串解码为unicode. 在unicode字符串中创建一个插入语句并执行该语句，结果是此错误.

I create a connection, create the cursor, execute a statment to create a test table with an id and text field of types NUMBER and NCLOB. I open a file containing what I know to be text encoded in UTF-8, decode the string to unicode. Create an insert statment in a unicode string and execute that statement, and the result is this error.

  Traceback (most recent call last):
    File "unicode-test.py", line 19, in <module>
      result = cursor.execute(statement)
  UnicodeEncodeError: 'ascii' codec can't encode character u'\u2122' in position 170: ordinal not in range(128)

某事正在尝试将我的语句编码为ASCII，然后再将其插入Oracle模式.因此，我开始四处寻找以更好地了解cx-Oracle如何处理unicode，并在我从sourceforge.net下载的cx-Oracle源代码的HISTORY.txt中找到了这一点.

Something is trying to encode my statement as ASCII before inserting it into the Oracle schema. So I started hunting around looking to better understand how cx-Oracle handles unicode and found this in the HISTORY.txt of the cx-Oracle source I downloaded from sourceforge.net

从5.0.4更改为5.1
1)取消对UNICODE模式的支持，并且 允许Unicode传入 可以在任何地方传递字符串.这意味着字符串将 使用NLS_LANG环境的值传递给Oracle Python 3.x中的变量.这样做消除了很多问题 使用UNICODE模式发现的文件，并删除了不必要的文件 Python 2.x中的限制:无法在连接字符串中使用Unicode 或SQL语句，例如. ...

Changes from 5.0.4 to 5.1
1) Remove support for UNICODE mode and permit Unicode to be passed through in everywhere a string may be passed in. This means that strings will be passed through to Oracle using the value of the NLS_LANG environment variable in Python 3.x as well. Doing this eliminated a bunch of problems that were discovered by using UNICODE mode and also removed an unnecessary restriction in Python 2.x that Unicode could not be used in connect strings or SQL statements, for example. ...

我的假设是将NLS_LANG环境变量设置为'ascii'或其他等效变量，因此我尝试将NLS_LANG设置为'AL32UTF8'，我认为这是unicode的正确值，并在创建连接之前设置新值.

My assumption is that the NLS_LANG environment variable is set to 'ascii' or some equivalent, so I try setting NLS_LANG to 'AL32UTF8' which I believe is the correct value for unicode, and set the new value before creating my connection.

  os.environ["NLS_LANG"] = "AL32UTF8"
  connection = cx_Oracle.connect(
      "<user>/<password>@<host>/<service-name>"
      )
  cursor = connection.cursor()
  ...

但是我得到这个错误.

  Traceback (most recent call last):
    File "unicode-test.py", line 11, in <module>
      "<user>/<password>@<host>/<service-name>"
  cx_Oracle.DatabaseError: ORA-12705: Cannot access NLS data files or invalid environment specified

所以看起来我无法篡改NLS_LANG值.

So it looks like I cannot tamper with the NLS_LANG value.

这是我到目前为止的问题.我是否缺少诸如错误的列类型之类的简单内容? cx-Oracle驱动程序有问题吗?构建cx-Oracle模块时是否需要设置"WITH_UNICODE"环境变量，我该怎么做? Oracle实例有问题吗?我对Oracle的经验很少，也从未与Oracle和python一起工作过.我已经花了两天的时间解决这个问题，并希望在与DBA小组合作之前能更好地了解这个问题.

Here are my questions as of now. Am I missing something simple like an incorrect column type? Is the problem with the cx-Oracle driver? Do I need to set the "WITH_UNICODE" environment variable when building the cx-Oracle module and how would I do that? Is the issue with the Oracle instance? I have little experience with Oracle and have never worked with Oracle and python together. I've spend two days working on this issue and would like a better understanding of what the issue is before I go to the DBA group with.

谢谢

推荐答案

设置环境变量是正确的方法，但是"AL32UTF8"不是NLS_LANG的正确值.要获得在您的Oracle实例中使用的NLS_LANG的正确值，请执行

Setting environment variable is the right way, but "AL32UTF8" is not the right value for NLS_LANG. To get the right value of the NLS_LANG used in your instance of Oracle, execute

SELECT USERENV ('language') FROM DUAL  

这篇关于无法使用cx-Oracle插入Unicode的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！