[DB-SIG] MySQLdb - syntax question
chris at cogdon.org
Mon Apr 24 22:58:26 CEST 2006
On Apr 24, 2006, at 13:37, Patty wrote:
> Hi again,
>> query_string = "update mytable set " + ",".join ( [ key + "=%s" for
>> key, value in d_items ] )
>> cursor.execute ( query_string, tuple ( [ value for key, value in
>> d_items ] ) )
> Thanks for your prompt reply. I kinda got it, but the the part that I
> forgot to
> mention was that the values are not retrieved from the dictionary.
> What I
> retrieve from the dictionary are the column names and the target. The
> values are
> in a separate list, so how would it be in this case? I'm sorry if I
> sound silly,
> but I'm new to this.
I don't know what you mean by 'target' :)
But, that kind of fiddling is more in the python realm than the
DB/mysql realm... ie, as far as the dbabi side is concerned, the values
are passed in through a tuple, and connected to the query string with %
marks, and the column names have to be manually inserted into the query
string (which is what the + and join items did) because there's no
dbapi support for manipulating non-literal-value entries in the query
ie, you can't do this:
colname = "column1"
value = "John"
cursor.execute ( "update mytable set %s=%s", ( colname, value ) )
Because that would result in this SQL:
update mytable set "column1"="John"; # the mysql connector might use
single quotes, I'm not sure
And that's not what you wanted. Instead, if it was just ONE value,
you'd do this:
cursor.execute ( "update mytable set "+colname+"=%s", ( value, ) )
Would would send this SQL:
update mytable set column1="John";
Now, my example above assumed all the column names, and values, came
from one dictionary, and used join and list comprehensions to expand
everything. They're just python tricks, not mysql or db-api tricks.
("`-/")_.-'"``-._ Chris Cogdon <chris at cogdon.org>
. . `; -._ )-;-,_`)
(v_,)' _ )`-.\ ``-'
_.- _..-_/ / ((.'
((,.-' ((,/ fL
More information about the DB-SIG