MySQL.Connector For Python Won't Update Values

Question

What I am trying to do:

I want to run this function

updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt + 1, score = %s WHERE id = %s" %(score, f)

Like this

  if (i != app.timerValue):
                score = i
                print(score, i)
                print("Dette er dit resultat: {}".format('%.2f' %score))
                print(myresult)
                mycursor.execute(updaterScoren)

So that the score from the game is saved at the row selected by the object "myresult". At the same time, the pincode usage (how many times it has been used for the game) should be saved.

I get the following message:

1.480000000000001 1.480000000000001
Dette er dit resultat: 1.48
[(3, '(ADRESS)', '(NAME)', None, 0)]

Obs. (ADRESS) and (NAME) are stand-ins, as I didn't want to reveal the actual address or name.

My whole code:

   import threading
    import tkinter as tk
    import pip
    import time
    import mysql.connector
    from time import sleep
    
    
    
    
    # NY THREAD(s), der kan vise scoreboarden på en skærm.
    class Timer(threading.Thread):
        def _init_(self):
            threading.Thread._init_(self)
            self.label = 0
            global score
    
    
        def run(self) -> None:
            self.master = tk.Tk()
    
            self.master.gemoetry("+0+0")
    
            self.master = tk.Label(self.root, bg = "black", fg = "red")
    
            self.timer.grid(row = 1, column = 2)
    
            self.master.mainloop()
    
    
    class Tkinter(threading.Thread):
        def __init__(self):
            threading.Thread.__init__(self)
            self.daemon = True
            self.root = 0
            self.entry = 0
            self.label = 0
            self.button = 0
            self.timerValue = 0
            self.stopButton = 0
    
            self.start()
    
        def run(self) -> 0:
            self.root = tk.Tk()
    
            # tkinter code goes here
            self.root.geometry("+2+0")
            self.root.config(background="black")
    
            self.label = tk.Label(self.root, bg="white", fg="black", font=("Fixedsys", 28))
            self.entry = tk.Entry(self.root, bg="white", fg="black", font=("Fixedsys", 28))
            self.stopTiden = tk.Button(self.root, bg="red", fg="white", text="Stop Spillet", command=self.stopTid)
            self.startTiden = tk.Button(self.root, bg="green", fg="white", text="Start Spillet", command=self.startTid)
    
            self.timerDisplay = tk.Label(self.root, bg="black", fg="red", text="Ceci n'est pas une timer")
    
            self.timerDisplay.grid(row=2, column=50)
            self.startTiden.grid(row=0, column=1)
            self.stopTiden.grid(row=0, column=10)
            self.entry.grid(row=1, column=1)
            self.label.grid(row=1, column=10)
    
            self.root.bind("<KeyPress>", self.read)
            self.root.mainloop()
    
        def displayTime(self):
            self.timerDisplay.config(text=i)
    
        def read(self, event):
            key = event.keysym
            try:
                if key == "Return":
                    self.getA()
                    self.visTid()
            except:
                self.fejlTidvalg()
    
        def startTid(self):
            self.stopButton = 0
    
        def getA(self):
            self.timerValue = float(self.entry.get().format("%.2f", 1.23456))
            print(self.timerValue)
            return self.timerValue
    
        def stopTid(self):
            self.stopButton = 1
    
        def visTid(self):
            self.label.config(text="Det her er timerens slutværdi: {}".format(self.timerValue))
    
        def fejlTidvalg(self):
            self.label.config(text="FEJL -Vælg Venligst Et Tal")
    
    
    app = Tkinter()
    
    stopTiden = 0
    
    # Login til databasen
    mydb = mysql.connector.connect(
        host="",
        user="",
        password="",
        database='mydatabase',
        auth_plugin='mysql_native_password'
    )
    
    doorButton = 0
    
    while (doorButton != 1):
        stopButton = app.stopButton
        timerValue = app.timerValue
        iTwo = timerValue
        doorButton = 0
        i = 0.00
        mycursor = mydb.cursor()
    
    
        def countdown(i):
    
            while i:
                mins, secs = divmod(t, 60)
                timer = '{:02d}:{:02d}'.format(mins, secs)
                print(timer, end="\r")
                time.sleep(1)
                t -= 1
    
    
        # Servodøren skal åbnes her i denne kode
        while (app.stopButton != 1):
            score = 0
    
            # Indtast Pinkoden
            f = int(input(
                "Kodeord - bemærk her, at kodeordene er pinkoderne fra min egen database, bare indtast en værdi mellem 1-6"))
            mycursor.execute("SELECT id,adresse,name,pinkode_brugt,score FROM bois WHERE id=%s", (f,))
    
            # MySQL Commands
            updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt +1, score = %s WHERE id = %s" %(score, f)
    
            myresult = mycursor.fetchall()
    
            row_count = mycursor.rowcount
            print(myresult)
            print("number of affected rows: {}".format(row_count))
            if row_count == 1:
    
                print(row_count)
                print(app.timerValue)
                while (i != app.timerValue) and (i <= app.timerValue) and (doorButton != 1) and (app.stopButton != 1):
                    print('%.2f' % i)
                    i = i + 0.01
                    timeLeft = app.timerValue - i
                    iTwo = app.timerValue
                    print("THIS IS X", app.timerValue)
                    time.sleep(0.01)
                # Hvis spillet stoppes, or i ikke når timerens slutværdi, vil scoren gemmes
                if (i != app.timerValue):
                    score = i
                    print(score, i)
                    print("Dette er dit resultat: {}".format('%.2f' %score))
                    print(myresult)
                    mycursor.execute(updaterScoren)
    
    
                else:
                    print("Ingen point")
                    mycursor.execute(updaterScoren)
    
    
            i = 0
            mydb.commit()
        # Her skal du tilføje i til den ID's row, der lige har åbnet via sin pinkode. Så er du stort set færdig! :D

Answer 1

This coammnd is a simple string concatenation

updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt +1, score = %s WHERE id = %s" %(score, f)

When you declare it above all , you have no score, so that it will be empty.

When you move it to

            if (i != app.timerValue):
                score = i
                print(score, i)
                print("Dette er dit resultat: {}".format('%.2f' %score))
                print(myresult)
                updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt +1, score = %s WHERE  id = %s" %(score, f)
                mycursor.execute(updaterScoren)

you will get the actual wanted score in it.

To do that want you trying. you need prepared statements

mycursor = mydb.cursor(prepared=True)
...
 updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt +1, score = %s WHERE id = %s"
...
mycursor.execute(updaterScoren , (score, f))

Of course you need correct data to have affected rows.

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html