when using sqlachemy autoload feature with oracle table column name coming in lower case even if in DB it is in upper case
Anoop
I am using Sqlalchemy 0.6.6 + cx_oracle5.0.3+python 2.6.4 and
oracle 10g from linux.
when I tried the sqlalchemy autoload feature
tbl=Table('autoload_test', meta,
autoload=True,autoload_with=engine, schema=None)
Here I am getting tbl.columns.keys() are all lower case even if
my column names are in upper case in DB . I checked the issue and
found the reason
in get_columns method (sqlalchemy/dialects/oracle/base.py)
after fetching the column names in the loop before assigning the
column name to colname variable normalize_name(sqlalchemy/dialects/
oracle/base.py) method is calling and finally the colname varaiable
will set as a value of name key and finally the dict will append to a
list in the loop.
cdict = {
'name': colname,
'type': coltype,
'nullable': nullable,
'default': default,
}
columns.append(cdict)
Here In normalize_name method
the code is
+++++++++++++++++++++++++++++++++++++++++++++++++
if name.upper() == name and \
not
self.identifier_preparer._requires_quotes(name.lower()):
return name.lower()
else:
return name
++++++++++++++++++++++++++++++++++++++++++++++++++
the _requires_quotes(sqlalchemy/sql/compiler.py) method will return a
Boolean value
++++++++++++++++++++++++++++++++++++++++++++++++++
def _requires_quotes(self, value):
"""Return True if the given identifier requires quoting."""
lc_value = value.lower()
return (lc_value in self.reserved_words
or value[0] in self.illegal_initial_characters
or not self.legal_characters.match(unicode(value))
or (lc_value != value))
++++++++++++++++++++++++++++++++++++++++++++++++++
Here the problem is lc_value!=value checking ,suppose my column name
is 'FIRST_NAME' in normalize_name method we called this method
"self.identifier_preparer._requires_quotes(name.lower())" ,so value
='first_name'
and lc_value = value.lower() => 'fist_name'
* In this case the last checking 'first_name'!='first_name' will
always fail and all four condition in my case is false now so
_requires_quotes will return False
e
* so now in normalize_name method return name.lower() will invoke
and i will get column name in upper case.
t
when I modified the code in normalize_name method like below
if name.upper() == name and \
not self.identifier_preparer._requires_quotes(name):
return name.lower()
else:
return name
I am getting table column names in upper case (ie how they are in
DB, here I am not using any quoted column names') . . Now SQLServer
+SqlAlchemy +autoload give upper case column names in upper case but
when connecting with oracle upper case column names will be converted
to lower case.Anybody have an idea why requires_quotes method is
called like this?
thanks:
Anoop
Michael Bayer
On Apr 6, 2011, at 3:54 PM, Anoop wrote:
> Hi All
>
> I am using Sqlalchemy 0.6.6 + cx_oracle5.0.3+python 2.6.4 and
> oracle 10g from linux.
>
> when I tried the sqlalchemy autoload feature
>
> tbl=Table('autoload_test', meta,
> autoload=True,autoload_with=engine, schema=None)
>
> Here I am getting tbl.columns.keys() are all lower case even if
> my column names are in upper case in DB . I checked the issue and
> found the reason
>
> in get_columns method (sqlalchemy/dialects/oracle/base.py)
> after fetching the column names in the loop before assigning the
> column name to colname variable normalize_name(sqlalchemy/dialects/
> oracle/base.py) method is calling and finally the colname varaiable
> will set as a value of name key and finally the dict will append to a
> list in the loop.
SQLAlchemy considers all lower case names to indicate a "case insensitive" name, whereas Oracle has the opposite behavior - it represents case insensitive names as all uppercase. SQLAlchemy's behavior in this regard is described at http://www.sqlalchemy.org/docs/dialects/oracle.html#identifier-casing . The "normalize_name" method you've found is part of the implementation of this approach.
>
> Here the problem is lc_value!=value checking ,suppose my column name
> is 'FIRST_NAME'
If your column is named "FIRST_NAME" in the Oracle database, and was named without quotes, the identifier is case insensitive. Any casing will match it. If you've named it "FIRST_NAME" and you did actually use quotes in the CREATE TABLE statement, Oracle still considers that name to be equivalent to "first_name", as long as quoting isn't used - because all uppercase is case insensitive in Oracle. See note #8 at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm (I also tried here to confirm).
> I am getting table column names in upper case (ie how they are in
> DB, here I am not using any quoted column names') . . Now SQLServer
> +SqlAlchemy +autoload give upper case column names in upper case but
because SQL Server uses lowercase names to indicate "case insensitive". Oracle does the opposite.
> when connecting with oracle upper case column names will be converted
> to lower case.Anybody have an idea why requires_quotes method is
> called like this?
Because SQLAlchemy is normalizing the differences in casing conventions to work across all databases with no changes in the declared Python metadata needed.
Anoop
HI Michael ,
Thanks for your reply,this is my table creation query without any
quoted variables
CREATE TABLE AUTOLOAD_TEST
(
ID INTEGER
, FIRST_NAME VARCHAR(20)
);
this is my connect_sqlalchemy.py script
++++++++++++++++++++++++++++++++++++++++++
from sqlalchemy import MetaData,create_engine,Table
engine = create_engine("oracle+cx_oracle://anoop:asdfgh@xe" )
meta = MetaData(engine)
tbl_autoload_test=Table('autoload_test', meta, autoload=True,
autoload_with=engine, schema=None)
print tbl_autoload_test.columns.keys()
++++++++++++++++++++++++++++++++++++++++++
When I run this script
(myenv)anoop@AGLAP:~/Example/sqlalchemy$ python connect_sqlalchemy.py
[u'id', u'first_name'] #Here I got column names in lower case
Current normalize_method in (loc :/sqlalchemy/dialects/oracle/
base.py)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
def normalize_name(self, name):
if name is None:
return None
# Py2K
if isinstance(name, str):
name = name.decode(self.encoding)
# end Py2K
not
self.identifier_preparer._requires_quotes(name.lower()):
return name.lower()
else:
return name
I modified the above method slightly my version of normalize_name
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
def normalize_name(self, name):
if name is None:
return None
# Py2K
if isinstance(name, str):
name = name.decode(self.encoding)
# end Py2K
this is my change not converted into lower when calling
else:
return name
(myenv)anoop@AGLAP:~/Example/sqlalchemy$ python connect_sqlalchemy.py
#OUTPUT
===============================================================
[u'ID', u'FIRST_NAME'] # Here output is in Capital letters not
converted into lower case
================================================================
Did you got my point ? In first the fourth checking (lc_value !=
value) in case _requires_quotes method (loc:sqlalchemy/sql/
compiler.py) is False because ('first_name'!='first_name' is the
checking) so here _requires_quotes will return False
But in second case (lc_value!=value) checking became True because now
the checking is 'first_name'!='FIRST_NAME' this is True and
_requires_quotes will return True and we will get upper case column
names in upper case. I wondering why "name" converted into lower when
calling _requires_quotes because in _requires_quotes again it is
converting into lower in statement " lc_value=value.lower() " ,I think
two times lower conversion is not needed here,am I correct?.
thanks:
Anoop
Michael Bayer
On Apr 9, 2011, at 6:15 PM, Anoop wrote:
>
> HI Michael ,
> Thanks for your reply,this is my table creation query without any
> quoted variables
>
> CREATE TABLE AUTOLOAD_TEST
> (
> ID INTEGER
> , FIRST_NAME VARCHAR(20)
> );
>
> this is my connect_sqlalchemy.py script
> ++++++++++++++++++++++++++++++++++++++++++
>
> from sqlalchemy import MetaData,create_engine,Table
> engine = create_engine("oracle+cx_oracle://anoop:asdfgh@xe" )
> meta = MetaData(engine)
> tbl_autoload_test=Table('autoload_test', meta, autoload=True,
> autoload_with=engine, schema=None)
> print tbl_autoload_test.columns.keys()
> ++++++++++++++++++++++++++++++++++++++++++
>
> When I run this script
> (myenv)anoop@AGLAP:~/Example/sqlalchemy$ python connect_sqlalchemy.py
> [u'id', u'first_name'] #Here I got column names in lower case
>
>
> if name.upper() == name and \
> not
> self.identifier_preparer._requires_quotes(name.lower()):
> return name.lower()
> else:
> return name
> if name.upper() == name and \
> not self.identifier_preparer._requires_quotes(name): #
> this is my change not converted into lower when calling
> return name.lower()
> else:
> return name
>
> [u'ID', u'FIRST_NAME'] # Here output is in Capital letters not
>
> Did you got my point ?
I'm assuming what you're looking for here is columns.keys() to be converted to uppercase. To be honest, to suit your use case, you should just say my_keys = [k.upper() for k in table.columns.keys() if k.lower() == k]. Because you shouldn't be thinking of those names as upper case *or* lowercase - they are case insensitive. SQLA uses all lower case to indicate case insensitive, Oracle uses all uppercase. You can emit the statement "select FIRST_NAME from autoload_test" or "select first_name from autoload_test" on your oracle database and you get the same result.
With your change, we basically treat Oracle names as UPPERCASE for case insensitive, the way Oracle itself does. Which is the reverse of the usage contract that SQLAlchemy provides for all other backends. Tables now have to be referenced as table.c.SOME_COLUMN, mapped classes will look like MyClass.FIRST_NAME, etc. If I make your change, symmetrically or not versus denormalize_name, there are dozens of test failures, illustrated here: http://paste.pocoo.org/show/368744/ . All the tests in test_reflection are normally fully cross-platform compatible. If we make an arbitrary reversal of SQLAlchemy's case insensitive convention in the case of Oracle, virtually all tests regarding reflection do not act as expected.