Search code examples
pythonmysqlsqlpymysql

use a variable multiple times in a sql statement with pymysql

[edit: removed the where statement]

I'm trying to update a table when duplicate keys are passed in I have a list like

examplelist = [[key1, value1], [key2, value2], [key3, value3]]

and a table with, for example, key1 and key3 already in the table with different values, so we want to update those rows and insert the key/value pair key2 value2 in. The table was created with:

cursor.execute('CREATE TABLE exampletable (keycolumn FLOAT, valuecolumn FLOAT)')
cursor.execute('CREATE INDEX keycolumn_index ON exampletable (keycolumn DESC)')

I was trying to use

sql = "INSERT INTO exampletable (keycolumn, valuecolumn) VALUES (%s, %s)"\
" ON DUPLICATE KEY UPDATE valuecolumn = VALUES(valuecolumn)"
cursor.executemany(sql, examplelist)

but I get a bunch of rows with the same keycolumn value

I can just change the input to a list of 3 element lists but this seems really sloppy, I imagine there's a more elegant way

Solution

  • The problem is using float type for the index. This leads to unpredictable behavior as equality checking doesn't work well for float types. use something like DECIMAL(total_precision, decimal_precision) for the key type instead.

    Also, the query can be simplified to initialize the key on declaring the table.

    cursor.execute('CREATE TABLE exampletable (keycolumn DECIMAL(10,2) PRIMARY KEY, valuecolumn DECIMAL(10,2))')