Why can't IPython return records with multiple fields when submitting a query to sqlite?

Question

I am trying to write a simple query to an sqlite database in a python script. To test if my parameters were correct, I tried running the query from the ipython command line. It looked something like this:

import sqlite3
db = 'G:\path\to\db\file.sqlite'
conn = sqlite3.connect(db)
results = conn.execute('SELECT * FROM studies').fetchall()

for some reason, my results came back totally empty. Then I tried another test query:

results = conn.execute('SELECT id FROM studies').fetchall()

Which returned correctly. I figured there was a problem with the asterisk [WRONG, SEE SECOND UPDATE BELOW], so I tried the 'SELECT * FROM studies' query from a default python command line. Lo and behold, it returned correctly. I tried all the normal ways to escape the asterisk only to be met by a wide variety of error messages. Is there any way to run this query in IPython?

---

EDIT: Sorry, I incorrectly assumed IronPython and IPython were the same. What I meant was the IPython command line, not the IronPython framework.

---

EDIT2: Okay, it turns out the asterisk DOES work as shown by this successful query:

'SELECT COUNT(*) FROM studies'

From the suggestions posted here, it turns out the error results from trying to return records with multiple fields, i.e.:

'SELECT field1,field2 FROM studies'

which still results in to records being returned. I have changed the title of the question accordingly.

Answer 1

This is SQL. IronPython has little or nothing to do with the processing of the query. Are you using an unusual character encoding? (IE not UTF-8 or ASCII)?

What happens if you SELECT id,fieldname,fieldname FROM studies (In other words, simulating what '*' does.)

Answer 2

Just a wild guess, but please try to escape backslashes in the path to the database file. In other words instead of

db = 'G:\path\to\db\file.sqlite'

try

db = 'G:\\path\\to\\db\\file.sqlite'

Answer 3

Some more debugging you could try:

s = 'SELEECT * from studies'
print s
conn.execute(s).fetchall()

or:

s = 'SELECT ' + chr(42) + ' from studies'
conn.execute(s).fetchall()

You might also try:

conn.execute('select count(*) from studies').fetchall()

if that comes back as [(0,)] then something really weird is going on :-)

---

Some more things you could try:

conn.execute('select id from (select * from studies)').fetchall()

or:

cur = conn.cursor()
cur.execute('select * from studies').fetchall()

Answer 4

I've tried all the things you've mentioned in IPython and sqlite without any problems (ipython 0.9.1, python 2.5.2).

Is there a chance this is some kind of version mismatch issue? Maybe your shells are referencing different libraries?

For example, does

import sqlite3; print sqlite3.version

return the same thing from both shells (i.e. ipython and the regular one where the sql query works)?

How about

conn.execute('select sqlite_version()').fetchall()

Does that return the same thing?