classification
Title: sqlite3 cursor.description varies across Linux (3.3.1), Win32 (3.3.2), when selecting from a view.
Type: behavior Stage: resolved
Components: Library (Lib) Versions: Python 3.3
process
Status: closed Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: kushal.das, mpb, r.david.murray, vajrasky, zach.ware
Priority: normal Keywords:

Created on 2013-10-04 20:53 by mpb, last changed 2013-12-15 17:16 by r.david.murray. This issue is now closed.

Messages (8)
msg198964 - (view) Author: mpb (mpb) Date: 2013-10-04 20:53
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.
msg199215 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-10-08 16:15
There is a decent chance this is a bug in sqlite.  Have you checked?
msg199224 - (view) Author: Kushal Das (kushal.das) * (Python committer) Date: 2013-10-08 17:47
Looking at the code, it seems to be a sqlite issue.
msg199236 - (view) Author: mpb (mpb) Date: 2013-10-08 18:58
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.
msg206187 - (view) Author: Vajrasky Kok (vajrasky) * Date: 2013-12-14 15:08
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
msg206193 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-12-14 16:51
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).
msg206231 - (view) Author: Vajrasky Kok (vajrasky) * Date: 2013-12-15 13:05
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.
msg206243 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-12-15 17:16
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).
History
Date User Action Args
2013-12-15 17:16:11r.david.murraysetmessages: + msg206243
2013-12-15 13:05:11vajraskysetnosy: + zach.ware
messages: + msg206231
2013-12-14 16:51:37r.david.murraysetmessages: + msg206193
2013-12-14 16:51:23r.david.murraysetmessages: - msg206192
2013-12-14 16:50:47r.david.murraysetstatus: open -> closed

messages: + msg206192
stage: resolved
2013-12-14 15:08:45vajraskysetnosy: + vajrasky
messages: + msg206187
2013-10-08 18:58:02mpbsetmessages: + msg199236
2013-10-08 17:47:00kushal.dassetnosy: + kushal.das
messages: + msg199224
2013-10-08 16:15:05r.david.murraysetnosy: + r.david.murray
messages: + msg199215
2013-10-04 20:53:03mpbcreate