PYTHON - Dynamically update multiple columns using a custom MariaDB connector

Question

While reading this question: SQL Multiple Updates vs single Update performance

I was wondering how could I dynamically implement an update for several variables at the same time using a connector like MariaDB's. Reading the official documentation I did not find anything similar.

This question is similar, and it has helped me to understand how to use parametrized queries with custom connectors but it does not answer my question.

Let's suppose that, from one of the views of the project, we receive a dictionary.

This dictionary has the following structure (simplified example):

{'form-0-input_file_name': 'nofilename', 'form-0-id': 'K0944', 'form-0-gene': 'GJXX', 'form-0-mutation': 'NM_0040(p.Y136*)', 'form-0-trix': 'ZSSS4'}

Assuming that each key in the dictionary corresponds to a column in a table of the database, if I'm not mistaken we would have to iterate over the dictionary and build the query in each iteration.

Something like this (semi pseudo-code, probably it's not correct):

query = "UPDATE `db-dummy`.info "
for key in a_dict:
    query += "SET key = a_dict[key]"

It is not clear to me how to construct said query within a loop.

What is the most pythonic way to achieve this?

ggordon · Accepted Answer · 2021-05-10T14:11:28.720

2

Although this could work.


query = "UPDATE `db-dummy`.info "
for index, key in enumerate(a_dict):
    query = query + ("," if index != 0 else "") +" SET {0} = '{1}'".format(key,a_dict[key])

You should consider parameterized queries for safety and security. Moreover, a dynamic dictionary may also raise other concerns, it may be best to verify or filter on a set of agreed keys before attempting such an operation.


query = "UPDATE `db-dummy`.info "
for index, key in enumerate(a_dict):
    query = query + ("," if index != 0 else "") +" SET {0} = ? ".format(key)

# Then execute with your connection/cursor

cursor.execute(query, tuple(a_dict.values()) )

edited May 10 '21 at 14:11

answered May 10 '21 at 02:35

ggordon

9,790
2
14
27

1

It looks like the dictionary is dynamic, so parametarizing both its key and value is necessary to actually prevent sqli. – CyberSrikanth May 10 '21 at 02:47
1

@CyberSrikanth Good eye. I agree that a dynamic dictionary has some concerns, I'll update the answer with a recommendation that the dictionary keys be verified before attempting such an update – ggordon May 10 '21 at 02:49

score 0 · Answer 2 · answered May 10 '21 at 08:21

This is what I did (inspired by @ggordon's answer)

query = "UPDATE `db-dummy`.info "
for index, key in enumerate(a_dict):
    if index == 0:
        query = query + "SET {0} = ?".format(key)
    else:
        query = query + ", {0} = ?".format(key)
query += " WHERE record_id = " + record_id

And it works!

PYTHON - Dynamically update multiple columns using a custom MariaDB connector

2 Answers2