Oracle Connection Oddities

[Burhan]

Platform: Windows XP
Oracle : 10.2.0.4.0
SQLAlchemy: 0.7
Python: 2.7
Driver: cx_Oracle (compiled with unicode support as per the Windows
binary)

My problem is that when I try to do introspection, sqla refuses to
connect with this error:

cursor.execute("SELECT 0.1 FROM DUAL") TypeError: expecting None or a
string

If this string is unicode it works fine (I actually edited the
cx_oracle.py file and changed this to cursor.execute(u"SELECT 0.1 FROM
DUAL")) and it worked.

Next problem is a simple case doesn't work, the exact line is:

foo =
Table('MERCHANT',oracle_meta,autoload=True,autoload_with=oracle_engine)

Error is:

NoSuchTableError: MERCHANT

However, the exact same connect string with raw cx_Oracle works great:

>> cur.execute(u"SELECT MERCHANT_NUMBER FROM MERCHANT")
>> r = cur.fetchall()
>> len(r)
>> 2922

Please point me in the right direction.

[Michael Bayer]

Burhan wrote:
> Platform: Windows XP
> Oracle : 10.2.0.4.0
> SQLAlchemy: 0.7
> Python: 2.7

> Driver: cx_Oracle (compiled with unicode support as per the Windows
> binary)

What version is in use here? Is the "unicode support as per the Windows
binary" you refer to cx_oracle's "UNICODE" mode (im guessing so since that
version seems to include builds with the mode turned on)? this setting
has been discontinued with cx_oracle and is removed in version 5.1, and is
never required for Python 2.xx. SQLAlchemy supports this mode marginally
but the error you are getting would appear that this mode is in use (and
an edge that SQLA has missed). You should upgrade to cx_oracle 5.1, or
use the 5.0 build without the needless/extremely inconvenient UNICODE
flag, ensuring the special UNICODE mode is not in use - so that
cursor.execute() accepts strings as well as Python unicode objects
equally.

> Next problem is a simple case doesn't work, the exact line is:
>
> foo =
> Table('MERCHANT',oracle_meta,autoload=True,autoload_with=oracle_engine)
>
> Error is:
>
> NoSuchTableError: MERCHANT

SQLAlchemy will interpret 'MERCHANT' as:

'"MERCHANT"'

i.e., with quotes, case sensitive. Case insensitive names with SQLAlchemy
are indicated using all lowercase names, i.e. 'merchant' - else quoting is
used. The Oracle SQL statement you illustrate is using case insensitive
names. Still, its not clear how it wouldn't locate the table at all as
'MERCHANT' should equate to '"MERCHANT"' (haven't tried lately though),
turn on SQL echoing with echo='debug' (after changing 'MERCHANT' to
'merchant' in code) to ensure it's talking to the right database and such.

>
> However, the exact same connect string with raw cx_Oracle works great:
>
>>> cur.execute(u"SELECT MERCHANT_NUMBER FROM MERCHANT")
>>> r = cur.fetchall()
>>> len(r)
>>> 2922
>
> Please point me in the right direction.
>

> --
> 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.
>
>

[Burhan]

Thanks Michael for the reply.

I am not sure what version of cx_Oracle it is - it was downloaded as a Windows binary - the latest version is 5.1 on the cx_Oracle download page.

I did manage to solve the other problem though, but I don't know why I needed a fix.

In the vanilla cx_Oracle code, I used the exact same connect string (with the Oracle service name), and ran the query and it worked fine.

In sqla I had to give it a namespace definition (which I figured out by accident by browsing the tables from a third party tool), and then the exact same "MERCHANT" worked.

[Ian Kelly]

On Thu, Jul 14, 2011 at 12:53 PM, Burhan <burhan...@gmail.com> wrote:
> I am not sure what version of cx_Oracle it is - it was downloaded as a
> Windows binary - the latest version is 5.1 on the cx_Oracle download page.

>>> import cx_Oracle
>>> print cx_Oracle.version