Error in SQL syntax while querying in MariaDB

Question

I have a table named Container in mariadb having three fields container_id, mt_date and age.

What i am trying to do is, to update/set new values to the age field corresponding to the specific container_id, every time the db is loaded. I have kept the age and corresponding container_id in a python dictionary as a value and key, respectively. Than i loop through the dictionary and try to update age like this -

for i in list(age_dict):
    frappe.db.sql("update Container set age = age_dict[i] where container_id = i")

Here, frappe.db.sql() is the db connecting command for my framework.

I am constantly getting this error message-

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[i] where container_id = i' at line 1")

I have checked my sql query code several times, but couldn't find the syntax error. Looking for help.

Answer 1

The python code you have inside your SQL statement is never interpreted. The database is literally trying to execute the command update Container set age = age_dict[i] where container_id = i which is indeed invalid syntax. You should use parameterization, which will help prevent SQL injection and will easily format the SQL command. The syntax is almost identical to string interpolation, but you pass the values (as a tuple) as a second parameter to frappe.db.sql().

for key in list(age_dict):
    frappe.db.sql(
        "update Container set age = %s where container_id = %s",
        (age_dict[key], key)
    )