In python-sql interfacing how do I use string parameters properly?

July 1, 2023

I am writing a function to retrieve an item from a table ‘MenuEntree’ but there is apparently a syntax error in the cur.execute() argument.

import mysql.connector as mc
con = mc.connect(user = 'root',host = 'localhost', password = 'something',database = 'something')
cur = con.cursor()

def getItem(ID,course):
    currentItem = ''
    cur.execute("select Item from %s where ID = (%s)",("Menu" + course,ID))
    currentItem = cur.fetchone()
    return currentItem

This is the table:

(https://i.stack.imgur.com/Dy5Ty.png)

Error message:

Exception has occurred: ProgrammingError
1064 (42000): 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 ‘} where ID = (%s)’ at line 1
_mysql_connector.MySQLInterfaceError: 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 ‘} where ID = (%s)’ at line 1

Tried messing around with the brackets and parameters but nothing worked

>Solution :

To use string parameters properly in Python SQL interfacing, you should use placeholders (%s) in the SQL query string and pass the values as a tuple as the second argument to the cur.execute() method. However, you cannot use placeholders for table or column names in SQL queries. Instead, you can use string formatting to safely insert the table name into the query.

import mysql.connector as mc

con = mc.connect(user='root', host='localhost', password='something', database='something')
cur = con.cursor()

def getItem(ID, course):
    currentItem = ''
    table_name = "Menu" + course
    query = "SELECT Item FROM {} WHERE ID = %s".format(table_name)
    cur.execute(query, (ID,)) # passing placeholder in tuple format
    currentItem = cur.fetchone()
    return currentItem