Python mysql row deletion problems

Question

I'm having issues deleting rows from my database with python. I can hard code it like this:

del_student = "DELETE FROM students WHERE sid=34556"
cursor.execute(del_student)
cnx.commit()

but I'm trying to get sid from my entry box in my gui and this isn't working:

sid=SIDEnt.get()
del_student = "DELETE FROM students WHERE sid=%s"
cursor.execute(del_student,sid)
cnx.commit()

I'm rather new to python and I've tried looking through the posted questions already, thanks.

What is printed if you put print(repr(sid)) after the sid=SIDEnt.get() line ? — falsetru
– falsetru, Commented Nov 20, 2013 at 7:31
In addition to checking the value of sid, I believe that you need to use ? as a placeholder rather than %s. — Tim
– Tim, Commented Nov 20, 2013 at 7:36

mechanical_meat · Accepted Answer · 2013-11-20 07:50:07Z

2

sid needs to be in a tuple.

sid=SIDEnt.get()
del_student = "DELETE FROM students WHERE sid=%s"
cursor.execute(del_student,(sid,)) # put `sid` into a one-element tuple
cnx.commit()

The DB-API specifies, "Parameters may be provided as sequence or mapping..."

edited Nov 20, 2013 at 7:50

answered Nov 20, 2013 at 7:43

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

4 Comments

Burhan Khalid Over a year ago

Here is the reference to the documentation that explains this: "Why the tuple? Because the DB API requires you to pass in any parameters as a sequence."

mechanical_meat Over a year ago

Thanks for adding that, Burhan. Indeed a one-element list is OK here too.

ifonlyirocked Over a year ago

Amazing, would have never thought of adding an extra comma after sid, thanks so much.

hjpotter92 Over a year ago

@ifonlyirocked and also putting it in a box?

PyGuy · Accepted Answer · 2013-11-20 08:51:47Z

0

you haven't substituted the '%s' with any values; so your query is this:

'DELETE FROM students WHERE sid=%s'

it should be as follows:

sid=SIDEnt.get()
del_student = "DELETE FROM students WHERE sid=%s"%(sid,)
cursor.execute(del_student,sid)
cnx.commit()

answered Nov 20, 2013 at 8:51

PyGuy

5711 gold badge7 silver badges17 bronze badges

1 Comment

Michał Leon Over a year ago

You definitely do NOT want to use "%" in SQL, as it may allow an ''SQL injection''. Even if the variable 'sid' cannot be accessed by an attacker in this particular case, it is considered bad practice to use it.

Collectives™ on Stack Overflow

Python mysql row deletion problems

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related