1. Michael Bayer
  2. sqlalchemy
  3. Issues


Issue #169 resolved

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

Michael Bayer
repo owner created an issue

test case:

from sqlalchemy import *
    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
insert=t1.insert(values=dict(familyname='simpson', registered="1960-09-15 12:30:10"))
# this works too
#insert.execute(name='bart', registered="1960-09-15 12:30:10") 
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.

Comments (5)

  1. Anonymous

    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
    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)
    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')
    FAIL: testinsert (objectstore.DefaultTest)
    Traceback (most recent call last):
      File "/home/robin/devel/sqlalchemy/test/objectstore.py", line 435, in testinsert
    Ran 225 tests in 7.422s
    FAILED (failures=4, errors=11)
  2. Michael Bayer reporter

    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.

  3. Log in to comment