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

Question

Hi all.

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.execute('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

Answer 1

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