Encoding issue with SqlAlchemy + cxOracle
Guilherme Menezes
I am having an encoding issue using Python 2.6.2, SqlAlchemy 0.6.5 and
cxOracle 5.0.4 to access an Oracle 11g database.
I am using the NLS_LANG=.AL32UTF8 enviroment variable. My table in
Oracle is correctly configured to accept Unicode.
First, I compiled cxOracle without the WITH_UNICODE flag (as
recommended by SqlAlchemy). I was passing data to SqlAlchemy as
unicode() objects. Everything worked without errors or warnings.
However, sometimes Oracle would complain that the string I was trying
to insert into a VARCHAR2 field was too big (> 4000), even when the
string size ( len(the_string.encode('utf-8')) ) was about 2300 bytes
(before SqlAlchemy). I used a sniffer to verify that the Oracle client
was sending two bytes for each character (even the ASCII ones),
instead of sending two bytes only for special characters.
I repeated this insertion experiment using cx_Oracle directly and
passing unicode() objects to it. Same thing: no errors or warnings,
and again the sniffer shown that the Oracle client was sending two
bytes per character. It seemed to me that cx_Oracle does not really
supports unicode() objects; it accepts them but it does not encode()
them to the correct encoding (as set in NLS_LANG variable).
However, when reading data from Oracle everything worked as expected
(i.e. someone was converting the UTF-16(?) string to UTF-8, probably
Oracle itself).
I tried two approaches to make the Oracle client send data in UTF-8
(not UTF-16):
1. Passing str() objects to SqlAlchemy. SqlAlchemy would then complain
in a warning:
* UnicodeWarning: Unicode equal comparison failed to convert both
arguments to Unicode - interpreting them as being unequal *
It worked, but it did not seem right, since I was receiving unicode()
objects and sending str() objetcs to SqlAlchemy. Moreover, I did not
know how things work inside SqlAlchemy, so I tried something else.
2. Compiling cx_Oracle with the WITH_UNICODE flag solved the problem
(utf-8 was sent to Oracle), but another warning was issued:
* SAWarning: cx_Oracle is compiled under Python 2.xx using the
WITH_UNICODE flag. Consider recompiling cx_Oracle without this flag,
which is in no way necessary for full support of Unicode. Otherwise,
all string-holding bind parameters must be explicitly typed using
SQLAlchemy's String type or one of its subtypes,or otherwise be passed
as Python unicode. Plain Python strings passed as bind parameters
will be silently corrupted by cx_Oracle. *
Right now I am using cx_Oracle compiled with WITH_UNICODE, everything
works, but this warning is worrying me. I tried to pass a str() object
in UTF-8 to SqlAlchemy, and the execution halted with an encoding
error (UnicodeDecodeError). In other words, cx_Oracle did not silently
corrupted the data (at least when special characters were sent).
I would like to know if anyone has a better (safer) solution to my
problem. Or am I safe enough by using the WITH_UNICODE flag and
passing only unicode() objects?
Thank you in advance.
Guilherme.
Michael Bayer
Regarding the passing of utf-8 vs. utf-16 and the differences in WITH_UNICODE mode, you should really email the cx_oracle list about that as WITH_UNICODE to my knowledge only has to do with the Python interpretation of arguments, not its interaction with OCI. If you email their list, make sure all code examples are purely derived from cx_oracle, to eliminate any doubt that each behavior is one of cx_oracle directly. WITH_UNICODE was not intended for general use in Python 2.xx. if your whole application really works with it, then there's no reason not to use it, its just that you'll always have to ensure that no non-Python unicode strings ever get sent to cx_oracle which can be fairly tedious. It sounds like a bug that cx_oracle would be expanding into a two-byte-per-character stream like that.
Its possible that calling setinputsizes() with cx_oracle.UNICODE may be the key to cx_oracle's behavior here. We call setinputsizes for every Oracle cursor but we currently exclude the string types from that list as it had some unwanted side effects.
Also note that VARCHAR2(4000) is measuring itself in bytes, not characters. For true Unicode support Oracle provides the NVARCHAR2 type, where the "length" specifies the number of characters, instead of bytes. Recent versions of Oracle also support the form VARCHAR2(4000 CHAR) which will similarly measure the column in terms of characters based on the databases encoding instead of bytes. Its worth investigating if using NVARCHAR2 causes cx_oracle, or OCI, to change its behavior.
>
> Thank you in advance.
>
> Guilherme.
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Guilherme Menezes
I will report the problem to the cx_Oracle list and see what they have to say.
Regards,
Guilherme.
Guilherme Menezes
It seems that cx_Oracle always sends data in UTF-16 if WITH_UNICODE is
unset and unicode() objects are passed.
Regards,
Guilherme.
On Wed, Nov 17, 2010 at 2:16 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>