Passing a list in a WHERE clause python mysql

Question

I am trying to obtain a list of speed values from my table in my database. I was wondering if their was an SQL command/method which would allow me to pass a list of CharIDs in the WHERE function, so that I would not have to create a FOR loop and carry out the four searches separately.

party_ids= [4, 3, 1, 2]

def initializegame(party_ids):
    #Get selected party members IDS
    print(party_ids)
    #Obtain Speeds
    fetchspeed=("SELECT startspeed FROM characters WHERE CharID=%s")
    print(fetchspeed)
    mycursor.execute(fetchspeed,party_ids)
    myspeeds=mycursor.fetchall()
    print(myspeeds)
    print("done")

I'm relatively new to SQL and I may be missing something major.

I did try: passing a list to a where clause Yet that was a little too hard to follow.

Thanks in advance

Answer 1

Use the IN keyword when you have multiple values you are filtering on. In this case you have known integer values, so SQL injection is not an issue. But your function was written where it could be passed arbitrary values where SQL injection could be an issue or you might be passed string literals that needed to be escaped. So it pays to address these issues by letting the database driver process the actual parameters:

party_ids= [4, 3, 1, 2]

def initializegame(party_ids):
    #Get selected party members IDS
    print(party_ids)
    #Obtain Speeds

    in_params = ','.join(['%s'] * len(party_ids))
    sql = "SELECT startspeed FROM characters WHERE CharID IN (%s)" % in_params
    mycursor.execute(sql, party_ids)

    myspeeds=mycursor.fetchall()
    print(myspeeds)
    print("done")

The variable sql becomes:

SELECT startspeed FROM characters WHERE CharID IN (%s,%s,%s,%s)

And then the execute statement passes as the second parameter a list of the 4 IN values.

Answer 2

You can use IN keyword for filtering based on list.

party_ids= [4, 3, 1, 2]

def initializegame(party_ids):
    #Get selected party members IDS
    print(party_ids)
    #Obtain Speeds

    ids_string = ','.join(str(id) for id in party_ids)
    mycursor.execute("SELECT startspeed FROM characters WHERE CharID IN ({0})".format(ids_string))

    myspeeds=mycursor.fetchall()
    print(myspeeds)
    print("done")

You can find more information on IN keyword and how it works from this MySQL blog tutorial.