I'm adding data into sqlite table and when I try to update the table, I'm getting this error 'string index not in range'.
Again when I execute the update command, all the columms gets updated except the identity column but my intention is only to update a selected row.
what I not doing right from the code below>
Your assistance will be highly appreciated
Below is the code:
from tkinter import *
from tkinter import ttk
import tkinter.messagebox
import sqlite3
root =Tk()
root.title('Accounting App')
root.config(bg='#3d6466')
root.geometry("520x600")
root.resizable(False, False)
style = ttk.Style()
style.theme_use('alt')
style.configure("TCombobox", fieldbackground="Grey", background="Grey")
class Backend():
def __init__(self):
self.conn = sqlite3.connect('accounting.db')
self.cur = self.conn.cursor()
#self.conn.execute("""DROP TABLE IF EXISTS account_type""")
self.conn.execute("""CREATE TABLE IF NOT EXISTS account_type(
id INTEGER PRIMARY KEY,
category_id INTEGER NOT NULL,
category_type TEXT NOT NULL
)"""),
self.conn.commit()
# elf.conn.close()
# =========Account Type======
class Account_type():
def insert_account_type(self, category_id, category_type):
self.conn = sqlite3.connect('accounting.db')
self.cur = self.conn.cursor()
self.cur.execute("""INSERT INTO account_type(category_id,category_type) VALUES(?,?);""",
(category_id, category_type,))
self.conn.commit()
self.conn.close()
def view_account_type(self):
self.conn = sqlite3.connect('accounting.db')
self.cur = self.conn.cursor()
self.cur.execute("SELECT * FROM account_type")
rows = self.cur.fetchall()
self.conn.close()
return rows
def delete_account_type(self, category_id):
conn = sqlite3.connect("accounting.db")
cur = conn.cursor()
cur.execute("DELETE FROM account_type WHERE category_id=?", [category_id])
conn.commit()
conn.close()
acc_type = Backend.Account_type()
tb = Backend()
class Front_end():
def __init__(self, master):
# Frames
global cur
global conn
conn = sqlite3.connect('accounting.db')
cur = conn.cursor()
# Frames
self.left_frame = LabelFrame(master,bg='#3d6466', relief=SUNKEN,width=200)
self.left_frame.pack(fill = 'both',expand = YES , padx = 5,side=LEFT,anchor=NW)
self.right_frame = LabelFrame(master, bg='#3d6466', relief=SUNKEN)
self.right_frame.pack(fill = 'both',expand = YES ,side=LEFT,anchor=NW)
self.top_right_frame = LabelFrame(self.right_frame, bg='#3d6466', relief=SUNKEN,text='Details',fg='maroon')
self.top_right_frame.pack(fill=BOTH,side=TOP, anchor=NW,expand=YES)
self.top_r_inner_frame = LabelFrame(self.right_frame, bg='#3d6466', relief=SUNKEN, text='...', fg='maroon',height=10)
self.top_r_inner_frame.pack(fill=BOTH, side=TOP, anchor=SW, expand=YES)
self.bottom_right_frame = LabelFrame(self.right_frame, bg='#3d6466', relief=SUNKEN, text='Field View', fg='maroon')
self.bottom_right_frame.pack(fill=BOTH,side=TOP, anchor=SW, expand=YES)
self.my_canvas = Canvas(self.top_right_frame,bg='#3d6466')
self.my_canvas.pack(side=LEFT,fill='both', expand=YES)
# vertical configuration of scrollbar
self.yscrollbar = ttk.Scrollbar(self.top_right_frame, orient=VERTICAL, command = self.my_canvas.yview)
self.yscrollbar.pack(side=RIGHT,fill='both')
self.my_canvas.config(yscrollcommand = self.yscrollbar.set)
self.top_right_frame = Frame(self.my_canvas, bg='#3d6466', relief=SUNKEN)
self.my_canvas.create_window((0,0),window=self.top_right_frame, anchor=NW)
self.my_canvas.bind('<Configure>',lambda e:self.my_canvas.configure(scrollregion = self.my_canvas.bbox('all')))
self.side_frame = LabelFrame(self.left_frame,bg='#3d6466',relief=SUNKEN,text='Menu Buttons',fg='maroon',)
self.side_frame.pack(side=TOP,anchor=NW,expand=YES )
# Side Buttons
self.btn1 = Button(self.side_frame, text='Main Account Types', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white', width=18,height=2,command=self.main_account)
self.btn1.grid(row=0, column=0, sticky=W)
self.btn2 = Button(self.side_frame, text='Chart of Accounts', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white', width=18,height=2 )
self.btn2.grid(row=1, column=0, pady=2, sticky=W)
self.btn3 = Button(self.side_frame, text='Accounting Vouchers', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white',
width=18 ,height=2)
self.btn3.grid(row=2, column=0, sticky=W)
self.btn4 = Button(self.side_frame, text='General Ledger', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white',
width=18 ,height=2)
self.btn4.grid(row=3, column=0, pady=2, sticky=W)
self.btn5 = Button(self.side_frame, text='Support Documents', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white',
width=18, height=2)
self.btn5.grid(row=4, column=0, sticky=W)
self.btn6 = Button(self.side_frame, text='Employees Center', bg='#3d6466', font=('cambria', 12), anchor=W,
fg='white',
width=18,height=2)
self.btn6.grid(row=5, column=0, pady=2, sticky=W)
def main_account(self):
global category_type
for widget in self.top_right_frame.winfo_children():
widget.destroy()
for widget in self.bottom_right_frame.winfo_children():
widget.destroy()
for widget in self.top_r_inner_frame.winfo_children():
widget.destroy()
def resize_window():
root.geometry('540x590')
resize_window()
# variables
self.category_id = StringVar()
self.category_type = StringVar()
self.category_search =StringVar()
# functions
def add_main_accounts(self):
if self.category_id.get() == "":
tkinter.messagebox.showinfo('All fields are required')
else:
Backend.Account_type.insert_account_type(self,
self.category_id.get(),self.category_type.get())
tkinter.messagebox.showinfo('Entry successful')
def display_account_types(self):
self.trv.delete(*self.trv.get_children())
for rows in Backend.Account_type.view_account_type(self):
self.trv.insert("", END, values=rows)
def get_account_type(e):
selected_row = self.trv.focus()
data = self.trv.item(selected_row)
row = data["values"]
"""Grab items and send them to entry fields"""
self.category_id.set(row[1])
self.category_type.set(row[2])
def clear(self):
self.category_id.set("")
self.category_type.set("")
#self.category_search.set("")
def delete_record(self):
type_ID = self.category_id.get()
conn = sqlite3.connect("operations.db")
cur = conn.cursor()
if self.category_id.get() == "":
tkinter.messagebox.showinfo('All fields are required!')
return
delete_record = tkinter.messagebox.askyesno("Confirm deletion!",
'Do you want to delete the partner record?')
if delete_record > 0 and type_ID != " ":
Backend.Account_type.delete_account_type(self,type_ID)
self.trv.selection_clear()
tkinter.messagebox.showinfo('Deletion successful!')
return
elif delete_record == 0 and type_ID == " ":
tkinter.messagebox.showinfo('All fields are required!')
return
else:
tkinter.messagebox.showinfo('Empty fields cannot be deleted')
return
def update_record(self):
selected = self.trv.focus()
self.trv.item(selected, values=(
self.category_id.get(), self.category_type.get()))
conn = sqlite3.connect("accounting.db")
cur = conn.cursor()
if self.category_id.get() == "" or self.category_type.get() == "" :
tkinter.messagebox.showinfo('All fields are required!')
return
update_record = tkinter.messagebox.askyesno('Confirm please',
'Do you want to update employee records?')
if update_record > 0:
cur.execute(
"UPDATE account_type SET category_id=:cat_id, category_type=:type",
{'cat_id': self.category_id.get(), 'type': self.category_type.get()})
tkinter.messagebox.showinfo('Record update successful!')
conn.commit()
# call the function for Clearing the fields
clear(self)
conn.close()
def update_rows(rows):
self.trv.delete(*self.trv.get_children())
for i in rows:
self.trv.insert("", END, values=i)
def search_data(self):
conn = sqlite3.connect("accounting.db")
cur = conn.cursor()
Q1 = self.category_search.get()
cur.execute("SELECT id, category_id, category_type FROM account_type WHERE category_id=?;", (Q1,))
rows = cur.fetchall()
update_rows(rows)
conn.commit()
conn.close()
"""=================TreeView==============="""
# Scrollbars
ttk.Style().configure("Treeview", background = "#3d6466", foreground = "white", fieldbackground = "grey")
scroll_x = Scrollbar(self.bottom_right_frame, orient = HORIZONTAL)
scroll_x.pack(side = BOTTOM, fill = X)
scroll_y = Scrollbar(self.bottom_right_frame, orient = VERTICAL)
scroll_y.pack(side = RIGHT, fill = Y)
# Treeview columns & setting scrollbars
self.trv = ttk.Treeview(self.bottom_right_frame, height=5, columns=
('id','category_id', 'category_type'), xscrollcommand = scroll_x.set, yscrollcommand = scroll_y.set)
# Treeview style configuration
ttk.Style().configure("Treeview", background = "#3d6466", foreground = "white", fieldbackground = "#3d6466")
# Configure vertical and Horizontal scrollbars
scroll_x.config(command = self.trv.xview)
scroll_y.config(command = self.trv.yview)
# Treeview Headings/columns
self.trv.heading('id', text = 'NO')
self.trv.heading('category_id', text = 'Category ID')
self.trv.heading('category_type', text = 'Category Type')
self.trv['show'] = 'headings'
# Treeview columns width
self.trv.column('id', width = 50)
self.trv.column('category_id', width = 70)
self.trv.column('category_type', width = 90)
self.trv.pack(fill = BOTH, expand = YES,anchor = NW)
# Binding Treeview with data
self.trv.bind('<<TreeviewSelect>>',get_account_type) # trv.bind('<Double-1>',"")
# Account Types Labels
self.lbl1 = Label(self.top_right_frame,text = 'Category ID',anchor = W,width=12,font = ('cambria',13,),bg = '#3d6466')
self.lbl1.grid(row = 0,column = 0,pady = 5)
self.lbl1 = Label(self.top_right_frame, text='Category Type', anchor=W, width=12, font=('cambria', 13,), bg='#3d6466')
self.lbl1.grid(row=1, column=0, pady=5)
self.lbl2 = Label(self.top_right_frame, text='Search Account', anchor=W, width=12, font=('cambria', 13,),
bg='#3d6466')
self.lbl2.grid(row=6, column=0, pady=5)
# Account Type Entries
self.entry1 = Entry(self.top_right_frame,textvariable = self.category_id,font = ('cambria',13,),bg = 'Grey',width=14)
self.entry1.grid(row = 0,column=1,sticky = W,padx = 4,columnspan=2)
self.entry1 = Entry(self.top_right_frame, textvariable=self.category_type, font=('cambria', 13,), bg='Grey', width=14)
self.entry1.grid(row=1, column=1, sticky=W, padx=4, columnspan=2)
self.entry2 = Entry(self.top_right_frame, textvariable=self.category_search, font=('cambria', 13,), bg='Grey', width=14)
self.entry2.grid(row=6, column=1, sticky=W, padx=4, columnspan=2)
self.blank_label1 = Label(self.top_right_frame,bg = '#3d6466')
self.blank_label1.grid(row = 2,columnspan=2,pady = 8,)
self.blank_label2 = Label(self.top_right_frame, bg='#3d6466')
self.blank_label2.grid(row=5, columnspan=2, pady=5, )
self.blank_label3 = Label(self.top_r_inner_frame, bg='#3d6466',height=2)
self.blank_label3.grid(row=0, columnspan=2, )
# Buttons
self.btn_1 = Button(self.top_right_frame,text='Add',font=('cambria',12,'bold'),bg='#3d6466',
activebackground='green', fg = 'white',width=12,height = 1,relief=RAISED,
command = lambda :[add_main_accounts(self),display_account_types(self),clear(self)])
self.btn_1.grid(row = 3,column = 0,pady=6, padx=6)
self.btn_2 = Button(self.top_right_frame, text = 'View',command=lambda :[display_account_types(self),clear(self)],
font=('cambria', 12, 'bold'), bg = '#3d6466', activebackground='green',
fg ='white', width=12, height = 1, relief=RAISED)
self.btn_2.grid(row = 3, column=1,padx=0)
self.btn_3 = Button(self.top_right_frame, text = 'Update', command= lambda :[update_record(self),
display_account_types(self)],font=('cambria', 12, 'bold'), bg = '#3d6466',
activebackground = 'green', fg='white', width = 12, height = 1, relief=RAISED)
self.btn_3.grid(row = 4, column = 0,pady=6,padx=10)
self.btn_4 = Button(self.top_right_frame, text = 'Delete', command=lambda :[delete_record(self),clear(self),
display_account_types(self)], font=('cambria', 12, 'bold'), bg = '#3d6466',
activebackground = 'green', fg = 'white', width = 12, height = 1,relief = RAISED)
self.btn_4.grid(row = 4, column=1, padx=0)
self.btn_5 = Button(self.top_right_frame, text='Search', command=lambda: [clear(self),search_data(self)],
font=('cambria', 12, 'bold'), bg='#3d6466',
activebackground='green', fg='white', width=12, height=1, relief=RAISED)
self.btn_5.grid(row=7, columnspan=2)
# calling the class
app = Front_end(root)
root.mainloop() ```
CodePudding user response：
Your update function updates all the records exist. To avoid this you should use WHERE. Here its fixed version
def update_record(self):
selected = self.trv.focus()
oldValues = self.trv.item(selected)["values"]
self.trv.item(selected, values=(oldValues[0],
self.category_id.get(), self.category_type.get()))
conn = sqlite3.connect("accounting.db")
cur = conn.cursor()
if self.category_id.get() == "" or self.category_type.get() == "" :
tkinter.messagebox.showinfo('All fields are required!')
return
update_record = tkinter.messagebox.askyesno('Confirm please',
'Do you want to update employee records?')
if update_record > 0:
cur.execute(
"UPDATE account_type SET category_id=:cat_id, category_type=:type WHERE id=:id_value",
{'cat_id': self.category_id.get(), 'type': self.category_type.get(), "id_value": oldValues[0]})
tkinter.messagebox.showinfo('Record update successful!')
conn.commit()
# call the function for Clearing the fields
clear(self)
conn.close()
We used selected rows idx etc. However your error string index out of range is not about this. The function above going to fix your record update problem. Your error is because it tries to show a record value but after refreshed table, nothing is selected. So it returns empty.
def get_account_type(e):
selected_row = self.trv.focus()
data = self.trv.item(selected_row)
row = data["values"]
if(row == ""):
return
"""Grab items and send them to entry fields"""
self.category_id.set(row[1])
self.category_type.set(row[2])
Now if row is just an empty string, function stops executing and you get no errors.