Deleting a record from database inside of python gave an error

Question

I tried deleting a record inside of python for my database DB and it gave me error,

mysql.connector.errors.ProgrammingError: 1370 (42000): execute command denied to user 'user'@'%' for routine 'e7.get'

e7.get is the input from an entry box in tkinter and i tried the same command in mysql workbench,

DELETE FROM PATIENTS WHERE Patient_name=random_name

and it worked inside of mysql workbench, only problem is when i use the variable, and some privillage error? im using a online free hosting website db4free.net . I have tried grant privilage and it gives another error that #1044 - Access denied for user 'user'@'%' to database 'DB' Server does support remote access. Thanks in advance :) EDIT : I TRIED PUTTING e7.get() in quotes and it dint give the error but it did nothing and the record still exists.

CODE:

def delete():
    con = mysql.connect(host='localhost', user='user', password='*****', database='DB')
    c = con.cursor()
    c.execute("DELETE FROM PATIENTS WHERE gender = 'e7.get()' ")
    c.execute('commit')
    con.close()
    e7.delete(0, END)

gives no error but does not delete the record.

How are you connecting to the database in python? Are you passing authentication credentials to the database? Can you post your python code? I found another post that answers a similar question, https://stackoverflow.com/a/7370436/10151980 — Lateralus, May 08 '20 at 23:04
Does this answer your question? [Mysql: execute command denied to user ''@'localhost' for routine error](https://stackoverflow.com/questions/6434573/mysql-execute-command-denied-to-user-localhost-for-routine-error) — Lateralus, May 08 '20 at 23:07
actually that error was solved by using 'e7.get()' in quotes which i did not use before.... now the problem is that it does no good and record still exists — Delrius Euphoria, May 08 '20 at 23:15
Can you try: c.execute(f"DELETE FROM PATIENTS WHERE gender = {e7.get()} ") — Lateralus, May 08 '20 at 23:19
sorry it gives error ```mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Male' in 'where clause' ``` i think they took the value as a column :( — Delrius Euphoria, May 08 '20 at 23:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/213457/discussion-between-kickin-wing-and-cool-cloud). — Lateralus, May 08 '20 at 23:27

score 1 · Answer 1 · answered May 08 '20 at 23:24

It sounds like you are trying to insert the return value of e7.get() into your sql command.

in python you can use:

c.execute(f"DELETE FROM PATIENTS WHERE gender = '{e7.get()}' ")

or you can use:

c.execute("DELETE FROM PATIENTS WHERE gender = '{}' ".format(e7.get())

either of those will insert the return value of e7.get() into your string

score 1 · Accepted Answer · answered May 08 '20 at 23:29

1

Even python is susceptible to SQL injection so use parametrized queries and avoid the python/sql escaping complications:

 c.execute('DELETE FROM PATIENTS WHERE gender = %s', (e7.get()))

see tutorial

answered May 08 '20 at 23:29

danblack

12,130
2
22
41

1

connection has a commit method too: [con.commit()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-commit.html) – danblack May 08 '20 at 23:32

Deleting a record from database inside of python gave an error

2 Answers2