"values" keyword argument of insert() clobbered by defaults
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.
Comments (5)
-
Account Deleted -
Account Deleted - attached repro_insertupdate_issues.py
reproduction of the issue & mini test harness
-
Account Deleted - attached ticket_169_tentative_fix.patch
patch that resolves them, but introduces 3 aditional test failures
-
reporter - changed status to resolved
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.
-
Account Deleted Ah, excelent. That makes sense now thanks.
- Log in to comment
Thanks for your detailed response to ticket:165. Thats helped me loads.
Insert
andUpdate
do not propogate the values captured in__init__
to execute. They are captured during__init__
and discarded. TheExecutor
wrapper, used byClauseElement
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_precompilingWith the attatched patch I have
values
,ColumnDefaults
andPassiveDefaults
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: