1

I'm using MySQLdb in Python3 to execute some queries. One of which I have a list of user_ids and want to delete from my user table based off of them.

When I execute the query I get a warning from MySQLdb:

Warning: (1292, "Truncated incorrect DOUBLE value: '108, 114, 109, 115'")

The query attempted to be executed is:

DELETE FROM user
WHERE id IN (108, 114, 109, 115);

My code looks like the following:

cur.execute(get_users)
users = cur.fetchall()
users = [item[0] for item in users]
users = ", ".join(str(user) for user in users)

cur.execute(delete_query, [users])

I have also tried to do the following, it results in no warning however checking my database I can still see those users existing:

users = ", ".join("\'{}\'".format(user) for user in users)

The following works perfectly, however isn't secure: cur.execute(delete_query.format(users))

And yes, I am executing a db.commit() in my code.

Matthew Brzezinski
  • 1,685
  • 4
  • 29
  • 53

1 Answers1

1

The following approach has worked for me. The users list will be going directly to the MySQL driver so injection is not an issue:

format_strings = ','.join(['%s'] * len(users))
cursor.execute("DELETE FROM user WHERE id IN (%s)" % format_strings,
                tuple(users))
cody
  • 11,045
  • 3
  • 21
  • 36