ResultProxy uses case-insensitive column names

[Former user]

We're dealing with tables with user-defined schemas. Sometimes they provide columns with duplicate names, except for case, e.g.:

  CREATE TABLE foo ("bar" integer, "BAR" integer);
  INSERT INTO foo ("bar", "BAR") VALUES (1, 2);

While that's clearly crazy, I expect sqlalchemy to handle them. Unfortunately:

  for row in table.select().execute():
    print row.items()

Causes:

  Traceback (most recent call last):
    File "<ipython console>", line 4, in f
    File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 1359, in items
      return [getattr(self, key)) for key in self.iterkeys()]((key,)
    File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 1352, in __getattr__
      return self.__parent._get_col(self.__row, name)
    File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 1620, in _get_col
      return processor(row[index](index))
    File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 1513, in process
      "try 'use_labels' option on select statement." % colname)
  InvalidRequestError: Ambiguous column name 'bar' in result set! try 'use_labels' option on select statement.

The problem is apparent from the ResultProxy docstring:

    Individual columns may be accessed by ... case-insensitive column name ...

Note that row.keys() and list(row) both work fine, so the exception message is actually misleading, because the fetch from the database works fine (the naming ambiguity is a problem in sqlalchemy, after the rows have been fetched)

Discovered on 0.5.8 but that docstring is unchanged in the 0.7.7 source, so I assume it hasn't been fixed.

[Mike Bayer]

dupe of #2423, please see 08Migration#Case-insensitiveresultrownameswillbedisabledinmostcases.

>>> from sqlalchemy import __version__
>>> __version__
'0.8.0b1'
>>> from sqlalchemy import *
>>> 
>>> e = create_engine("postgresql://scott:tiger@localhost/test")
>>> conn = e.connect()
>>> t = conn.begin()
>>> conn.execute('CREATE TABLE foo ("bar" integer, "BAR" integer)')
<sqlalchemy.engine.base.ResultProxy object at 0x101422190>
>>> conn.execute('INSERT INTO foo ("bar", "BAR") VALUES (1, 2)')
<sqlalchemy.engine.base.ResultProxy object at 0x101422210>
>>> table = Table('foo', MetaData(), Column('bar', Integer), Column('BAR', Integer))
>>> for row in conn.execute(table.select()):
...     print row.items()
... 
[1), (u'BAR', 2)]((u'bar',)