Surprising behaviour for session.execute(table.insert(), [])

Issue #4118 wontfix
Torsten Landschoff
created an issue

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:

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.

Comments (2)

  1. Michael Bayer repo owner

    this is more an engine-level thing which is that if you say conn.execute(insert, []), that means execute an INSERT statement with no parameters. the Session is just passing it along and no indication that this is an "executemany" is communicated, because connection.execute() sees no entry in the list to know that it would be such.

    a SQLAlchemy 2.0 would have much stricter rules between execute() and executemany(), maybe even restore the two separate methods.

    Until then, you will need to do "params = []; if params: session.execute(stmt, params) thing.

  2. Log in to comment