[DB-SIG] MySQLdb - syntax question

Chris Cogdon 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 mailing list