Issue with python tkinter sqlite3

londer · August 12, 2021, 11:28am

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 , all the names are updated (not just the one that was selected), and i have also 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º"]],
]

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

erlendaasland · August 12, 2021, 7:05pm

From a short glance at your SQL queries, here’s some hints:

You’ve created a table SCHOOL with primary key CLASS and type name txt oid. This also explains your failing insert statement.
Your update statement updates every row because your WHERE clause is always true; you probably want to supply the rowid of the row you are updating.

BTW, SQLite will create your integer primary key for you; you don’t need to create it explicitly.

Good luck

londer · August 12, 2021, 8:14pm

Thanks very much for your help! I will make these suggested modifications

Topic		Replies	Views
How to update an sqlite database from the end in tkinter Python Help help	0	1255	August 16, 2021
Python tkinter Combo box help Python Help help	5	2263	February 28, 2021
Difference between "ID" in sqlite3 table with Treeview box in tkinter Python Help	6	1042	July 11, 2023
PostGreSQL + tkinter conundrum Python Help help	1	165	February 1, 2024
Python Tkinter help Python Help help	1	510	January 19, 2021

Issue with python tkinter sqlite3

Related Topics