_fetch_implicit_returning raises TypeError if INSERT FROM SELECT inserts no rows

[First Last]

I'm inserting into a table using the results of a query. When that query returns no rows, a TypeError is raised. A test example:

from sqlalchemy import create_engine, String, Column, Table, MetaData, \
    literal_column, select

engine = create_engine('postgresql://rich@localhost/test', echo=True)

metadata = MetaData()

with engine.connect() as conn:

    tbl = Table('test_table', metadata, Column('id', String, primary_key=True))
    tbl.create(bind=conn)

    conn.execute(
        tbl.insert()
        .from_select(
            [tbl.c.id],
            select([literal_column("'id'")])
            .where(literal_column("false")))
    )

dies with the following error on 0.9.7 with psycopg2 2.5.3 and postgresql 9.3.4:

2014-08-20 17:34:19,054 INFO sqlalchemy.engine.base.Engine select version()
2014-08-20 17:34:19,054 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,057 INFO sqlalchemy.engine.base.Engine select current_schema()
2014-08-20 17:34:19,057 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,058 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-20 17:34:19,058 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,059 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-08-20 17:34:19,059 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,060 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2014-08-20 17:34:19,060 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,062 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE test_table (
    id VARCHAR NOT NULL, 
    PRIMARY KEY (id)
)


2014-08-20 17:34:19,062 INFO sqlalchemy.engine.base.Engine {}
2014-08-20 17:34:19,069 INFO sqlalchemy.engine.base.Engine COMMIT
2014-08-20 17:34:19,071 INFO sqlalchemy.engine.base.Engine INSERT INTO test_table (id) SELECT 'id' 
WHERE false RETURNING test_table.id
2014-08-20 17:34:19,071 INFO sqlalchemy.engine.base.Engine {}
Traceback (most recent call last):
  File "test.py", line 19, in <module>
    .where(literal_column("false")))
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 321, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 978, in _execute_context
    context._fetch_implicit_returning(result)
  File "/Users/rich/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 815, in _fetch_implicit_returning
    ipk.append(row[c])
TypeError: 'NoneType' object has no attribute '__getitem__'

Assuming that functionality isn't needed, looks like this can be worked around by setting implicit_returning=False on the engine or table.

[Mike Bayer]

I'm going to change this, but there is actually a documentation note regarding this, if you look at the note regarding inline=True at http://docs.sqlalchemy.org/en/rel_0_9/core/dml.html?highlight=from_select#sqlalchemy.sql.expression.Insert.from_select.

[Mike Bayer]

• The INSERT...FROM SELECT construct now implies inline=True on :class:.Insert. This helps to fix a bug where an INSERT...FROM SELECT construct would inadvertently be compiled as "implicit returning" on supporting backends, which would cause breakage in the case of an INSERT that inserts zero rows (as implicit returning expects a row), as well as arbitrary return data in the case of an INSERT that inserts multiple rows (e.g. only the first row of many). A similar change is also applied to an INSERT..VALUES with multiple parameter sets; implicit RETURNING will no longer emit for this statement either. As both of these constructs deal with varible numbers of rows, the :attr:.ResultProxy.inserted_primary_key accessor does not apply. Previously, there was a documentation note that one may prefer inline=True with INSERT..FROM SELECT as some databases don't support returning and therefore can't do "implicit" returning, but there's no reason an INSERT...FROM SELECT needs implicit returning in any case. Regular explicit :meth:.Insert.returning should be used to return variable numbers of result rows if inserted data is needed. fixes #3169

→ <<cset 71ca494f5186>>

[Mike Bayer]

• upgrade the note about inline=True to a warning, and make it clear that this flag should really be set all the time. ref #3169

→ <<cset d3dea559e180>>

[Mike Bayer]

so in 0.9, set inline=True, in 1.0 the insert from select will not generate RETURNING at all unless you specify explicitly with returning().

[First Last]

I see now - in the previous version of the that doc, I hadn't understood the usage of inline=True to prevent this failure. The updated version is very clear now, and I'll use that fix in 0.9. Thanks!