MySQL cur.execute with format

Question

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

KC. · Accepted Answer · 2018-12-20 13:48:46Z

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

edited Dec 20, 2018 at 13:48

answered Dec 20, 2018 at 13:36

KC.

3,1072 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rostislav Aleev Over a year ago

sql = "UPDATE intmt_vals SET {col1} = %s WHERE {col2} =%s". expression expected got { Strange, That's what about IntelliJ warns.

KC. Over a year ago

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. Over a year ago

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. Over a year ago

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. Over a year ago

My first and second comment is to explain your final sql_statement will not contain {}, so you need not to care about IntelliJ warns.

|

Collectives™ on Stack Overflow

MySQL cur.execute with format

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related