Aug-12-2021, 11:18 AM
Hi
i'm trying to create a registration form with python tkinter sqlite3, but i have a problem, when i do the update of just one record record, all the names are updated (not just the one that was selected), and i have this issue when i try do add new data: "c.execute("INSERT INTO SCHOOL VALUES (:ID, :NAME, :BIRTH, :DOCS, :FATHER, :MOTHER, :CLASS)",
sqlite3.IntegrityError: UNIQUE constraint failed: SCHOOL.CLASS"
i would like some help to solve this problem, this is the code:
i'm trying to create a registration form with python tkinter sqlite3, but i have a problem, when i do the update of just one record record, all the names are updated (not just the one that was selected), and i have this issue when i try do add new data: "c.execute("INSERT INTO SCHOOL VALUES (:ID, :NAME, :BIRTH, :DOCS, :FATHER, :MOTHER, :CLASS)",
sqlite3.IntegrityError: UNIQUE constraint failed: SCHOOL.CLASS"
i would like some help to solve this problem, this is the code:
from tkinter import * from tkinter import ttk import _sqlite3 root = Tk() root.title('trying again') root.geometry("1000x500") data = [["1", ["BOB"], ["27/10/2020"], ["234512"], ["JOHN"], ["DOE"], ["6ยบ"]], ] # conectar a database conn = _sqlite3.connect('tree_crm.db') c = conn.cursor() c.execute("""CREATE TABLE IF NOT EXISTS SCHOOL ( ID text, NAME txt, BIRTH txt, DOCS txt, FATHER txt, MOTHER txt, CLASS txt oid PRIMARY KEY) """) # aDD RECORD TO TABLE for record in data: c.execute("INSERT INTO SCHOOL VALUES (:ID, :NAME, :BIRTH, :DOCS, :FATHER, :MOTHER, :CLASS)", { 'ID': str(record[0]), 'NAME': str(record[1]), 'BIRTH': str(record[2]), 'DOCS': str(record[3]), 'FATHER': str(record[4]), 'MOTHER': str(record[5]), 'CLASS': str(record[6]) } ) conn.commit() conn.close() def query_database(): conn = _sqlite3.connect('tree_crm.db') c = conn.cursor() c.execute("SELECT rowid, * FROM SCHOOL") records = c.fetchall() global count count = 0 for record in records: if count % 2 == 0: my_tree.insert(parent='', index='end', iid=count, text='', values=( record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[0]), tags=('evenrow',)) else: my_tree.insert(parent='', index='end', iid=count, text='', values=( record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[0]), tags=('oddrow',)) count += 1 print(records) conn.commit() conn.close() style = ttk.Style() style.theme_use('default') style.configure("Treeview", background="#D3D3D3", foreground="black", rowheigth=25, filedbackground="#D3D3D3") style.map('Treeview', background=[('selected', "#347083")]) tree_frame = Frame(root) tree_frame.pack(pady=10) tree_scroll = Scrollbar(tree_frame) tree_scroll.pack(side=RIGHT, fill=Y) my_tree = ttk.Treeview(tree_frame, yscrollcommand=tree_scroll) my_tree.pack() tree_scroll.config(command=my_tree.yview) my_tree['columns'] = ("ID", "NAME", "BIRTH", "DOCS", "FATHER", "MOTHER", "CLASS", "OID") my_tree.column("#0", width=0, stretch=NO) my_tree.column("ID", anchor=W, width=140) my_tree.column("NAME", anchor=W, width=140) my_tree.column("BIRTH", anchor=CENTER, width=100) my_tree.column("DOCS", anchor=W, width=140) my_tree.column("FATHER", anchor=W, width=140) my_tree.column("MOTHER", anchor=W, width=140) my_tree.column("CLASS", anchor=W, width=140) my_tree.column("OID", anchor=W, width=50) my_tree.heading("#0", text="", anchor=W) my_tree.heading("ID", text="ID", anchor=W) my_tree.heading("NAME", text="NAME", anchor=W) my_tree.heading("BIRTH", text="BIRTH", anchor=CENTER) my_tree.heading("DOCS", text="DOCS", anchor=W) my_tree.heading("FATHER", text="FATHER", anchor=W) my_tree.heading("MOTHER", text="MOTHER", anchor=W) my_tree.heading("CLASS", text="CLASS", anchor=W) my_tree.heading("OID", text="OID", anchor=W) my_tree.tag_configure('oddrow', background="white") my_tree.tag_configure('evenrow', background="lightblue") data_frame = LabelFrame(root, text="INFORMATION") data_frame.pack(fill="x", expand="yes", pady=20) fn_ID = Label(data_frame, text="ID") fn_ID.grid(row=0, column=0, padx=10, pady=10) fn_ID = Entry(data_frame) fn_ID.grid(row=0, column=1, padx=10, pady=10) fn_NAME = Label(data_frame, text="NAME") fn_NAME.grid(row=0, column=2, padx=10, pady=10) fn_NAME = Entry(data_frame) fn_NAME.grid(row=0, column=3, padx=10, pady=10) fn_BIRTH = Label(data_frame, text="DOCS") fn_BIRTH.grid(row=0, column=4, padx=10, pady=10) fn_BIRTH = Entry(data_frame) fn_BIRTH.grid(row=0, column=5, padx=10, pady=10) fn_DOCS = Label(data_frame, text="FATHER") fn_DOCS.grid(row=1, column=0, padx=10, pady=10) fn_DOCS = Entry(data_frame) fn_DOCS.grid(row=1, column=1, padx=10, pady=10) fn_FATHER = Label(data_frame, text="MOTHER") fn_FATHER.grid(row=1, column=2, padx=10, pady=10) fn_FATHER = Entry(data_frame) fn_FATHER.grid(row=1, column=3, padx=10, pady=10) fn_MOTHER = Label(data_frame, text="CLASS") fn_MOTHER.grid(row=1, column=4, padx=10, pady=10) fn_MOTHER = Entry(data_frame) fn_MOTHER.grid(row=1, column=5, padx=10, pady=10) fn_OID = Label(data_frame, text="OID") fn_OID.grid(row=0, column=6, padx=10, pady=10) fn_OID = Entry(data_frame) fn_OID.grid(row=0, column=7, padx=10, pady=10) fn_CLASS = Label(data_frame, text="BIRTH") fn_CLASS.grid(row=1, column=6, padx=10, pady=10) fn_CLASS = Entry(data_frame) fn_CLASS.grid(row=1, column=7, padx=10, pady=10) # add records: def add_records(): my_tree.insert(parent='', index='end', text='', values=( fn_ID.get(), fn_NAME.get(), fn_CLASS.get(), fn_BIRTH.get(), fn_DOCS.get(), fn_FATHER.get(), fn_MOTHER.get(), fn_OID.get()), ) # update records: def update_records(): select = my_tree.focus() my_tree.item(select, text="", values=( fn_ID.get(), fn_NAME.get(), fn_CLASS.get(), fn_BIRTH.get(), fn_DOCS.get(), fn_FATHER.get(), fn_MOTHER.get(), ), ) conn = _sqlite3.connect('tree_crm.db') c = conn.cursor() c.execute("""UPDATE SCHOOL SET ID=:ID, NAME=:NAME, BIRTH=:BIRTH, DOCS=:DOCS, MOTHER=:MOTHER, CLASS=:CLASS WHERE oid=oid""", { 'ID': fn_ID.get(), 'NAME': fn_NAME.get(), 'BIRTH': fn_BIRTH.get(), 'DOCS': fn_DOCS.get(), 'FATHER': fn_FATHER.get(), 'MOTHER': fn_MOTHER.get(), 'CLASS': fn_CLASS.get(), } ) conn.commit() conn.close() # MOVE UP def up(): rows = my_tree.selection() for row in rows: my_tree.move(row, my_tree.parent(row), my_tree.index(row) - 1) # MOVE DOWN def down(): rows = my_tree.selection() for row in reversed(rows): my_tree.move(row, my_tree.parent(row), my_tree.index(row) + 1) # DELETE RECORDS SPECIFIC def remove_one(): x = my_tree.selection() for record in x: my_tree.delete(record) def remove_all(): for record in my_tree.children(): my_tree.delete(record) # delete all def clear_record(): fn_ID.delete(0, END) fn_NAME.delete(0, END) fn_CLASS.delete(0, END) fn_BIRTH.delete(0, END) fn_DOCS.delete(0, END) fn_FATHER.delete(0, END) fn_MOTHER.delete(0, END) fn_CLASS.delete(0, END) # SELECT RECORD def select_record(e): fn_ID.delete(0, END) fn_NAME.delete(0, END) fn_CLASS.delete(0, END) fn_BIRTH.delete(0, END) fn_DOCS.delete(0, END) fn_FATHER.delete(0, END) fn_MOTHER.delete(0, END) fn_CLASS.delete(0, END) fn_OID.delete(0, END) selected = my_tree.focus() values = my_tree.item(selected, 'values') # insert values fn_ID.insert(0, values[0]) fn_NAME.insert(0, values[1]) fn_CLASS.insert(0, values[2]) fn_BIRTH.insert(0, values[3]) fn_DOCS.insert(0, values[4]) fn_FATHER.insert(0, values[5]) fn_MOTHER.insert(0, values[6]) fn_OID.insert(0, values[7]) # add butons: button_frame = LabelFrame(root, text="COMANDOS") button_frame.pack(fill="x", expand="yes", padx=20) update_button = Button(button_frame, text="changing data", command=update_records) update_button.grid(row=0, column=0, padx=10, pady=10) add_button = Button(button_frame, text="add data", command=add_records) add_button.grid(row=0, column=1, padx=10, pady=10) remove_button = Button(button_frame, text="remove data", command=remove_one) remove_button.grid(row=0, column=2, padx=10, pady=10) select_button = Button(button_frame, text="clear data", command=clear_record) select_button.grid(row=0, column=3, padx=10, pady=10) move_button = Button(button_frame, text="move row down", command=down) move_button.grid(row=0, column=4, padx=10, pady=10) move_button = Button(button_frame, text="move row up", command=up) move_button.grid(row=0, column=5, padx=10, pady=10) # delete_button = Button(button_frame, text="remover tudo",command=remove_all) # delete_button.grid(row=0, column=7, padx=10, pady=10) my_tree.bind("<ButtonRelease-1>", select_record) query_database() root.mainloop()