Accessing iterator on a query that has an error shows as not iterable

[Michael Brown]

Found on 1.0.3 and 1.0.4

I have a query that when evaluated throws a SQL error, except if the iterator is used. If the iterator is used then it shows as not iterable:

Normal:

In [66]: q = session.query(m.Panel).filter(m.Panel.ptype=='blood')

In [67]: map(lambda x: x, q)
Out[67]: 
[Panel(ptype='blood', id=74, name='bottle'),
 Panel(ptype='blood', id=75, name='space shuttle'),
 Panel(ptype='blood', id=76, name='russian'),
…

Failing:

In [25]: q = session.query(m.Panel).filter(m.Panel.ptype=='bloody')

In [26]: map(lambda x: x, q)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
panel.py in <module>()
----> 1 map(lambda x: x, q)

TypeError: argument 2 to map() must support iteration

In [68]: q = session.query(m.Panel).filter(m.Panel.ptype=='bloody')

In [69]: next(q)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
panel.py in <module>()
----> 1 next(q)

TypeError: Query object is not an iterator

What I think should happen is that the underlying error should be thrown like so when the iterator gets accessed (note the Query still shows as Iterable):

In [70]: q.first()
---------------------------------------------------------------------------
DataError                                 Traceback (most recent call last)
…
DataError: (psycopg2.DataError) invalid input value for enum panel_type: "bloody"

[Mike Bayer]

I kind of think this is a Py2k bug, that Py3K fixes. Try this:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://")
Base.metadata.create_all(e)
s = Session(e)

s.add(A(id=1))
s.commit()

good_q = s.query(A)
bad_q = s.query(A).filter(text("THIS IS CRAP"))

try:
    list(bad_q)
except Exception as e:
    print("Threw exception as we'd expect:  %s" % e)

# doesn't throw our exception in py2k, but in py3k, it does
map(lambda x: x, bad_q)

on Py2k:

#!

$ python test.py
Threw exception as we'd expect:  (sqlite3.OperationalError) no such column: THIS [SQL: u'SELECT a.id AS a_id \nFROM a \nWHERE THIS IS CRAP']
Traceback (most recent call last):
  File "test.py", line 28, in <module>
    map(lambda x: x, bad_q)
TypeError: argument 2 to map() must support iteration

on Py3K:

#!

$ python3 test.py
Threw exception as we'd expect:  (sqlite3.OperationalError) no such column: THIS [SQL: 'SELECT a.id AS a_id \nFROM a \nWHERE THIS IS CRAP']
Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: THIS

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "test.py", line 28, in <module>
    map(lambda x: x, bad_q)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2515, in __iter__
    return self._execute_and_instances(context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 2530, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: THIS [SQL: 'SELECT a.id AS a_id \nFROM a \nWHERE THIS IS CRAP']

looks like py3k fixed the behavior of map() here.

[Michael Brown]

Noted, although next(q) also fails so it's not specific to map…

Oh! next(q) also fails on the good query… that's weird.

[Mike Bayer]

next() is not an issue here. you can't call next() on a list either:

>>> a = []
>>> next(a)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: list object is not an iterator

[Mike Bayer]

this is the expected behavior of an object that implements __iter__(). py2k's map() is broken.