MySQL Query Passing Two Variables Python SQLConnector

I can’t seem to get two values passed on a query, I can get one variable to pass no problem, can’t figure out what I’m doing wrong, error posted when I try to use the two variable code, one variable code passes.

One Variable:

mycursor = mydb.cursor()

mycursor.execute("SELECT `HOME TEAM:` FROM `nbav8` WHERE `HOME TEAM:` is NOT NULL")

myresult = mycursor.fetchall()

awayuyu = []
homeuyu = []
kt = "Atlanta"
for team in myresult:
    mycursor = mydb.cursor()

    mycursor.execute("SELECT `ACTUAL OVER UNDER RESULT:` FROM `previous_losses_nbav1_with_date_agg` WHERE `HOME TEAM:` = %s", team)
    myresult = mycursor.fetchall()
    print(myresult)

print(myresult)

Two Variable:

mycursor = mydb.cursor()

mycursor.execute("SELECT `HOME TEAM:` FROM `nbav8` WHERE `HOME TEAM:` is NOT NULL")

myresult = mycursor.fetchall()

awayuyu = []
homeuyu = []
kt = "Atlanta"
for team in myresult:
    mycursor = mydb.cursor()
    mycursor.execute("SELECT `ACTUAL OVER UNDER RESULT:` FROM `previous_losses_nbav1_with_date_agg` WHERE `HOME TEAM:` = %s OR 'AWAY TEAM:' = %s;", team, team)
    myresult = mycursor.fetchall()
    print(myresult)

print(myresult)

Error:

Traceback (most recent call last):
  File "C:/Users/joshb/PycharmProjects/NBA_V8/grabbing_page_data.py", line 26, in <module>
    mycursor.execute("SELECT `ACTUAL OVER UNDER RESULT:` FROM `previous_losses_nbav1_with_date_agg` WHERE `HOME TEAM:` = %s OR 'AWAY TEAM:' = %s;", team, team)
  File "C:UsersjoshbPycharmProjectspythonProject2venvlibsite-packagesmysqlconnectorcursor.py", line 540, in execute
    stmt = RE_PY_PARAM.sub(psub, stmt)
  File "C:UsersjoshbPycharmProjectspythonProject2venvlibsite-packagesmysqlconnectorcursor.py", line 77, in __call__
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Updated code after answer:

mycursor = mydb.cursor()

mycursor.execute("SELECT `HOME TEAM:` FROM `nbav8` WHERE `HOME TEAM:` is NOT NULL")

myresult = mycursor.fetchall()
print(myresult)
yeet = 0
awayuyu = []
homeuyu = []
kt = "Atlanta"
temp1 = "Miami"
temp2 = "Toronto"
for team in myresult:
    data1 = team
    data2 = team
    mycursor = mydb.cursor()
    sql = """SELECT `ACTUAL OVER UNDER RESULT:` FROM `previous_losses_nbav1_with_date_agg` WHERE `HOME TEAM:` = %s OR 'AWAY TEAM:' = %s"""
    mycursor.execute(sql, (data1, data2), multi=True)
    myresult = mycursor.fetchall()
    print(myresult)

Error:

Traceback (most recent call last):
  File "C:/Users/joshb/PycharmProjects/NBA_V8/grabbing_page_data.py", line 28, in <module>
    mycursor.execute(sql, (data1, data2), multi=True,)
  File "C:UsersjoshbPycharmProjectspythonProject2venvlibsite-packagesmysqlconnectorcursor.py", line 539, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:UsersjoshbPycharmProjectspythonProject2venvlibsite-packagesmysqlconnectorcursor.py", line 421, in _process_params
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type

I am not sure I still get this error

Answer

Place you parameters in a tuple, like this:

cursor.execute(sql, (param1, param2))

The MySQL Connector cursor.execute method accepts three parameters: the SQL statement, parameters and a flag (multi). When you execute

cursor.execute(sql, team, team)

the final parameter is assumed to be the multi flag. It’s best practice to always pass parameter values as a tuple*, even if there is only one ((param,)). You may get other errors otherwise.

* You can use a list also, but using a tuple is conventional.

Advertisement

Answer