How can I delete a record using MySQL in Python?

Question

I have a database table called Students and I want to delete a record using SQL. Here is my code:

uid = int(input("Please enter students ID: "))
c.execute("DELETE FROM Students WHERE ID = (uid) ")

I want to input the ID variable (uid) into the c.execute

Thanks in advance.

I guess you are looking for: `c.execute("DELETE FROM Students WHERE ID = {} ".format(uid))` — JustRufus, Mar 04 '17 at 10:57
@JustRufus Keep in mind that this example is exposed to SQL injection. — DeepSpace, Mar 04 '17 at 11:19
It is not, as it's int... you can't inject any code with int. — Flash Thunder, Mar 04 '17 at 11:20

score 3 · Answer 1 · answered Mar 04 '17 at 11:37

You must not use string interpolation as recommended in the other answer; while in this specific case it might be OK, generally it is unsafe as it opens you up to SQL injection. Instead, use the support for parameters in the execute method:

uid = int(input("Please enter students ID: "))
c.execute("DELETE FROM Students WHERE ID = %s", (uid,))

score 1 · Answer 2 · edited May 23 '17 at 11:54

1

What Daniel Roseman said should be the correct answer.

You can insert the ID as a parameter for the .execute method. There is an answer about this here

edited May 23 '17 at 11:54

Community

1
1

answered Mar 04 '17 at 12:09

EyfI

975
2
17
24

score 0 · Answer 3 · answered Mar 04 '17 at 10:58

0

Basically the syntax is:

"some string: %s, some int: %i, some double: %d" % (string_var,int_var,double_var)

so:

uid = int(input("Please enter students ID: "))
c.execute("DELETE FROM Students WHERE ID = %i" % (uid))

answered Mar 04 '17 at 10:58

Flash Thunder

11,672
8
47
91

1

This code is exposed to SQL injection. OP should use a parameterized query. – DeepSpace Mar 04 '17 at 11:18
Not really... it is int... not vulnerable. – Flash Thunder Mar 04 '17 at 11:19
And tomorrow OP will try to use a string in the same fashion. – DeepSpace Mar 04 '17 at 11:20
Maybe, but this code is fine. Giving `-1` because I don't agree with you? Oh ok. – Flash Thunder Mar 04 '17 at 11:21
But why recommend potentially unsafe code when there is a built-in way of making it safe? – Daniel Roseman Mar 04 '17 at 11:23
Because the question in my opinion doesn't have anything to do with databases, but variable in variable. Of course it is not safe to use this with strings... – Flash Thunder Mar 04 '17 at 11:24
1

What? I can't understand how you can say that. This question is *entirely* about how to interpolate a variable into a database call. It is unquestionably *unsafe* to use string formatting to do that, which is why the database API provides a safe supported way to do it. – Daniel Roseman Mar 04 '17 at 11:36
@FlashThunder Not because I don't agree with you, but because this answer recommends to use unsafe code. There's a big difference. – DeepSpace Mar 04 '17 at 11:41

How can I delete a record using MySQL in Python?

3 Answers3