InvalidRequestError with compiled INSERT statement (SA v0.6.0)

[Former user]

# Environment: CPython 2.5 on AMD64 Linux
# SA 0.6.0
# EMail: [e-mail]

db = create_engine('sqlite:///:memory:') # same error with MySQL
metadata = MetaData(db) 
t_user = Table("im060_user",metadata,
  Column("id",Integer,primary_key=True), # no matter whether it's a primary key, or not
  Column("foo",String(10)),
  )
metadata.drop_all()
metadata.create_all()

#ins = t_user.insert() # works well
#ins = t_user.insert().values(id=777).compile() # works well (except that foo remains empty...)
#ins = t_user.insert().values(foo="anything").compile() # works well
ins = t_user.insert().compile() # execute fails with SA 0.6.0, OK with 0.5.8
print ins

#ins.execute({"foo":"foovalue","id":None}) # works well
ins.execute({"foo":"foovalue"}) # InvalidRequestError: A value is required for bind parameter 'id'

#Output
#############################################################
#
#INSERT INTO im060_user (id, foo) VALUES (%s, %s)
#Traceback (most recent call last):
#  File "/yyy/test.py", line 32, in <module>
#    ins.execute({"foo":"foovalue"}) # InvalidRequestError: A value is required for bind parameter 'id'
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/engine/base.py", line 719, in execute
#    return e._execute_compiled(self, multiparams, params)
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/engine/base.py", line 1579, in _execute_compiled
#    return connection._execute_compiled(compiled, multiparams, params)
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/engine/base.py", line 1193, in _execute_compiled
#    parameters=self.__distill_params(multiparams, params)
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/engine/base.py", line 1272, in __create_execution_context
#    return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/engine/default.py", line 366, in __init__
#    grp,m in enumerate(parameters)]
#  File "/xxx/SQLAlchemy-0.6.0/lib/sqlalchemy/sql/compiler.py", line 277, in construct_params
#    % bindparam.key)
#sqlalchemy.exc.InvalidRequestError: A value is required for bind parameter 'id'

[Mike Bayer]

this is actually a feature, not a bug. Once an insert() construct is compiled, its parameters are fixed:

"INSERT INTO table (a, b) VALUES (?, ?)"

It's an error for it to "assume" None and this has been fixed in 0.6.

If you're looking for a performance speedup based on storing the compiled() form of clauses around, look into execution_options(compiled_cache={}). this can be called against a series of statements or on a Connection. http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.Executable.execution_options