Skip to content
Advertisement

Why is no error showing up when inserting a name that is not in the table sql/python

from tkinter import *
import tkinter as tk
from tkinter import filedialog
import mysql.connector
from tkinter import messagebox, simpledialog
from tkinter.messagebox import askokcancel, showinfo, WARNING

db = mysql.connector.connect(
    host="localhost",
    user="admin",
    passwd="test",
    database="dbpath"
)

mycursor = db.cursor()

mycursor.execute("CREATE DATABASE IF NOT EXISTS dbpath")
mycursor.execute(
    "CREATE TABLE IF NOT EXISTS filepaths (id INT AUTO_INCREMENT PRIMARY KEY, path VARCHAR(255), name VARCHAR(255))")


def confirmDeleteOne():
    ROOT = tk.Tk()
    ROOT.withdraw()

    USER_INP = simpledialog.askstring(title="App Name",
                                      prompt="Insert the name of the app you want delete:")
    answer = askokcancel(
        title='DELETION',
        message='Are you sure?nTHIS IS AN IRREVERSIBLE ACTION',
        icon=WARNING)

    if answer:
        mycursor.execute("DELETE FROM filepaths WHERE name = '" + USER_INP + "';")
        db.commit()
        showinfo(
            title='DELETION',
            message='SUCCESSFULLY DELETED ' + USER_INP.upper())


root = Tk()


deleteOne = Button(root, text="Delete One", command=confirmDeleteOne, bg='yellow', font="bold")
deleteOne.pack()

root.mainloop()

The problem that I am refering to is that if you insert something random in the USER_INP variable that is not in the database, it doesn’t show an error like THIS DOES NOT EXIST IN THE TABLE or something like that. What I want to achieve is when the user inserts something random in the USER_INP variable, the last message that should be displayed is

message='THIS DOES NOT EXIST IN THE TABLE '

Advertisement

Answer

SQL doesn’t generate an error if a WHERE clause doesn’t match anything, it just doesn’t do anything. In a SELECT query it returns an empty result set; in a DELETE or UPDATE it doesn’t change anything.

You can use the rowcount attribute to find out how many rows were deleted. If this is 0 you can display an error message.

    if answer:
        mycursor.execute("DELETE FROM filepaths WHERE name = %s", (USER_INP,))
        if mycursor.rowcount == 0:
            msg = 'THIS DOES NOT EXIST IN THE TABLE'
        else:
            msg = 'SUCCESSFULLY DELETED ' + USER_INP.upper()
        db.commit()
        showinfo(
            title='DELETION',
            message=msg)
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement