On Win32, when I select from an SQLite view, and enclose the column name in double quotes in the select query, the cursor description (erroneously?) contains the double quotes.

On Linux (or on Win32 when selecting from a table rather than a view) the cursor description does not contain the double quotes. I expect the Linux behavior, not the Win32 behavior.

The following code demonstrates the problem.
----

import sqlite3, sys

print (sys.platform)
print (sys.version)

conn = sqlite3.connect (':memory:')
cur  = conn.cursor ()

cur.execute ('create table Foo ( foo_id integer primary key ) ;')
cur.execute ('create view Foo_View as select * from Foo ;')

cur.execute ('select foo_id from Foo;')
print (cur.description[0][0])
cur.execute ('select "foo_id" from Foo;')
print (cur.description[0][0])
cur.execute ('select foo_id from Foo_View;')
print (cur.description[0][0])
cur.execute ('select "foo_id" from Foo_View;')
print (cur.description[0][0])
----

Sample output on Linux and Win32.
----
linux
3.3.1 (default, Apr 17 2013, 22:32:14)
[GCC 4.7.3]
foo_id
foo_id
foo_id
foo_id
----
win32
3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)]
foo_id
foo_id
foo_id
"foo_id"
----

Above, please note the (erroneous?) double quotes around the final foo_id.

There is a decent chance this is a bug in sqlite. Have you checked?

Looking at the code, it seems to be a sqlite issue.

No, I have not checked to see if it is a bug in the Windows version of SQLite.

How would I even test that?

I just tried running the command line version of SQLite (version 3.8.0.2 2013-09-03) on Windows (XP SP2, in VirtualBox).

I manually ran the same statements from the Python script. I turned on headers (.headers ON). The headers did not contain the quotes around foo_id.

That's probably all the testing I can do easily, unless there is some other way to access the cursor description. I don't have a C development environment installed on Windows, nor have I ever written C code that calls SQLite.

This is a bug from Sqlite. Sqlite 3.7 is afflicted. Solution: upgrade to sqlite 3.8.

http://sqlite.1065341.n5.nabble.com/sqlite3-column-name-contains-quotes-for-views-td65226.html
http://www.sqlite.org/src/info/5526e0aa3c

Thanks for the confirmation, Vajrasky. It is apparently not hard to upgrade the sqlite3 that python uses even on Windows, so I'm going to close this issue. (We're currently up to sqlite3 3.8.1 on 3.4).

Upgrading sqlite3 in Windows maybe easy but Python 2.7.6 and 3.3.3 on Windows were built with defected sqlite3. Maybe at least we can provide the correct sqlite3 version next time we release Windows version of Python 2.7 and 3.3? Python 3.3 comes with sqlite3 3.7.12. Python 2.7.6 comes with sqlite3 3.6.12.

Python 3.4 is not afflicted. It comes with sqlite3 3.8.1.

That sqlite checkin is well before 3.7.12 was released, and 3.3.3 shipped with that version. Was the bug present in 3.6? If so I don't think we can do anything, since I believe we stay with the same minor version (ie: 3.6) of sqlite for the life of a minor version of Python (ie: 2.7).