[Sqlalchemy-tickets] Issue #4118: Surprising behaviour for session.execute(table.insert(), []) (zzzeek/sqlalchemy)

[Torsten L. <iss...@bi...>]

New issue 4118: Surprising behaviour for session.execute(table.insert(), [])
https://bitbucket.org/zzzeek/sqlalchemy/issues/4118/surprising-behaviour-for-sessionexecute

Torsten Landschoff:

Hi Michael,

I don't think this is really a bug, but it was surprising to me. Basically, I was doing some python side processing during a database upgrade, processing batches of 1000 input rows into the target table, filtering invalid data.

For each each I used a `session.execute` call with a list comprehension generating the rows to insert for each batch. This happend to crash with real data, as all rows of the batch were filtered out.

Code example:

```python
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()



class Task(Base):
    __tablename__ = "tasks"

    customer_id = Column(Integer, primary_key=True)
    task_id = Column(Integer, primary_key=True)


engine = create_engine("sqlite:///")
Base.metadata.create_all(engine)

session = sessionmaker(engine)()
session.execute(Task.__table__.insert(), [])  # real code: [row for row in ... if ...]
```

I get this output:

```
/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/sql/crud.py:695: SAWarning: Column 'tasks.customer_id' is marked as a member of the primary key for table 'tasks', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.
  util.warn(msg)
/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/sql/crud.py:695: SAWarning: Column 'tasks.task_id' is marked as a member of the primary key for table 'tasks', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.
  util.warn(msg)
Traceback (most recent call last):
  File "demo.py", line 20, in <module>
    session.execute(Task.__table__.insert(), [])
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1170, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/torsten.landschoff/sqlabug/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: tasks.customer_id [SQL: u'INSERT INTO tasks DEFAULT VALUES']
```

I would have expected that session.execute would do nothing when presented with an empty list for rows. The documentation is unclear about this:

> Optional dictionary, or list of dictionaries, containing bound parameter values.   If a single dictionary, [...]; if a list of dictionaries, an "executemany" will be invoked.

In my opinion an empty list qualifies as a list of dictionary - YMMV.

Thanks for considering, IMHO it would be a good thing to eliminate this pitfall in the long run.

Greetings, Torsten

PS: BTW: The behaviour is consistent compared with calling `engine.execute`.