How to use PyMySQL to return a list of entry ID's and use these ID's for further SQL queries

so I have a table in a MySQL database called games. I'm trying to use a python Lambda function to run a search on the database and create a dictionary to return as a JSON file to the app that's calling the connected API.

The problem I am having is that my first step is to run a query that returns a list of the game ID's and then iterate over these ID's doing a select * where the gameId matches that ID and insert the ID as a key into a dictionary and the result from the query as the value. This doesn't work because the returned list of ID's isn't a list of numbers it looks like this: ((4,), (5,), (6,), (7,), (8,), (10,)). I can easily convert the id's to a string or something for them to be the key but it doesn't seem to work when I try to convert them to an int to use in the look and the SQL select statements so I thought it's best to try work out why the list is returning this way.

This part of the code looks like:

    gameDay = event['gameDay']
    gameMonth = event['gameMonth']
    gameYear = event['gameYear']                
    secondGameDay = event['secondGameDay']
    secondGameMonth = event['secondGameMonth']
    secondGameYear = event['secondGameYear']   
    formatter = '%Y-%m-%e%'
    gameIds = []
    gameDetails = {}

    search_sql = f"SELECT gameId FROM Games WHERE CAST(CONCAT_WS('-',gameYear,gameMonth,gameDay) AS date) BETWEEN DATE_FORMAT('2023-5-01', '{formatter}') AND DATE_FORMAT('2023-12-01', '{formatter}')"
    cur.execute(search_sql)
    gameIds = cur.fetchall()
    print(gameIds)
    for id in gameIds:
        idInt = str(id).replace(',', '')
        list_sql = f"SELECT * FROM Games WHERE gameId = '{id}'"
        cur.execute(list_sql)
        gameDetails[str(id).replace(',', '')] = cur.fetchall()

    result = gameDetails
    return result

Sorry I know this code is really unclean. It's full of my troubleshooting I've tried which will be removed.

The response ends up as:

{
  "(4)": [],
  "(5)": [],
  "(6)": [],
  "(7)": [],
  "(8)": [],
  "(10)": []
}

and the print requests end up as:

((4,), (5,), (6,), (7,), (8,), (10,))
{'(4)': (), '(5)': (), '(6)': (), '(7)': (), '(8)': (), '(10)': ()}

Any help appreciated thanks.

Solution

The problem is that gameIds is a list of tuples. cur.fetchall() returns each row as a tuple, even if it's only one column. Then when you substitute that into the SQL

f"SELECT * FROM Games WHERE gameId = '{id}'"

you get WHERE gameId = '(1)' instead of WHERE gameId = '1'. Since none of the gameId values have () in them, this doesn't match anything.

There's no need for multiple queries, though. Just do one query that gets all the rows that match the date condition. And there's no need to call DATE_FORMAT(), MySQL will parse the date strings itself automatically.

search_sql = f"SELECT gameId, * FROM Games WHERE CAST(CONCAT_WS('-',gameYear,gameMonth,gameDay) AS date) BETWEEN '2023-5-01' AND '2023-12-01'"
cur.execute(search_sql)
for row in cursor:
    gameDetails[row[0]].setdefault([]).append(row[1:])