MySQL Query Passing Two Variables Python SQLConnector

Question

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:\Users\joshb\PycharmProjects\pythonProject2\venv\lib\site-packages\mysql\connector\cursor.py", line 540, in execute
    stmt = RE_PY_PARAM.sub(psub, stmt)
  File "C:\Users\joshb\PycharmProjects\pythonProject2\venv\lib\site-packages\mysql\connector\cursor.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:\Users\joshb\PycharmProjects\pythonProject2\venv\lib\site-packages\mysql\connector\cursor.py", line 539, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:\Users\joshb\PycharmProjects\pythonProject2\venv\lib\site-packages\mysql\connector\cursor.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

snakecharmerb · Accepted Answer · 2022-01-25 07:21:20Z

2

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.

answered Jan 25, 2022 at 7:21

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

3 Comments

Josh Over a year ago

Thanks for the response, I updated my question, I believe I am following the correct syntax now but getting an error still.

snakecharmerb Over a year ago

My bad - I didn't look hard enough at the preceding code. fetchall() returns a list of tuples, so SELECT team FROM tbl will result in [('TeamA',), ('TeamB',), ... ]. So your values parameters should be the first element of each tuple (team[0], team[0]). (Or slightly tricksier, team + team, which will merge the two tuples into a single tuple).

Josh Over a year ago

Thanks a ton! This had me stopped for hours, the [0] was the game changer. Appreciate your help.

Collectives™ on Stack Overflow

MySQL Query Passing Two Variables Python SQLConnector

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related