insert of a sequence of dictionaries

[Moshe C.]

The following line:
r = cnnctn.execute(insert(t),
{'xkey': 'k1','yval':1},
{'xkey': 'k2','yval':2},
{'xkey': 'k3'})

Cause the following :
INSERT INTO `A` (xkey, yval) VALUES (%s, %s)
[['k1', 1], ['k2', 2], ['k3', 1]]

i.e. the unspecified value in the 3rd dict is copied from the 1st one.

This is quite surprising as I would have expected null() to be used in
such cases.

Is there a rationale behind this?
Is there a way to have some non-full dicts as in the above example so
that it will behave as I expected?

[Michael Bayer]

theres a bug there, but the expected behavior is still not want you
want. it's expected to be "1" and not "2", because the statement is
compiled against the first set of parameters in the list. its the
equivalent of:

c = insert(t, values={'xkey':'k1', 'yval':1}).compile()
c.execute(<params>)

Ive simpliied this in r3452 so that an insert or update statement
will compile against the "keys" present in the first argument and not
the values, so missing parameters default to None instead of anything
which was present in a previous set of arguments.

But, its still invalid to say:

t.insert().execute({'a':1}, {'a':2, 'b':5}, {'a':6, 'b':7})

because we use the first expression in the list in order to compile.
Scanning the entire list of parameters to determine the full list of
keys to compile against is not an option here because executemany()
calls are intended to handle potentially tens of thousands of entries
and we already have enough overhead in that area (its possible that a
significant reorganization of how the compile step/execute parameters
are sent could make it more feasable).

the really *correct* behavior might even be to raise an error when
inconsistent parameter dictionaries are detected - your logic can
still fail if your dictionary with missing keys is the first argument
in the list. The above behavior might be construed as a silent
failure case.

If you want to force compilation against a specific set of keys, add
them to your insert statement:

t.insert(values={'a':None, 'b':None})

hope this helps....