"values" keyword argument of insert() clobbered by defaults

[Mike Bayer]

test case:

from sqlalchemy import *
e=create_engine('sqlite://')
t1=Table('t1',e,
    Column('id', Integer, nullable=False,primary_key=True),
    Column('name', String, nullable=False,default='anon'),
    Column('familyname', String, nullable=False,default='anon'),
    Column('registered', DateTime,nullable=False,default='1980-12-10 00:00:00')
#    Column('registered', DateTime,nullable=False) # uncomment this to make it work
    )
t1.create()
insert=t1.insert(values=dict(familyname='simpson', registered="1960-09-15 12:30:10"))
insert.execute(name='bart')
# this works too
#insert.execute(name='bart', registered="1960-09-15 12:30:10") 
result=t1.select().execute()
for r in result:
    print r

the typical way to do "insert" is that all the bind parameter values are supplied to the execute() method, so this is not a huge deal, but the parameters bound to a compiled object should be reconciled with the runtime parameters. interestingly, the example works if you create a compiled object with the binds directly:

insert=t1.insert().compile(engine=e, parameters=dict(familyname='simpson', registered="1960-09-15 12:30:10"))

so that would imply its definitely something local to the way the Insert object processes the "values" parameter.

[Former user]

Thanks for your detailed response to ticket:165. Thats helped me loads.

Insert and Update do not propogate the values captured in __init__ to execute. They are captured during __init__ and discarded. The Executor wrapper, used by ClauseElement to manage the compile, which has a back ref to the clause element, doesn't pull them through in any way either. What am I missing ?

I might abandon this now and wait for somone who knows what they are doing to have a look. Everything else is excelent and I don't urgently need values/defaults to be as flexible as this ticket and ticket:120 would make them.

Trying not to obssesse over compile but this section from the docs had me thinking of them as a kind of curry function for SQL statements: http://www.sqlalchemy.org/docs/sqlconstruction.myt#constructing_bindparams_precompiling

---

• file:repro_insertupdate_issues.py
• file:ticket_169_tentative_fix.patch

With the attatched patch I have values, ColumnDefaults and PassiveDefaults playing nicely together. The only thing that doesn't work in the repro case, also attatched, is precompiled updates (I think I need to look at the onupdate column flag more closely). The repro case runs the whole lot through both sqlite & mysql.

The patch includes, and requires, the change I submited as a patch for ticket:165.

Don't understand why I need to comment out engine in alltests.py in order to run the tests (with or without patch!).

pre patch I get a single test failure & 10 errors, teststandalone (defaults.SequenceTest).

With the patch I get 4 fails & 11 errors:

======================================================================
FAIL: testinsert (defaults.DefaultTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/robin/devel/sqlalchemy/test/defaults.py", line 86, in testinsert
    self.assert_(t.engine.lastrow_has_defaults())
AssertionError

======================================================================
FAIL: teststandalone (defaults.SequenceTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/robin/devel/sqlalchemy/test/defaults.py", line 134, in teststandalone
    self.assert_(x == 1)
AssertionError

======================================================================
FAIL: testrefresh (mapper.MapperTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/robin/devel/sqlalchemy/test/mapper.py", line 102, in testrefresh
    self.assert_(u.user_name == 'jack')
AssertionError

======================================================================
FAIL: testinsert (objectstore.DefaultTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/robin/devel/sqlalchemy/test/objectstore.py", line 435, in testinsert
    self.assert_(h2.hoho==h4.hoho==h5.hoho==self.hohoval)
AssertionError

----------------------------------------------------------------------
Ran 225 tests in 7.422s

FAILED (failures=4, errors=11)

[Former user]

reproduction of the issue & mini test harness

[Former user]

patch that resolves them, but introduces 3 aditional test failures

[Mike Bayer]

sorry, this is not quite how it works. a Compiled object consists of two basic things: the string version of the statement, and a dictionary of all compiled-in parameters. these parameters are populated by three distinct sources: the parameters that are assocaited with the ClauseElement object (i.e. the VALUES/SET parameters added to the Insert/Update in this case), the parameters that are passed to the Compiler constructor, and a "None" placeholder corresponding to the defaults placed upon Columns in Tables that are being inserted or updated. the full structure of parameters to be executed is then accessible via the get_params() method off the Compiled object, which itself takes another dictionary of values that can be used to override the compiled-in parameters.

it is the category of "None placeholder corresponding to column defaults" that was most recently added, and is the sole source of this issue. it occurs entirely at the level of get_params() returning the wrong results, independently of any subsequent execution.

I have checked in a fix for this particular issue which places the "create None placeholders for defaults" step at a slightly different place in the organizing of bind parameters so that they dont clobber the built-in binds. this code started as something a lot simpler and has been added onto in order to support new features; this is why its hard to follow. but it takes place entirely in in ansisql.py.

also added some unittests to defaults.py for this. changeset changeset:1353.

[Former user]

Ah, excelent. That makes sense now thanks.