Imploding a List for Use in a Python MySQLdb in Clause

imploding a list for use in a python MySQLDB IN clause

Use the list_of_ids directly:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

That way you avoid having to quote yourself, and avoid all kinds of sql injection.

Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

Passing a list in a WHERE clause python mysql

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.

WHERE IN Clause in python list

Think about the arguments to cursor.execute that you want. You want to ultimately execute

cursor.execute("SELECT * FROM my_table WHERE name IN (%s, %s, %s) AND id = %s AND puid = %s;", ["name1", "name2", "name3", id, pid])

How do you get there? The tricky part is getting the variable number of %ss right in the IN clause. The solution, as you probably saw from this answer is to dynamically build it and %-format it into the string.

in_p = ', '.join(list(map(lambda x: '%s', names)))
sql = "SELECT * FROM my_table WHERE name IN (%s) AND id = %s AND puid = %s;" % in_p

But this doesn't work. You get:

TypeError: not enough arguments for format string

It looks like Python is confused about the second two %ss, which you don't want to replace. The solution is to tell Python to treat those %ss differently by escaping the %:

sql = "SELECT * FROM my_table WHERE name IN (%s) AND id = %%s AND puid = %%s;" % in_p

Finally, to build the arguments and execute the query:

args = names + [id, pid]
cursor.execute(sql, args)

Python MySQL SELECT WHERE with list

Happily, you can do this in a single query using a LEFT JOIN.

cursor = mydb.cursor()

cursor.execute(
    "SELECT t1.id, t2.record_key FROM table1 t1 "
    "LEFT JOIN table2 t2 ON (t1.id = t2.id) "
    "WHERE t1.col1=%s AND t2.col2=%s",
    (val1, val2),
)
for id, record_key in cursor.fetchall():
    pass  # do something...

How to address a python list in MySQL query with WHERE ?

You should use

cur.execute(
    "SELECT version,name FROM data2.files WHERE name IN (" +
    ",".join(("%s",) * len(filenames)) + 
    ")",
    tuple(filenames)
)

This creates a SQL string of the form

WHERE name IN (%s,%s,%s,%s)

with as many %ss you need and makes the MySQL module replace them with the filenames data, taking care of quoting as needed.

How to prevent sql injection for a IN clause using python mysql connector?

You should not use string formatting to create your request, leave argument parsing to the lib with the second parameter of cursor.execute.

Aka:

query = """
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number = %s
    LIMIT 1;
"""
result_in_list_of_dict = cursor.execute(query, (quotation_number,))

Now, to use this with a list is pretty simple

quotation_numbers = [1, 2, 3]
query = f"""
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number IN ({', '.join(['%s'] * len(quotation_numbers))})
;
"""
result_in_list_of_dict = cursor.execute(query, quotation_numbers)

You only have to create a placeholder for every item in your list and pass it to cursor.execute

---

To show the problem string formatting.

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s" % fake_args

print(query) # => 'SELECT "hello"; SELECT 123; -- '

By passing args to cursor.execute

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s"

cursor.execute(query, (fake_args, ))

# In MySQL log
# SELECT '\"hello\"; SELECT 123; -- '

With string formatting, you have so security against SQL injection (that's not the point of it). mysql-connector-python can do it and should do it.

Executing SELECT ... WHERE ... IN ... using MySQLdb

Here is a similar solution which I think is more efficient in building up the list of %s strings in the SQL:

Use the list_of_ids directly:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

That way you avoid having to quote yourself, and avoid all kinds of sql injection.

Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

give parameter(list or array) to in operator - python, sql

The idea is to have a query like this one:

cursor.execute("SELECT ... IN (%s, %s, %s)", (1, 2, 3))

where each %s will be substituted by elements in your list. To construct this string query you can do:

placeholders= ', '.join(['%s']*len(article_ids))  # "%s, %s, %s, ... %s"
query = 'SELECT name FROM table WHERE article_id IN ({})'.format(placeholders)

finally

cursor.execute(query, tuple(article_ids))

---

Related Topics