I am struggling with "Update" function in Python MySql, PyMySql

Question

Here is how the code looks like. "vare" is the section i want to update

def update():
                sqlCon = pymysql.connect(host = "localhost", user = "root", password = "root", database = "varehusdb")
                cur =sqlCon.cursor()
                cur.execute("update vare set Betegnelse=%s, Pris=%s, KatNr=%s, Antall=%s, Hylle=%s where VNr=%s",(

                Betegnelse.get(),
                Pris.get(),
                KatNr.get(),
                Antall.get(),
                Hylle.get(),
                VNr.get(),

                ))
                sqlCon.commit()
                sqlCon.close()
                tkinter.messagebox.showinfo("Data Entry Form", "Record Updated Successfully")

edit: Thx!

I based this from this tutorial i saw on youtube https://youtu.be/dxOPaIX4qt4?feature=shared

Which is a very useful video. I got it all to work, exit, add data, info, reset.. and while i had to use a trick to make "Display" work, overall worked very well.

But everytime i try to run this normally, and press the "Update" button for the str i want to change. It says it went successfully... without anything happening. Same thing happens if i press "Delete" button. And if i try to search for a value... it just says "No such record found". Which is usually means it couldnt find that value you were typing in.

So what am i doing wrong with these three functions?

To make it work with the Display function i used this tutorial in the Visual Studio https://youtu.be/tnlGr1LeZDs?feature=shared Which worked. But for Update, Delete and Search function, i am totally clueless on.

edit cur.commit() did the trick. THANKS! I dont think it works for Delete or Search function. But its better than nothing and works well for update function. Much appreciated!

Answer 1

it's essential to ensure that the values you're trying to update with (Betegnelse, Pris, KatNr, Antall, Hylle, VNr) have the correct values at the time of execution. You may print these values before the update statement to debug:

print(Betegnelse.get(), Pris.get(), KatNr.get(), Antall.get(), Hylle.get(), VNr.get())

Answer 2

Based at the information provided within the query and the subsequent edit, it looks as if the problem with the "Update" function within the Python script lies in now not committing the adjustments to the database after executing the update query. Additionally, it seems that comparable troubles are present within the "Delete" and "Search" features. The repair includes adding sqlCon.Dedicate() after executing the SQL queries to ensure changes are meditated within the database.

Here's a revised version of the update() function:

import pymysql
import tkinter.messagebox

def update():
    sqlCon = pymysql.connect(host="localhost", user="root", password="root", database="varehusdb")
    cur = sqlCon.cursor()
    
    try:
        cur.execute("UPDATE vare SET Betegnelse=%s, Pris=%s, KatNr=%s, Antall=%s, Hylle=%s WHERE VNr=%s",
                    (Betegnelse.get(), Pris.get(), KatNr.get(), Antall.get(), Hylle.get(), VNr.get()))
        
        # Commit the changes to the database
        sqlCon.commit()
        
        tkinter.messagebox.showinfo("Data Entry Form", "Record Updated Successfully")
    except pymysql.Error as e:
        tkinter.messagebox.showerror("Error", f"Error updating record: {e}")
    finally:
        sqlCon.close()

# Other functions (e.g., delete, search) may need similar modifications

This up to date feature guarantees that adjustments made through the "Update" operation are committed to the database, thereby making sure they persist past the contemporary session.

Remember to apply comparable modifications to the "Delete" and "Search" functions if they may be encountering comparable issues. Always make sure right mistakes dealing with to cope with potential database errors.