[DB-SIG] MySQLdb - syntax question

Chris Cogdon chris at cogdon.org
Mon Apr 24 20:51:19 CEST 2006


On Apr 24, 2006, at 11:35, Patty wrote:

> I have the following code:
>
> UPDATE mytable SET col1 = %s, col2 = %s, col3 = %s, col4 = %s,
>     col5 = %s, col6 = %s   WHERE name = %s
>     """, (tuple(entries)))
> In this example, entries is a list of numbers and the last element is 
> a name of
> a target.
>
> This works fine. But, there will be times when new columns will be 
> added based
> on a dictionary I have. I've been asked not to hard code the column 
> names (i.e.
> col1, col2, etc.). I need to know if it is possible to put 
> placeholders instead
> of column names (i.e. %s=%s -> column name = column value). If so, 
> what is the
> right syntax for that?

Unfortunately, using the %s syntax is going to put the name of the 
column in quotes, and so MySQL will think you're trying to assign one 
string to another, rather than a string to a column. Instead, you'll 
need to piecemeal your string from parts manually, like this:

d = { ... your dictionary of column names and values }

d_items = d.items ()   # doing it this way to ensure that we dont get 
inconsistent ordering

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 ] ) )


-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris at cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL



More information about the DB-SIG mailing list