doubt about python tkinter and sqlite3

[LONDER]

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:

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()

buran write Aug-12-2021, 11:21 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.

[deanhystad]

Write a short script to test your query. That removes tkinter and about 250 lines of code from the equation. I think your queries are formatted wrong, but I just cannot get past all that tkinter code to really see what is going on.

[ibreeden]

Hi @LONDER,

Error:
UNIQUE constraint failed: SCHOOL.CLASS

This means the CLASS column must be unique. This is a design error because it would mean there could only be one pupil in each class. So look at the definition of the table.

CREATE TABLE IF NOT EXISTS SCHOOL (
       ID text,
       NAME txt,
       BIRTH txt,
       DOCS txt,
       FATHER txt,
       MOTHER txt,
       CLASS txt
       oid PRIMARY KEY)

At first sight I would say there is a comma missing after "CLASS txt". Because of this omission CLASS is a primary key, which implies a unique constraint.
But why is OID mentioned here? OID or ROWID is a pseudo column and it should not be defined. I don't understand why it gave no error. Perhaps it was meant to define the column "ID" to be a primary key. But the syntax to do so is:

CREATE TABLE IF NOT EXISTS SCHOOL (
       ID text,
       NAME text,
       BIRTH text,
       DOCS text,
       FATHER text,
       MOTHER text,
       CLASS text,
       PRIMARY KEY(ID))

Or even easyer:

CREATE TABLE IF NOT EXISTS SCHOOL 
       (
       ID       text  PRIMARY KEY,
       NAME     text,
       BIRTH    text,
       DOCS     text,
       FATHER   text,
       MOTHER   text,
       CLASS    text
       )

When you correct your source code, don't forget to drop the table in the database or else the error will remain.