Inconsistent behaviour using sqlite3.Row to index into results by name

In my Python application I have been using sqlite3.Row as the row factory to index results by name for a while with no issues. Recently I moved my application to a new server (no code changes), and I discovered this method of indexing is now unexpectedly failing on the new server given quite a specific condition. I cannot see any explanation for it.

The problem seems to occur on the new server when I have the DISTINCT keyword in my select query:

import sqlite3
conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row
c = conn.cursor()

c.execute('create table test ([name] text)')
c.execute("insert into test values ('testing')")
conn.commit()

c.exec开发者_如何学Cute('select [name] from test')
row = c.fetchone()
print row['name'] # works fine on both machines

c.execute('select distinct [name] from test') # add distinct keyword
row = c.fetchone()
print row['name'] # fails on new server (no item with that key)

As you can see I am able to sandbox this problem using an in-memory database, so the problem is nothing to do with my existing data. Both machines are Debian based (old: Ubuntu 8.10, new: Debian 5.0.3) and both machines are running Python 2.5.2. I believe the sqlite3 module is a core part of the Python install, so I do not know how this subtle breakage can be occurring since the python versions are identical.

Has anyone got any ideas, or seen anything like this before?

Thanks,

Chris

Try adding the line

print row.keys()

instead of "print row['name']" to see what column 0's actual name is in the second case (it's probably altered by the "DISTINCT" keyword).

Alternatively you can use row[0] in this case, but that's most likely not what you want. :)

I had a different, but similar, problem but googling "indexerror no item with that key" led me to this question. In my case the issue was that different sqlite versions appear to handle row key names in row_factory = sqlite3.Row mode differently. In sqlite 3.24.0, a query like:

select table.col
from table

...creates a key in the row dictionary like col. But older versions appear to use the qualified key like table.col. Providing an explicit alias or not qualifying the column is a workaround. e.g:

select table.col as "col"
from table

Or:

select col
from table