classification
Title: sqlite3 cursor.description seems to rely on incomplete statement parsing for detection
Type: behavior Stage: resolved
Components: Library (Lib) Versions: Python 3.6, Python 3.5, Python 2.7
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: ghaering Nosy List: berker.peksag, ghaering, python-dev, r.david.murray, rbcollins, tanner, zzzeek
Priority: normal Keywords: patch

Created on 2014-06-11 13:11 by zzzeek, last changed 2016-08-21 16:32 by tanner. This issue is now closed.

Files
File name Uploaded Description Edit
with2.diff tanner, 2014-10-16 23:55 fix review
f67fa9c898a4713850e16934046f0fe2cba8c44c.patch r.david.murray, 2015-08-15 13:11
issue21718_tests.diff berker.peksag, 2016-04-02 07:53
Messages (14)
msg220263 - (view) Author: mike bayer (zzzeek) * Date: 2014-06-11 13:11
Per DBAPI and pysqlite docs, .description must be available for any SELECT statement regardless of whether or not rows are returned.  However, this fails for SELECT statements that aren't simple "SELECT"s, such as those that use CTEs and therefore start out with "WITH:":

import sqlite3
conn = sqlite3.connect(":memory:")

cursor = conn.cursor()
cursor.execute("""
    create table foo (id integer primary key, data varchar(20))
""")

cursor.execute("""
    insert into foo (id, data) values (10, 'ten')
""")

cursor.execute("""
    with bar as (select * from foo)
    select * from bar where id = 10
""")

assert cursor.description is not None


cursor.execute("""
    with bar as (select * from foo)
    select * from bar where id = 11
""")

assert cursor.description is not None


the second statement returns no rows and cursor.description is None.   Libraries like SQLAlchemy which rely on this to determine that the statement supports fetchone() and similar are blocked.
msg220268 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2014-06-11 13:30
It is true that the sqlite interface does not support WITH currently.  It is an interesting question whether or not we could change this as a bug fix or not...I suppose it depends on whether or not it changes any behavior other than making .description work.
msg229537 - (view) Author: Tom Tanner (tanner) * Date: 2014-10-16 16:56
this patch fixes the bug.
It parses the with WITH statement.
msg231424 - (view) Author: Tom Tanner (tanner) * Date: 2014-11-20 11:29
ping
I'd appreciate a review of my patch.
msg231439 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2014-11-20 16:42
It's more a matter of my finding time to fully research the problem and solution than an issue of reviewing the patch itself.  There is also potential cross-talk with other patches involving sqllite statement parsing.  Since the sqlite wrapper doesn't currently have an active maintainer, I'm afraid it may be a bit before any review of these issues happen.  If you can get other community members (doesn't have to be committers) to do reviews (of the concepts as well as the patch itself) that would be helpful.  (python-list might be a place to recruit some interest.)

(Personally I find the fact the the wrapper has to do statement parsing at all to be problematic, but there may be no good solution to that problem when you consider backward compatibility issues.)
msg233385 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-01-04 03:12
I have now committed a fix in the pysqlite project at github. https://github.com/ghaering/pysqlite/commit/f67fa9c898a4713850e16934046f0fe2cba8c44c

I'll eventually merge it into the Python tree.
msg240124 - (view) Author: Tom Tanner (tanner) * Date: 2015-04-05 15:58
Are you going to merge it into 2.7.10?
msg240135 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2015-04-05 20:39
It looks to me like a patch that could be merged as a bug fix.
msg243933 - (view) Author: Tom Tanner (tanner) * Date: 2015-05-23 17:26
is this going to be fixed in 2.7.10?
msg248066 - (view) Author: Robert Collins (rbcollins) * (Python committer) Date: 2015-08-05 19:51
@Gerhard would you like that ported to cPython for you?

@Tom - I think that if the patch applies to 2.7.x we should apply it there since its very unlikely to break non-buggy code.
msg248639 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2015-08-15 13:11
Attached is the patch ported to python2.7.  However, the test doesn't fail for me before I apply (compile) the fix.
msg262791 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2016-04-02 07:53
I adapted the reproducer in msg220263 and added more tests.
msg273301 - (view) Author: Roundup Robot (python-dev) (Python triager) Date: 2016-08-21 16:28
New changeset 45ef062734d6 by Berker Peksag in branch '3.5':
Issue #21718: cursor.description is now available for queries using CTEs
https://hg.python.org/cpython/rev/45ef062734d6

New changeset cf18375732ae by Berker Peksag in branch 'default':
Issue #21718: Merge from 3.5
https://hg.python.org/cpython/rev/cf18375732ae
msg273303 - (view) Author: Tom Tanner (tanner) * Date: 2016-08-21 16:32
will the fix be backported to 2.7?
History
Date User Action Args
2016-08-21 16:32:28tannersetmessages: + msg273303
2016-08-21 16:29:53berker.peksagsetstatus: open -> closed
resolution: fixed
stage: patch review -> resolved
2016-08-21 16:28:30python-devsetnosy: + python-dev
messages: + msg273301
2016-04-02 07:53:14berker.peksagsetfiles: + issue21718_tests.diff
versions: - Python 3.4
nosy: + berker.peksag

messages: + msg262791

stage: test needed -> patch review
2015-08-15 13:11:38r.david.murraysetstage: commit review -> test needed
2015-08-15 13:11:30r.david.murraysetfiles: + f67fa9c898a4713850e16934046f0fe2cba8c44c.patch

messages: + msg248639
2015-08-05 19:51:37rbcollinssetnosy: + rbcollins

messages: + msg248066
versions: + Python 3.6
2015-05-23 17:26:25tannersetmessages: + msg243933
2015-04-05 20:39:21r.david.murraysetstage: commit review
messages: + msg240135
versions: + Python 2.7, Python 3.4, Python 3.5
2015-04-05 15:58:12tannersetmessages: + msg240124
2015-01-04 03:12:20ghaeringsetassignee: ghaering

messages: + msg233385
nosy: + ghaering
2014-11-20 16:42:04r.david.murraysetmessages: + msg231439
2014-11-20 11:29:31tannersetmessages: + msg231424
2014-10-16 23:55:57tannersetfiles: + with2.diff
2014-10-16 23:54:34tannersetfiles: - with.diff
2014-10-16 16:56:31tannersetfiles: + with.diff

nosy: + tanner
messages: + msg229537

keywords: + patch
2014-06-11 13:30:11r.david.murraysetnosy: + r.david.murray
messages: + msg220268
2014-06-11 13:11:09zzzeekcreate