select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

Issue #3079 closed
created an issue

Below is code that generates what is, IMO, a bug. To see the bug, comment out the line towards the end with the comment saying COMMENT ME OUT. That line will result in the issuing of a select request that, given how I understand SQL and sqlalchemy, should generate 0 rows. Instead, it causes a ResourceClosedError. I can use this select statement, however, to be inserted into other tables (see I think that the statement should return 0 rows instead of raising the error.

from sqlalchemy import Table, Column, String, Integer, MetaData, \
    select, func, ForeignKey, text
import sys
from functools import reduce

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)

metadata = MetaData()

linked_list = Table('linked_list', metadata,
    Column('id', Integer, primary_key = True),
    Column('at', Integer, nullable=False),
    Column('val', Integer, nullable=False),
    Column('next', Integer, ForeignKey(''))

refs = Table('refs', metadata,
    Column('id', Integer, primary_key = True),
    Column('ref', Integer, ForeignKey('')),

conn = engine.connect()

refs_al = refs.alias()

linked_list_m = select([

llm_alias = linked_list_m.alias()
ll_alias = linked_list.alias()

linked_list_m = linked_list_m.union_all(
        ll_alias.c.val * llm_alias.c.val,

llm_alias_2 = linked_list_m.alias()

sub_statement = select([

def gen_statement(v) :
  return select([refs_al.c.ref, func.max(llm_alias_2.c.val)]).\
       join(llm_alias_2, onclause=refs_al.c.ref ==\
     group_by(refs_al.c.ref).where(llm_alias_2.c.val > v)

LISTS = [[2,4,4,11],[3,4,5,6]]

idx = 0
for LIST in LISTS :
  start = idx
  for x in range(len(LIST)) :
    ELT = LIST[x]
      values(at=idx, val = ELT, next=idx+1 if x != len(LIST) - 1 else None))
    idx += 1

print "LISTS:"
for LIST in LISTS :
  print "  ", LIST

def PRODUCT(L) : return reduce(lambda x,y : x*y, L, 1)
for LIST in LISTS :
  print "  ", PRODUCT(LIST)

for x in (345,355,365) :
  if x == 365 : continue # COMMENT ME OUT TO GET sqlalchemy.exc.ResourceClosedError
  statement_ = gen_statement(x)
  print "########"
  print "Lists that are greater than:", x
  allresults = conn.execute(statement_).fetchall()
  if len(allresults) == 0 :
    print "  /no results found/"
  else :
    for res in allresults :
      print res

print "########"

Comments (6)

  1. Michael Bayer repo owner

    this is a pysqlite bug. ALL DBAPI cursors must return a .description attribute when a SELECT has proceeded (please note the link, this is regardless of whether or not any rows are present). SQLAlchemy uses this as a guideline to know when the statement is one that returns rows, even one that returns zero rows (a zero-length result set still has column names).

    Below, a script illustrates this for some sample statements, and runs against psycopg2/postgresql as well as sqlite. Note that SQLAlchemy is not used, only plain DBAPIs psycopg2 and sqlite3. Only the last statement fails, and only on sqlite:

    s1 = "SELECT * FROM linked_list"
    s2 = "SELECT * FROM refs"
    s3 = """
    WITH RECURSIVE anon_2(at, val, next) AS
    (SELECT AS at, linked_list.val AS val, AS next
    FROM linked_list, refs AS refs_1
    WHERE = refs_1.ref
    UNION ALL SELECT AS at, linked_list_1.val * anon_3.val AS anon_4, AS next
    FROM anon_2 AS anon_3, linked_list AS linked_list_1
    WHERE =
     SELECT refs_1.ref, max(anon_1.val) AS max_1
    FROM refs AS refs_1 JOIN anon_2 AS anon_1 ON refs_1.ref =
     GROUP BY refs_1.ref
    import sqlite3
    sqlite_conn = sqlite3.connect(":memory:")
    import psycopg2
    pg_conn = psycopg2.connect(user='scott', password='tiger', database='test', host='localhost')
    for conn in (pg_conn, sqlite_conn):
        print ("Conn: ", conn)
        cursor = conn.cursor()
        CREATE TABLE linked_list (
            id INTEGER NOT NULL,
            at INTEGER NOT NULL,
            val INTEGER NOT NULL,
            next INTEGER,
            PRIMARY KEY (id),
            UNIQUE (at),
            FOREIGN KEY(next) REFERENCES linked_list (at)
        CREATE TABLE refs (
            id INTEGER NOT NULL,
            ref INTEGER,
            PRIMARY KEY (id),
            FOREIGN KEY(ref) REFERENCES linked_list (at)
        for statement in [s1, s2, s3]:
            print ("Statement: ", statement[0:30])
            assert cursor.description


    ('Conn: ', <connection object at 0x100559d90; dsn: 'dbname=test user=scott password=xxxxx host=localhost', closed: 0>)
    ('Statement: ', 'SELECT * FROM linked_list')
    ('Statement: ', 'SELECT * FROM refs')
    ('Statement: ', '\nWITH RECURSIVE anon_2(at, val')
    ('Conn: ', <sqlite3.Connection object at 0x100474118>)
    ('Statement: ', 'SELECT * FROM linked_list')
    ('Statement: ', 'SELECT * FROM refs')
    ('Statement: ', '\nWITH RECURSIVE anon_2(at, val')
    Traceback (most recent call last):
      File "", line 52, in <module>
        assert cursor.description
  2. Michael Bayer repo owner

    this bug is very simple. pysqlite is clearly parsing the statement for a SELECT in order to detect a zero-row statement:

    import sqlite3
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
        create table foo (id integer primary key, data varchar(20))
        insert into foo (id, data) values (10, 'ten')
        with bar as (select * from foo)
        select * from bar where id = 10
    assert cursor.description
        with bar as (select * from foo)
        select * from bar where id = 11
    assert cursor.description
  3. Michael Bayer repo owner

    that said, if someone proposes a workaround in that issue such as adding a special comment to the statement, we can do that. reopen if such a workaround becomes possible.

  4. Log in to comment