0

I found the only way how to update only null variables in mysql db with python.

I have this kind of statement:

sql = "INSERT INTO `table` VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)\
ON DUPLICATE KEY UPDATE Data_block_1_HC1_sec_voltage=IF(VALUES(Data_block_1_HC1_sec_voltage)IS NULL,Data_block_1_HC1_sec_voltage,VALUES(Data_block_1_HC1_sec_voltage)),\
`Data_block_1_TC1_1`=IF(VALUES(`Data_block_1_TC1_1`)IS NULL,`Data_block_1_TC1_1`,VALUES(`Data_block_1_TC1_1`)),\
`Data_block_1_TC1_2`=IF(VALUES(`Data_block_1_TC1_2`)IS NULL,`Data_block_1_TC1_2`,VALUES(`Data_block_1_TC1_2`)),\
`Data_block_1_TCF1_1`=IF(VALUES(`Data_block_1_TCF1_1`)IS NULL,`Data_block_1_TCF1_1`,VALUES(`Data_block_1_TCF1_1`)),\
`HC1_HC1_output`=IF(VALUES(`HC1_HC1_output`)IS NULL,`HC1_HC1_output`,VALUES(`HC1_HC1_output`)),\
`Data_block_1_HC1_sec_cur`=IF(VALUES(`Data_block_1_HC1_sec_cur`)IS NULL,`Data_block_1_HC1_sec_cur`,VALUES(`Data_block_1_HC1_sec_cur`)),\
`Data_block_1_HC1_power`=IF(VALUES(`Data_block_1_HC1_power`)IS NULL,`Data_block_1_HC1_power`,VALUES(`Data_block_1_HC1_power`)),\
`HC1_HC1_setpoint`=IF(VALUES(`HC1_HC1_setpoint`)IS NULL,`HC1_HC1_setpoint`,VALUES(`HC1_HC1_setpoint`))\
"

Datablocks are columns in db. Primary key is datetime. Right now there are 8 columns but I will have a lot more variables (more columns). I am not reallz good at python but I dont like the statement because its kind of hardcoded. Could I make this statement somehow in a for cycle or something so It doesnt have to be so long and I dont have to write all the variables manually?

Thx for your help

Matej Petráš
  • 67
  • 11
  • so, what have you tried so far? – ewcz Sep 28 '15 at 08:18
  • nothing because as I wrote I am not that good at python and I just dont know what are my options. I think that I could use something like %s=IF(VALUES(%s)IS NULL,%s,VALUES(%s)) in a for cycle - put each into list and then join it. But I still dont know if there is a better solution. Thats why I am asking those that can use python better than me – Matej Petráš Sep 28 '15 at 08:25

1 Answers1

1

Let's assume that your column names are stored in an array cols. Then in order to generate the "interesting" inner part of the SQL statement above, you could do

',\\\n'.join(map(lambda c: r'`%(col)s` = IF(VALUES(`%(col)s`) IS NULL, `%(col)s`, VALUES(`%(col)s`))' % {'col': c}, cols))

Here, map generates for each element of cols the corresponding line of the SQL statement and join then stitches everything together.

ewcz
  • 12,819
  • 1
  • 25
  • 47