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.

wpercy · Accepted Answer · 2015-12-19 05:10:03Z

3

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

edited Dec 19, 2015 at 5:10

answered Dec 19, 2015 at 5:00

wpercy

10.2k4 gold badges35 silver badges50 bronze badges

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

3 Comments

mhawke Over a year ago

It's also worth pointing out that the for loop would be better written as for key in age_dict: as there is no benefit to converting its keys to a list first.

ni8mr Over a year ago

I am converting it to list to preserve the state of the dictionary. May be in my query, it doesn't matter, but if i mistakenly try to delete a key, at least it willn't crash my code.

mhawke Over a year ago

@ni8mr: If you mean that you accidentally execute del key in your loop it won't make any difference. Either way you will be working with a reference to the key held by the actual dictionary. Deleting that reference will not delete the actual key because the dictionary will still hold a reference to it.

Collectives™ on Stack Overflow

Error in SQL syntax while querying in MariaDB

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related