Using a Python variable in MySQL query

Question

I am trying to write a function that takes the variable written in the function placeholder() written below and then uses that in MySQL queries. I have written up an example below:

import MySQLdb
connection = MySQLdb.connect(host = "localhost", user = "root", 
 passwd = "", db = "cars")
cursor = connection.cursor()
def placeholder(placeholder_variable):
 sql = "TRUNCATE TABLE %s"
 cursor.execute(sql, placeholder_variable)
placeholder('car_brands')

When I try to run this program I get the following error:

ProgrammingError: (1064, "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 ''car_brands'' at line 1")

This is for a university assignment and MUST use the placeholder('variable') format to receive the variable. I have spent literally hours searching the internet trying to find a solution to this please help :/

Answer 1

sql = "TRUNCATE TABLE " + placeholder_variable + ";"
cursor.execute(sql)

• Jimbo: Also, just to clarify, this is only for metadata such as the table name. Data should still be passed using the second input argument to cursor.execute See also: stackoverflow.com/questions/51529641/…

Answer 2

SQL parameters cannot be used for metadata with MySQL. You will need to sanitize the value and substitute it normally.

• Veiocity: Thanks for the quick response. I am only new at programming and am not sure how I would do this. Could you please help me out?
• Ignacio Vazquez-Abrams: Use a regex to make sure there are no characters that shouldn't be there, and then substitute it in normally.
• Veiocity: I have just done some research on what a regex is but I still don't understand how to use one for what I need. Any chance you could please give me some example code? I would really appreciate it. This stuff is a bit over my head as a novice but this assignment is due in 24 hours :S
• glglgl: As long as there is no user input which gets used in this place, the sanitation is optional at this place. In order to stay more flexible, it might be approriate to put backticks around the variable, such as sql = "TRUNCATE TABLE `" + placeholder_variable + "`".