Python Forum
Problem updating value in MySQL database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem updating value in MySQL database
#1
I made a simple app to read the product names and prices from a small database. You can change the prices and then commit them back to database. It runs without any errors but the values don't seem to update. When I print the value being written to the database it looks like it might be a list and there are multiple values. Can you take a look at my code and let me know where I am going wrong?

from tkinter import *
import mysql.connector

class Application(Frame):

   def __init__(self, master):
       super(Application, self).__init__(master)
       self.grid()

       self.mydb = mysql.connector.connect(
           host="localhost",
           user="root",
           password="password",
           database="wwproducts")

       self.mycursor = self.mydb.cursor()
       self.prices = []
       self.products = []
       self.mycursor.execute("SELECT product, price FROM products")
       self.row = self.mycursor.fetchone()
       while self.row is not None:
           self.products.append(self.row[0])
           self.prices.append(self.row[1])
           self.row = self.mycursor.fetchone()
       self.create_widgets()

   def create_widgets(self):
       self.lblproduct = list(self.products)
       self.entprice = list(self.prices)
       line = 1
       self.label0 = Label(self, text="Product Prices")
       self.label0.grid(row=0, column=0, columnspan=2, sticky=W)

       self.heading1 = Label(self, text="Product")
       self.heading1.grid(row=line, column=0, sticky=W)
       self.heading2 = Label(self, text="Price")
       self.heading2.grid(row=line, column=1, sticky=W)

       for i in range(len(self.products)):
           line = line + 1
           self.lblproduct[i] = Label(self, text=self.products[i])
           self.lblproduct[i].grid(row=line, column=0, sticky=W)
           self.entprice[i] = Entry(self, width=5)
           self.entprice[i].grid(row=line, column=1, sticky=W)
           self.entprice[i].insert(END, self.prices[i])

       self.label4 = Label(self, text="")
       self.label4.grid(row=line, column=0)
       self.button1 = Button(self, text="Update prices", command=self.update)
       line = line + 1
       self.button1.grid(row=line, column=0, sticky=W)
       self.entry4 = Entry(self, width=10)
       self.entry4.grid(row=line, column=1)

   def update(self):
       for i in range(len(self.products)):
           # print(self.prices[i])
           self.prices[i] = self.entprice[i].get()

       for i in range(len(self.products)):
           sql = "UPDATE products SET price = %s WHERE product = %s"
           val = (self.products[i], self.prices[i])
           # print(self.prices[i])
           self.mycursor.execute(sql, val)
           self.mydb.commit()
       self.entry4.delete(0,END)
       self.entry4.insert(END, "Updated")

window = Tk()
window.title("Product Prices")
window.geometry("400x300")
app = Application(window)
app.mainloop()
Reply
#2
Never mind. I found my error.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  mySQL Database error not resolving. cybertooth 2 310 Aug-30-2021, 05:45 PM
Last Post: ibreeden
  Problem Using SQL Placeholder In MySQL Query AdeS 11 928 Jul-31-2021, 12:19 AM
Last Post: Pedroski55
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 466 Jul-02-2021, 02:19 PM
Last Post: xtc14
  chatterbot utf-8 errors with mysql database isolatedastronaut 0 574 Nov-08-2020, 06:54 AM
Last Post: isolatedastronaut
  sqlite3 database problem Maryan 2 1,007 Oct-05-2020, 05:21 AM
Last Post: buran
  Telegram bot - Problem with database NoNameoN 1 626 Jul-13-2020, 06:39 AM
Last Post: Gribouillis
  MYSQL how to assign a table for each user in my username table database? YoshikageKira 1 1,403 Dec-26-2019, 05:57 AM
Last Post: buran
  How to return value from a stored procedure of a MySQL database? UtiliseIT 0 1,156 Jul-22-2019, 10:14 AM
Last Post: UtiliseIT
  Problem with updating file to attach/pynput jameseroni 7 2,374 Nov-02-2018, 03:47 AM
Last Post: jameseroni
  Trying to sort a MYSQL Database PierreSoulier 14 3,973 Jul-14-2018, 03:19 AM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020