[cx-oracle-users] WITH_UNICODE in Python 2.x. Why ?

[Michael B. <mi...@zz...>]

First, a bug report involving WITH_UNICODE mode.   I only am familiar with this option as one of my users (i.e. of SQLAlchemy) insists he must use this mode in production.

cx_Oracle is generally pretty good at loudly rejecting non-unicode objects in this mode, unless you send across a string as a bind:

import cx_Oracle

conn = cx_Oracle.connect(
dsn = u'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.248.131)(PORT=1521)))(CONNECT_DATA=(SID=xe)))',
    password=u'tiger', 
    user=u'scott'
)

cursor = conn.cursor()

cursor.execute(u"select :name from dual", {'name':u'some name'})
result = cursor.fetchall()[0][0]
assert result == u'some name', result  # passes

cursor.execute(u"select :name from dual", {'name':'some name'})
result = cursor.fetchall()[0][0]
assert result == u'some name', result  # does not raise an exception.  Returns: 736F6D65206E616D65


In SQLAlchemy, we're going to ensure that a plain string never gets passed, but it still seems inconsistent and dangerous that cx_Oracle simply corrupts the data in some circumstances.  For this reason I'm planning on having SQLA issue a huge warning when the presence of this flag is detected, that they must never stray from the confines of SQLA's bind handling or fear the wrath of odd hex-encoded data being generated without their knowledge.

Secondly, detecting that WITH_UNICODE was used is a trick !   Right now I just check that the version >= 5 and that cx_Oracle.UNICODE does not exist.   It would be nice if an explicit bit of information were available to detect this mode.

and thirdly, *why* is this mode, in its current form, even available in Python 2.x ?   The fact that it returns Python unicodes in result sets in all cases is great, and very useful.  But the rigidity and arguable bugginess on the connect/statement/bind parameter side doesn't seem to have any clear rationale.  It would be more performant and easier on the outside world if the unicode(x) call took place within cx_oracle's native guts and not within pure-Python libraries that wish to sanitize input.

- mike