Insert multiple rows fails when using a column's default value

[Former User]

OpenStack's oslo.db contains a TimestampMixin, which defines a created_at column:

created_at = Column(DateTime, default=lambda: timeutils.utcnow())

Attempting to insert multiple rows without explicitly specifying this column will fail. e.g.:

DBError: (sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 20, and there are 19 supplied. [SQL: u'INSERT INTO fixed_ips (created_at, deleted, address, network_id, virtual_interface_id, instance_uuid, allocated, leased, reserved, host) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2015-01-13 14:04:51.046323', 0, '192.168.1.5', 1, None, 'd4473f3a-dbb9-43ce-a30b-51a4583ead5f', 0, 0, 0, '127.0.0.1', 0, '192.168.1.6', 2, None, 'd4473f3a-dbb9-43ce-a30b-51a4583ead5f', 1, 0, 0, 'localhost')]

In the above case, the caller is not passing a value for created_at:

    params = [
        {'reserved': False, 'deleted': 0, 'leased': False,
         'host': '127.0.0.1', 'address': address_1, 'allocated': False,
         'instance_uuid': instance_uuid, 'network_id': network_id_1,
         'virtual_interface_id': None},
        {'reserved': False, 'deleted': 0, 'leased': False,
         'host': 'localhost', 'address': address_2, 'allocated': True,
         'instance_uuid': instance_uuid, 'network_id': network_id_2,
         'virtual_interface_id': None}
    ]
    ... openstack stuff ...
    tab = models.FixedIp.__table__
    insert = tab.insert().values(ips)
    session.execute(insert)

The cause seems to be in _extend_values_for_multiparams:

values.extend(
    [
        (
            c,
            (_create_bind_param(
                compiler, c, row[c.key],
                name="%s_%d" % (c.key, i + 1)
            ) if elements._is_literal(row[c.key])
                else compiler.process(
                    row[c.key].self_group(), **kw))
            if c.key in row else param
        )
        for (c, param) in values_0
    ]
    for i, row in enumerate(stmt.parameters[1:])
)

Note how the tricky innermost code only calls _create_bind_param() 'if c.key in row'. In the above case, created_at is not in the statement parameters. The first row is handled separately, and correctly adds a bind parameter for created_at, hence the error message complains about having only 19 bind values.

[Mike Bayer]

it says "if c.key in row else param". the param is there, the issue here lies only in the conversion to positional.

Test cases are super helpful, here's mine, which if you use a named format, passes:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    created_at = Column(DateTime, default=lambda: datetime.datetime.utcnow())


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

e.execute(
    A.__table__.insert().values([
        {'x': 5}, {'x': 10}
    ])
)

if we use e = create_engine("postgresql://scott:tiger@localhost/test", echo=True, paramstyle='format') or e = create_engine("sqlite://"), then we get errors. in the second case, I get the same error as yours, so that's how i know this is sqlite! (as opposed to that also being in the bug report explicitly ;) )

[Mike Bayer]

now what is odd is why that positional conversion isn't working, so in the logic you mention I think we might need to make a new bindparam() object with the same name, perhaps, i need to recall how that case is supposed to work.

[Mike Bayer]

ok yeah 'param' there is just the string so somehting has to happen that will add an entry to positiontup each time there

[Mike Bayer]

the contract of Python side default callables is that they be called for each row individually. This functionality is not at all built into the "multi VALUES" facility right now, and will change the behavior of an application that currently makes use of python-callable defaults with a name-based paramstyle. to accomplish this means some major changes in crud.py which I'll do today but this would be definitely too destabilizing for 0.9.

[Mike Bayer]

• The multi-values version of :meth:.Insert.values has been repaired to work more usefully with tables that have Python- side default values and/or functions, as well as server-side defaults. The feature will now work with a dialect that uses "positional" parameters; a Python callable will also be invoked individually for each row just as is the case with an "executemany" style invocation; a server- side default column will no longer implicitly receive the value explicitly specified for the first row, instead refusing to invoke without an explicit value. fixes #3288

→ <<cset 92cc232726a0>>

[Mike Bayer]

that was a total crapshow. there's a migration guide on that one coming up in the next readthedocs build.