I am trying to store/insert data to my db in mysql using tkinter, I can insert but when I try again, it would say already inserted.
Also, the update and delete won't work. Kindly need some help/guide PLEASE. Thank you.
:am trying to create a simple info. system.
What I have tried:
import mysql.connector
from tkinter import *
from tkinter import ttk
root = Tk()
root.title('Simple Retail POS System')
root.rowconfigure(0, weight=1)
root.columnconfigure(0, weight=1)
my_tree = ttk.Treeview(root)
root.state('zoomed')
storeName = "Retail POS System"
conn = mysql.connector.connect(host='localhost', user='root',
password='ll', database='dib')
cursor = conn.cursor()
def reverse(tuples):
new_tup = tuples[::-1]
return new_tup
def insert(id, item, price, stock):
conn = mysql.connector.connect(host='localhost', user='root',
password='ll', database='dib')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS
items(itemID INT, item VARCHAR, price REAL, stock INT)""")
cursor.execute(
"INSERT INTO items VALUES ('" + str(id) + "','" + str(item) + "','" + str(price) + "','" + str(stock) + "')")
conn.commit()
def delete(data):
conn = mysql.connector.connect(host='localhost', user='root',
password='ll', database='dib')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS
items(itemID INT, item VARCHAR, price REAL, stock INT)""")
cursor.execute("DELETE FROM items WHERE itemID = '" + str(data) + "'")
conn.commit()
def update(id, name, price, stock, idNum):
conn = mysql.connector.connect(host='localhost', user='root',
password='ll', database='dib')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS
product(prod_id INT, pname VARCHAR, price REAL, stcok INT)""")
cursor.execute("UPDATE product SET pro_id = '" + str(id) + "', pname = '" + str(name) + "', price = '" + str(
price) + "', stock = '" + str(stock) + "' WHERE prod_id='" + str(idNum) + "'")
conn.commit()
def read():
conn = mysql.connector.connect(host='localhost', user='root',
password='ll', database='dib')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS
items(itemID INT, item VARCHAR, price REAL, stock INT)""")
cursor.execute("SELECT * FROM items")
results = cursor.fetchall()
conn.commit()
return results
def insert_data():
itemID = str(entryId.get())
item = str(entryItem.get())
price = str(entryPrice.get())
stock = str(entryStock.get())
if itemID == "" or item == " ":
print("Error Inserting Id")
if item == "" or item == " ":
print("Error Inserting Name")
if price == "" or price == " ":
print("Error Inserting Price")
if stock == "" or stock == " ":
print("Error Inserting Stock")
else:
insert(str(itemID), str(item), str(price), str(stock))
for data in my_tree.get_children():
my_tree.delete(data)
for result in (read()):
my_tree.insert(parent='', index='end', iid=result, text="", values=result, tag="orow")
my_tree.tag_configure('orow', background='#EEEEEE')
my_tree.grid(row=1, column=5, columnspan=4, rowspan=5, padx=10, pady=10)
def delete_data():
selected_item = my_tree.selection()[0]
deleteData = str(my_tree.item(selected_item)['values'][0])
delete(deleteData)
for data in my_tree.get_children():
my_tree.delete(data)
for result in reverse(read()):
my_tree.insert(parent='', index='end', iid=result, text="", values=result, tag="orow")
my_tree.tag_configure('orow', background='#EEEEEE')
my_tree.grid(row=1, column=5, columnspan=4, rowspan=5, padx=10, pady=10)
def update_data():
selected_item = my_tree.selection()[0]
update_name = my_tree.item(selected_item)['values'][0]
update(entryId.get(), entryItem.get(), entryPrice.get(), entryStock.get(), update_name)
for data in my_tree.get_children():
my_tree.delete(data)
for result in reverse(read()):
my_tree.insert(parent='', index='end', iid=result, text="", values=result, tag="orow")
my_tree.tag_configure('orow', background='#EEEEEE')
my_tree.grid(row=1, column=5, columnspan=4, rowspan=5, padx=10, pady=10)
titleLabel = Label(root, text=storeName, font=('Arial bold', 12), bd=2)
titleLabel.grid(row=0, column=0, columnspan=8, padx=20, pady=20)
idLabel = Label(root, text="ID", font=('Arial bold', 15))
itemLabel = Label(root, text="Name", font=('Arial bold', 15))
priceLabel = Label(root, text="Price", font=('Arial bold', 15))
stockLabel = Label(root, text="Stock", font=('Arial bold', 15))
idLabel.grid(row=1, column=0, padx=10, pady=10)
itemLabel.grid(row=2, column=0, padx=10, pady=10)
priceLabel.grid(row=3, column=0, padx=10, pady=10)
stockLabel.grid(row=4, column=0, padx=10, pady=10)
entryId = Entry(root, width=25, bd=5, font=('Arial bold', 15))
entryItem = Entry(root, width=25, bd=5, font=('Arial bold', 15))
entryPrice = Entry(root, width=25, bd=5, font=('Arial bold', 15))
entryStock = Entry(root, width=25, bd=5, font=('Arial bold', 15))
entryId.grid(row=1, column=1, columnspan=3, padx=5, pady=5)
entryItem.grid(row=2, column=1, columnspan=3, padx=5, pady=5)
entryPrice.grid(row=3, column=1, columnspan=3, padx=5, pady=5)
entryStock.grid(row=4, column=1, columnspan=3, padx=5, pady=5)
buttonEnter = Button(
root, text="Enter", padx=5, pady=5, width=5,
bd=3, font=('Arial', 15), bg="#EEEEEE", command=insert_data)
buttonEnter.grid(row=5, column=1, columnspan=1)
buttonUpdate = Button(
root, text="Update", padx=5, pady=5, width=5,
bd=3, font=('Arial', 15), bg="#EEEEEE", command=update_data)
buttonUpdate.grid(row=5, column=2, columnspan=1)
buttonDelete = Button(
root, text="Delete", padx=5, pady=5, width=5,
bd=3, font=('Arial', 15), bg="#EEEEEE", command=delete_data)
buttonDelete.grid(row=5, column=3, columnspan=1)
style = ttk.Style()
style.configure("Treeview.Heading", font=('Arial bold', 15))
my_tree['columns'] = ("ID", "Name", "Price", "Stock")
my_tree.column("#0", width=0, stretch=NO)
my_tree.column("ID", anchor=W, width=100)
my_tree.column("Item", anchor=W, width=200)
my_tree.column("Price", anchor=W, width=150)
my_tree.column("Stock", anchor=W, width=150)
my_tree.heading("ID", text="ID", anchor=W)
my_tree.heading("Item", text="Name", anchor=W)
my_tree.heading("Price", text="Price", anchor=W)
my_tree.heading("Stock", text="Stock", anchor=W)
for data in my_tree.get_children():
my_tree.delete(data)
for result in reverse(read()):
my_tree.insert(parent='', index='end', iid='', text="", values=result, tag="orow")
my_tree.tag_configure('orow', background='#EEEEEE', font=('Arial bold', 15))
my_tree.grid(row=1, column=5, columnspan=4, rowspan=5, padx=10, pady=10)
root.mainloop()