MySQL Syntax error when inserting data (Python)

Question

I have the following:

def insertion(database, data):
       parsed_data = json.loads(data)
       cursor = database.cursor()
       cursor.execute("INSERT INTO Food (category, taste) VALUES (%s, %s)" % (parsed_data["category"], parsed_data["taste"]))
       database.commit()

However, an error occurs in the in line where I do cursor.execute(...). Specifically: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Sweet)' at line 1.

Printing out my dictionary reveals no extra parentheses at the end of the parsed_data["taste"]. It seems like whatever reason, an extra parentheses is being added which is being interpreted by mySQL has actual SQL syntax? I don't know why this is appearing.

Answer 1

If the values are strings, you need quotes around them. But the correct solution is to use parameters in cursor.execute() rather than string formatting.

cursor.execute("INSERT INTO Food (category, taste) VALUES (%s, %s)", (parsed_data["category"], parsed_data["taste"]))

Answer 2

VALUES (%s, %s)" % (parsed_data["category"], parsed_data["taste"])

That's your problem. You've got to quote your values. And python's string formatting doesn't do that.

VALUES ('%s', '%s')" % (parsed_data["category"], parsed_data["taste"])

This would probably work better, but even then that's a terrible solution prone to SQL Injection.

Don't reinvent the wheel, use a SQL library for python. We call those "ORM"s

I personally love django's if that can help.