Python Forum
Surprising behavior of SQLite3 "with" query
Thread Rating:
  • 2 Vote(s) - 1.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Surprising behavior of SQLite3 "with" query
#1
My question concerns the behavior of SQLite3 with query.
When a select query has been executed, cursor attribute description provides the list of column names of the result set. And this, whether this result set is empty or not (as told in the documentation: It is set for SELECT statements without any matching rows as well).
Query with-select behaves exactly like select, except when the result set is empty, in which case cursor attribute description is set to None, which is particularly disturbing.
Versions: Windows 7, Python 2.7.12, SQLite 3.17

The following Python program
  • opens an in-memory DB, creates table T(A,B) in which it stores three data rows.
  • creates a with-select query that selects the rows of T where B = <some value>
  • executes the query with value 4
  • displays the result set
  • displays the names of the columns

import sqlite3
conn = sqlite3.connect(u':memory:')
c = conn.cursor()

c.execute(u'create table T(A integer, B integer)')
c.execute(u'insert into T values (1,2),(2,4),(3,6)')

query = u'with CTE(X,Y) as (select A,B from T where B = ?) select X,Y from CTE'

c.execute(query,(4,))

for r in c:
    print r
    
for d in c.description:
    print d
    
c.close()
conn.close()
The result is as expected:

Output:
('X', None, None, None, None, None, None) ('Y', None, None, None, None, None, None) (2, 4)
However, if I change constant 4 for 5, the result set is empty so that nothing is displayed, then the program crashes:

Error:
Traceback (most recent call last): File "C:\Users\Administrateur\Desktop\Python_Test_With.py", line 17, in <module> for d in c.description: TypeError: 'NoneType' object is not iterable
I have found no trace of this problem on the web.
Has anybody observed it?
Is there another way to get column names?

Thanks
Reply
#2
There are no rows that have 5 for column B.  So there are no results.  So there's no results to iterate over.  So trying to iterate over nothing is an error.

I'm not sure what cursor.description is supposed to do, but why not just iterate over cursor.execute()'s results directly, or use cursor.fetchone() to get the results?
Reply
#3
I agree, but the problem is not the way empty result sets are coped with (they behave the same way for select and with-select) but the value assigned to c.description in both cases. Missing these metadata is particularly annoying with recursive CTE.
Reply
#4
If you need the column names, and you're not sure if there's always going to be contents in the table (a common situation for db admin tools), you could select from the sqlite_master table, or use pragma table_info(table_name);, although I don't know if that works for ctes.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Basic SQL query using Py: Inserting or querying sqlite3 database not returning data marlonbown 3 1,306 Nov-08-2022, 07:16 PM
Last Post: marlonbown
  Block of code, scope of variables and surprising exception arbiel 8 3,338 Apr-06-2020, 07:57 PM
Last Post: arbiel
  sqlite3 operational error on insert query jonesin1974 5 4,212 Jun-26-2018, 03:31 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020