0

I don't properly understand how to use .format for SQL statement in my case. Can I use it to define a column name?

It works with one value, but not with whole tuples.

sql_stmt = ("""UPDATE intmt_vals SET {0} = {1} WHERE ID={2};""".format(column, values, ids))
cur.execute(sql_stmt)

column is a key from an outter dict column = k, e.g. 'column1'

values are values from inner dict, vals = tuple(dict.get(k).values()) e.g. (a, b, c, d, e, f,)

ids are keys from inner dict, e.g. ids = tuple(dict.get.(k).keys()).e.g (1,2,3,4,5,6)

Error mysql.connector.errors.DataError: 1241 (21000): Operand should contain 1 column(s).

Rostislav Aleev
  • 351
  • 5
  • 19

1 Answers1

2

Yes you can, and it is easy to do. But you should only use format on setting columns name. Reason: MySQL parameterized queries. And why you got Operand should contain 1 column(s) is your sql statement only have one column but multiple value, like column1 = (a, b, c, d, e, f).

sql = "UPDATE test SET {col} = %s WHERE {col} = %s;"

data = {"a":"b","c":"d"}
print(data.items())
try:
    cur.executemany(sql.format(col="msg"),data.items())
    con.commit()
except Exception as e:
    con.rollback()
    raise e
KC.
  • 2,981
  • 2
  • 12
  • 22
  • `sql = "UPDATE intmt_vals SET {col1} = %s WHERE {col2} =%s"`. _expression expected got {_ Strange, That's what about IntelliJ warns. – Rostislav Aleev Dec 20 '18 at 14:16
  • But this statement is fine in Python syntax. because your final sql_stmt will be `"UPDATE intmt_vals SET col1 = %s WHERE col2 =%s"`. And it is safe if others can not control col value. – KC. Dec 20 '18 at 14:44
  • In another way to express it, try this code `a = "This is: {}".format("ad") print(a.format("bcde"))`. After you used `format` to the string, the string value will be changed and `{}` will be spent. – KC. Dec 20 '18 at 14:53
  • Yep, got it. Final `sql = "UPDATE test SET {0} = %s WHERE {1} = %s;".format(col1,col2)`. Do you mean, there is no possibility for a SQL injection even with `format`? – Rostislav Aleev Dec 20 '18 at 15:32
  • 1
    Nope, if other can change your col1 or col2 value. Because `format` insert data into string straightly. But it is easy to solve it by using regex to check whether col's value is legal. – KC. Dec 21 '18 at 04:42
  • 1
    My first and second comment is to explain your final sql_statement will not contain `{}`, so you need not to care about IntelliJ warns. – KC. Dec 21 '18 at 04:47