Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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()
Posted
Updated 15-Jun-22 21:39pm
v2
Comments
Richard MacCutchan 16-Jun-22 4:21am    
You are trying to create your database table more than once. Specifically it would be pointless creating it just before you try to delete a record, as it would not contain any. You should create the table only once, before you perform any of the other functions. You should also remove all the duplicates of mysql.connector.connect. Again, do it once before you perform any of the other functions.
lil_mint 16-Jun-22 6:19am    
Thank you very much

1 solution

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

And if you try to insert the same row twice, what did you expect was going to happen?
That's like trying to write the same cheque to two different people, for two different amounts, or trying to get two cars with the same legal registration number!
 
Share this answer
 
Comments
lil_mint 16-Jun-22 6:21am    
Thank you... I am new to this and this will guide me so much. Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900