Python3 MySQLdb Query Does Not Execute

Question

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.

cody · Accepted Answer · 2018-12-07 19:20:57Z

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))

answered Dec 7, 2018 at 19:20

cody

11.2k3 gold badges27 silver badges39 bronze badges

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

1 Comment

Matthew Brzezinski Over a year ago

Just found this from the thread: stackoverflow.com/a/589416/1327636 It seems to work for me as well, thanks!

Collectives™ on Stack Overflow

Python3 MySQLdb Query Does Not Execute

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related