select statement returning 0 rows generates: sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.
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 http://stackoverflow.com/questions/24094857/subqueries-for-filters-in-joined-sqlalchemy-statements/24159702#24159702). 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('linked_list.at'))
)
refs = Table('refs', metadata,
Column('id', Integer, primary_key = True),
Column('ref', Integer, ForeignKey('linked_list.at')),
)
metadata.create_all(engine)
conn = engine.connect()
refs_al = refs.alias()
linked_list_m = select([
linked_list.c.at,
linked_list.c.val,
linked_list.c.next]).\
where(linked_list.c.at==refs_al.c.ref).\
cte(recursive=True)
llm_alias = linked_list_m.alias()
ll_alias = linked_list.alias()
linked_list_m = linked_list_m.union_all(
select([
llm_alias.c.at,
ll_alias.c.val * llm_alias.c.val,
ll_alias.c.next
]).
where(ll_alias.c.at==llm_alias.c.next)
)
llm_alias_2 = linked_list_m.alias()
sub_statement = select([
llm_alias_2.c.at,
llm_alias_2.c.val]).\
order_by(llm_alias_2.c.val.desc()).\
limit(1)
def gen_statement(v) :
return select([refs_al.c.ref, func.max(llm_alias_2.c.val)]).\
select_from(
refs_al.\
join(llm_alias_2, onclause=refs_al.c.ref == llm_alias_2.c.at)).\
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]
conn.execute(linked_list.insert().\
values(at=idx, val = ELT, next=idx+1 if x != len(LIST) - 1 else None))
idx += 1
conn.execute(refs.insert().values(ref=start))
print "LISTS:"
for LIST in LISTS :
print " ", LIST
def PRODUCT(L) : return reduce(lambda x,y : x*y, L, 1)
print "PRODUCTS OF LISTS:"
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
conn.execute(statement_)
allresults = conn.execute(statement_).fetchall()
if len(allresults) == 0 :
print " /no results found/"
else :
for res in allresults :
print res
print "########"
Comments (6)
-
repo owner -
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() 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 cursor.execute(""" with bar as (select * from foo) select * from bar where id = 11 """) assert cursor.description
-
repo owner - changed status to closed
here's your issue: http://bugs.python.org/issue21718 it's upstream. Sorry!
-
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.
-
reporter That was faaasssst - thanks!
-
If anybody runs into this, just as a hint: this is fixed upstream in pysqlite2: https://github.com/ghaering/pysqlite/commit/f67fa9c898a4713850e16934046f0fe2cba8c44c It seems that no release has been made with the fix yet.
- Log in to comment
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:
output: