0

I wrote a small database manager, using SQLite, and it worked fine. After adding the last 2 fields (sq_idA , sq_idB), it is now locked. Before adding these two, everything was ok.

The problem will be trivial. I know that SQLite cannot perform many simultaneous transitions and is a lightweight database, but my database manager is also very small and lightweight. So the problem will be something in the code I wrote. Maybe it's because I didn't use conn.close? I'm just starting out with databases. However the error is found:

sqlite3.OperationalError: database is locked

Sorry if the code uses elements written not in English. The elements that I need to save are: campionato, giornata, calendario, ore, minuti, squadra_casa, squadra_fuori, ris_sq_casa, ris_sq_fuori, id_campionato, id_giornata. So far it worked fine, all ok, and the database hadn't crashed.

PROBLEM: After adding sq_idA, sq_idB, the database crashed. To be precise, it crashes when I insert a new record and click on the "Add" button. Before adding id_sqA, id_sqB, it worked fine, without problems. I want to specify that varoius combobox adds: campionato, giornata, calendario, ore, minuti, squadra_casa, squadra_fuori, ris_sq_casa, ris_sq_fuori. While id_campionato, id_giornata, id_sqA, id_sqB are added automatically by extracting the id.

The database manager consists of two files: main.py and db.py. I add the complete and directly executable code

MAIN.PY

from tkinter import *
from tkinter import ttk
from tkinter import messagebox
import tkinter as tk
from tkinter import ttk
from db import Database
import sqlite3
from PIL import ImageTk, Image
from tkcalendar import DateEntry
from datetime import date

db = Database('/database.db')
con = sqlite3.connect('database.db')
cursor = con.cursor()

root = Tk()
root.title("Gestione partite")
root.geometry("1920x1080+0+0")
root.config(bg="white")
root.state("normal")

#######################################
#StringVar
campionato = StringVar()
giornata = StringVar()
calendario = StringVar()
ore = StringVar()
minuti = StringVar()
squadra_casa = StringVar()
squadra_fuori = StringVar()
ris_sq_casa = StringVar()
ris_sq_fuori = StringVar()
squadracasa = StringVar()
squadrafuori = StringVar()
#######################################

#Functions

def combo_campionati():
    campionato = combo_Campionato.get()
    cursor.execute('SELECT Nome_Campionato FROM ARCHIVIO_CAMPIONATI')
    result=[row[0] for row in cursor]
    return result


def id_campionati():
    campionato = combo_Campionato.get()
    cursor.execute('SELECT ID_Campionato FROM ARCHIVIO_Campionati WHERE Nome_Campionato=?',(campionato,))
    result=[row[0] for row in cursor]
    return result[0]


def combo_squadre(event=None):
    campionato = combo_Campionato.get()
    cursor.execute('''
       SELECT s.Nome_Squadra FROM ARCHIVIO_Squadre_Campionato s, ARCHIVIO_CAMPIONATI c
       WHERE s.ID_Campionato=c.ID_Campionato AND c.Nome_Campionato = ?''', (campionato,))
    result=[row[0] for row in cursor]
    combo_NomeSquadra_Casa['values'] = result
    combo_NomeSquadra_Fuori['values'] = result
    return result


def combo_giornateee(event=None):
    # get all Number_Round for selected tournament
    cursor.execute('''
        SELECT Numero_Giornata From ARCHIVIO_Giornate r, ARCHIVIO_Campionati t
        WHERE r.ID_Campionato = t.ID_Campionato AND Nome_Campionato = ?''', (campionato.get(),))
    result=[row[0] for row in cursor]
    combo_Giornate['value'] = result  # update combo_Rounds
    combo_Giornate.set('Select') # reset Rounds selection
    return result

  
def id_giornate(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    cursor.execute('''
        SELECT ID_Giornata FROM ARCHIVIO_Giornate r, ARCHIVIO_CAMPIONATI t
        WHERE r.ID_Campionato = t.ID_Campionato AND Numero_Giornata = ? AND Nome_Campionato = ?''',
        (giornata.get(), campionato.get()))
    result = cursor.fetchone()
    if result:
        print(result[0])
        return result[0]
    return None

def id_sqAAA(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    squadracasa = combo_NomeSquadra_Casa.get()
    cursor.execute('SELECT ID_Sq FROM ARCHIVIO_Squadre_Campionato WHERE Nome_Squadra=?', (squadracasa,))
    result = cursor.fetchone()
    if result:
        print(result[0])
        return result[0]
    return None


def id_sqBBB(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    squadrafuori = combo_NomeSquadra_Fuori.get()
    cursor.execute('SELECT ID_Sq FROM ARCHIVIO_Squadre_Campionato WHERE Nome_Squadra=?', (squadrafuori,))
    result = cursor.fetchone()
    if result:
        print(result[0])
        return result[0]
    return None

def filtracampionati():
    tv.delete(*tv.get_children())
        
    campionati = combo_Campionato.get()
    giornate = combo_Giornate.get()
    cursor.execute('SELECT * FROM ARCHIVIO_Risultati WHERE campionato=? AND giornata=?',(campionati, giornate,))

    for row in cursor:
        tv.insert("", END, values=row)

#######################################
#Graphics of the two upper bars
        
#Barre sopra
barrasopra=Frame(root, width=2200, height=47, background="#333333")
barrasopra.place(x=0, y=0)

barra_seconda=Frame(root, width=2200, height=47, background="#eb5c00")
barra_seconda.place(x=0, y=47)

#Icona search
lenteingrandimento= tk.PhotoImage(file="/home/xxxxxx/Scrivania/xxxxxxx/img/search-icon2.png")
btn0 = tk.Button(root, image=lenteingrandimento, borderwidth=0, highlightthickness=0,  bg="#E95420", command=filtracampionati)
btn0.place(x=730, y=55)

#Combobox Campionati
lbl_Campionato = Label(root, text="Campionato", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Campionato.place(x=6, y=60)
combo_Campionato = ttk.Combobox(root, font=("Calibri", 11), width=20, textvariable=campionato, state="readonly")
combo_Campionato.place(x=110, y=60)
combo_Campionato.set("Seleziona campionato")
combo_Campionato['values'] = combo_campionati()
combo_Campionato.bind('<<ComboboxSelected>>', combo_squadre, combo_giornateee)
combo_Campionato.bind('<<ComboboxSelected>>', combo_giornateee, add=True)

#Combobox Squadre combinate
combo_NomeSquadra_Casa = ttk.Combobox(root, font=("Calibri", 11), width=17, textvariable=squadra_casa, state="readonly")
combo_NomeSquadra_Casa.place(x=6, y=225)
combo_NomeSquadra_Casa.set("Squadra Casa")

combo_NomeSquadra_Fuori = ttk.Combobox(root, font=("Calibri", 11), width=17, textvariable=squadra_fuori, state="readonly")
combo_NomeSquadra_Fuori.place(x=210, y=225)
combo_NomeSquadra_Fuori.set("Squadra Fuori")

#Combobox Giornate
lbl_Giornate = Label(root, text="Giornate", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Giornate.place(x=430, y=60)
combo_Giornate = ttk.Combobox(root, font=("Calibri", 11), width=20, textvariable=giornata, state="readonly") #ho tolto textvariable=giornate
combo_Giornate.place(x=510, y=60)
combo_Giornate.set("Seleziona giornata")

combo_Giornate['values'] = combo_campionati()
####################################################

#Window body graphics
labelframe = LabelFrame(root, text="Data e orario", width=400,height=90, bg="white", foreground='#eb5c00')
labelframe.place(x=8, y=120)

#calendario
lbl_Calendario = Label(root, text="Data", font=("Calibri", 11), bg="white", fg="black")
lbl_Calendario.place(x=15, y=143)

class MyDateEntry(DateEntry):
    def __init__(self, master=None, **kw):
        DateEntry.__init__(self, master=None, **kw)
        # add black border around drop-down calendar
        self._top_cal.configure(bg='black', bd=1)
        # add label displaying today's date below
        tk.Label(self._top_cal, bg='#77c030', anchor='w',
                 text='Oggi: %s' % date.today().strftime('%x')).pack(fill='x')

# create the entry and configure the calendar colors
de = MyDateEntry(root, year=2021, month=9, day=6, textvariable=calendario,
                 selectbackground='#77c030',
                 selectforeground='black',
                 normalbackground='white',
                 normalforeground='black',
                 background='#77c030',
                 foreground='black',
                 bordercolor='#77c030',
                 othermonthforeground='gray50',
                 othermonthbackground='white',
                 othermonthweforeground='gray50',
                 othermonthwebackground='white',
                 weekendbackground='white',
                 weekendforeground='black',
                 headersbackground='white',
                 headersforeground='gray70')
de.pack()
de.place(x=15, y=170)
   
#Ore
lbl_Ora = Label(root, text="Orario", font=("Calibri", 11), bg="white", fg="black")
lbl_Ora.place(x=197, y=143)
combo_Orario_Ora = ttk.Combobox(root, font=("Calibri", 11), width=7, textvariable=ore, state="readonly")
combo_Orario_Ora['values'] = ("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "00") 
combo_Orario_Ora.place(x=200, y=170)
combo_Orario_Ora.set("Ora")

#Minuti
combo_Orario_Minuti = ttk.Combobox(root, font=("Calibri", 11), width=7, textvariable=minuti, state="readonly")
combo_Orario_Minuti['values'] = ("00", "05", "10", "15", "20", "30", "40", "45", "50") 
combo_Orario_Minuti.place(x=300, y=170)
combo_Orario_Minuti.set("Minuti")

#Partita
lbl_Risultato_Sq_A = Label(root, text="Partita", font=("Calibri", 11), bg="white", fg="black")
lbl_Risultato_Sq_A.place(x=6, y=200)

#Risultato Sq A
lbl_Risultati = Label(root, text="Risultato Finale", font=("Calibri", 11), bg="white", fg="black")
lbl_Risultati.place(x=6, y=290)

combo_Risultato_Sq_A = ttk.Combobox(root, font=("Calibri", 11), width=7, textvariable=ris_sq_casa, state="readonly")
combo_Risultato_Sq_A['values'] = ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12") 
combo_Risultato_Sq_A.place(x=6, y=315)

#Risultato Sq B
combo_Risultato_Sq_B = ttk.Combobox(root, font=("Calibri", 11), width=7, textvariable=ris_sq_fuori, state="readonly")
combo_Risultato_Sq_B['values'] = ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11") 
combo_Risultato_Sq_B.place(x=120, y=315)
#####################################################

#Insert in Database
  
def getData(event):
    selected_row = tv.focus()
    data = tv.item(selected_row)
    global row
    row = data["values"]
    #print(row)

    campionato.set(row[1])
    giornata.set(row[2])
    calendario.set(row[3])
    ore.set(row[4])
    minuti.set(row[5])
    squadra_casa.set(row[6])
    squadra_fuori.set(row[7])
    ris_sq_casa.set(row[8])
    ris_sq_fuori.set(row[9])


def dispalyAll():
    tv.delete(*tv.get_children())
    for row in db.fetch():
        tv.insert("", END, values=row)


def add_employee():
    if combo_Campionato.get() == "" or combo_Giornate.get() == "" or  de.get() == "" or combo_Orario_Ora.get() == "" or combo_Orario_Minuti.get() == "" or combo_NomeSquadra_Casa.get() == "" or combo_NomeSquadra_Fuori.get() == "" or combo_Risultato_Sq_A.get() == "" or combo_Risultato_Sq_B.get() == "":
        messagebox.showerror("Hai mancato qualcosa", "Compila tutti i campi")
        return

    id_campionati_value=id_campionati()
    id_rounds_value=id_giornate()
    id_sqA_value=id_sqAAA()
    id_sqB_value=id_sqBBB()
    
    db.insert(campionato.get(), giornata.get(), de.get(),combo_Orario_Ora.get(), combo_Orario_Minuti.get(), combo_NomeSquadra_Casa.get(), combo_NomeSquadra_Fuori.get(), combo_Risultato_Sq_A.get(), combo_Risultato_Sq_B.get(), id_campionati_value, id_rounds_value, id_sqA_value, id_sqB_value)
    messagebox.showinfo("Partita inserita correttattamente", "Record Inserted")
    clearAll()
    dispalyAll()


def update_employee():
    if combo_Campionato.get() == "" or combo_Giornate.get() == "" or  de.get() == "" or combo_Orario_Ora.get() == "" or combo_Orario_Minuti.get() == "" or combo_NomeSquadra_Casa.get() == "" or combo_NomeSquadra_Fuori.get() == "" or combo_Risultato_Sq_A.get() == "" or combo_Risultato_Sq_B.get() == "":
        messagebox.showerror("Hai mancato qualcosa", "Compila tutti i campi")
        return

    id_campionati_value=id_campionati()
    id_rounds_value=id_giornate()
    
    db.update(row[0],combo_Campionato.get(),combo_Giornate.get(), de.get() , combo_Orario_Ora.get() ,combo_Orario_Minuti.get(), combo_NomeSquadra_Casa.get(), combo_NomeSquadra_Fuori.get(), combo_Risultato_Sq_A.get(), combo_Risultato_Sq_B.get(), id_campionati_value, id_rounds_value)
    messagebox.showinfo("Success", "Record Update")
    clearAll()
    dispalyAll()


def delete_employee():
    db.remove(row[0])
    clearAll()
    dispalyAll()


def clearAll():
    campionato.set("")
    giornata.set("")
    calendario.set("")
    ore.set("")
    minuti.set("")
    squadra_casa.set("")
    squadra_fuori.set("")
    ris_sq_casa.set("")
    ris_sq_fuori.set("")

##########################################

#Buttons

btnAdd = Button(root, command=add_employee, text="Aggiungi", width=12, font=("Calibri", 10, "bold"), fg="white",
                bg="#E95420", bd=0).place(x=5, y=10)
btnEdit = Button(root, command=update_employee, text="Modifica", width=12, font=("Calibri", 10, "bold"),
                 fg="white", bg="#E95420",
                 bd=0).place(x=150, y=10)
btnDelete = Button(root, command=delete_employee, text="Elimina", width=12, font=("Calibri", 10, "bold"),
                   fg="white", bg="#E95420",
                   bd=0).place(x=300, y=10)
btnClear = Button(root, command=clearAll, text="Pulisci", width=12, font=("Calibri", 10, "bold"), fg="white",
                  bg="#E95420",
                  bd=0).place(x=450, y=10)

###########################################

# Table Frame
tree_frame = Frame(root, bg="#ecf0f1")
tree_frame.place(x=0, y=570, width=1980, height=520)
style = ttk.Style()
style.configure("mystyle.Treeview", font=('Calibri', 11),
                rowheight=50)  # Modify the font of the body
style.configure("mystyle.Treeview.Heading", font=('Calibri', 11))  # Modify the font of the headings
tv = ttk.Treeview(tree_frame, columns=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15), style="mystyle.Treeview")
tv.heading("1", text="ID")
tv.column("1", width=5)

tv.heading("2", text="Campionato")
tv.column("2", width=70)

tv.heading("3", text="Giornata")
tv.column("3", width=70)

tv.heading("4", text="Data")
tv.column("4", width=70)

tv.heading("5", text="Ora")
tv.column("5", width=70)

tv.heading("6", text="Minuti")
tv.column("6", width=70)

tv.heading("7", text="Squadra Casa")
tv.column("7", width=70)

tv.heading("8", text="Squadra Ospite")
tv.column("8", width=70)

tv.heading("9", text="Risultato Sq Casa")
tv.column("9", width=70)

tv.heading("10", text="Risultato Sq Ospite")
tv.column("10", width=70)

tv.heading("11", text="id_campionato")
tv.column("11", width=70)

tv.heading("12", text="id_giornata")
tv.column("12", width=70)

tv.heading("13", text="id_sqA")
tv.column("13", width=70)

tv.heading("14", text="id_sqB")
tv.column("14", width=70)


tv['show'] = 'headings'
tv.bind("<ButtonRelease-1>", getData)
tv.pack(fill=X)

  
#COLORE TABELLA
style = ttk.Style(root)
style.configure("Treeview",
                background="white",
                foreground="#000000",
                rowheight=25,
                fieldbackground="white")
style.map('Treeview', background=[('selected', '#eb5c00')])


dispalyAll()
root.mainloop()

DB.PY

import sqlite3


class Database:
    def __init__(self, db):
        self.con = sqlite3.connect(db)
        self.cur = self.con.cursor()
        sql = """
        CREATE TABLE IF NOT EXISTS employees(
            ID Integer Primary Key,
            campionato integer,
            giornata integer,
            calendario integer,
            ore integer,
            minuti integer,
            squadra_casa integer,
            squadra_fuori integer,
            ris_sq_casa integer,
            ris_sq_fuori integer,
            id_campionato integer,
            id_giornata integer,
            id_sqA integer,
            id_sqB integer
        )
        """
        self.cur.execute(sql)
        self.con.commit()



    # Insert Function
    def insert(self, campionato, giornata, calendario, ore, minuti, squadra_casa, squadra_fuori, ris_sq_casa, ris_sq_fuori, id_campionato, id_giornata, id_sqA, id_sqB):
        self.cur.execute("insert into ARCHIVIO_Risultati values (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                         (campionato, giornata, calendario, ore, minuti, squadra_casa, squadra_fuori, ris_sq_casa, ris_sq_fuori, id_campionato, id_giornata, id_sqA, id_sqB))
        self.con.commit()

    # Fetch All Data from DB
    def fetch(self):
        self.cur.execute("SELECT * from ARCHIVIO_Risultati")
        rows = self.cur.fetchall()
        # print(rows)
        return rows

    #I don't write the DELETE and UPDATE code so as not to dwell on it

How to solve? Thank you

UPDATE: I add the 4 necessary tables of the database code to create a playable example between main.py, db.py and sqlite database update

CREATE TABLE "ARCHIVIO_Campionati" (
    "ID_Campionato" INTEGER,
    "Nome_Campionato"   TEXT,
    PRIMARY KEY("ID_Campionato" AUTOINCREMENT)
);

CREATE TABLE "ARCHIVIO_Squadre_Campionato" (
    "ID_Sq" INTEGER,
    "Nome_Squadra"  TEXT,
    "Campionato"    INTEGER,
    "ID_Campionato" INTEGER,
    PRIMARY KEY("ID_Sq" AUTOINCREMENT),

CREATE TABLE "ARCHIVIO_Giornate" (
    "ID_Giornata"   INTEGER,
    "Numero_Giornata"   INTEGER,
    "ID_Campionato" INTEGER,
    PRIMARY KEY("ID_Giornata" AUTOINCREMENT),
 "ARCHIVIO_Campionati"("ID_Campionato")
);

CREATE TABLE "ARCHIVIO_Risultati" (
    "ID"    INTEGER,
    "campionato"    INTEGER,
    "giornata"  INTEGER,
    "calendario"    INTEGER,
    "ore"   INTEGER,
    "minuti"    INTEGER,
    "squadra_casa"  INTEGER,
    "squadra_fuori" INTEGER,
    "ris_sq_casa"   INTEGER,
    "ris_sq_fuori"  INTEGER,
    "id_campionato" INTEGER,
    "id_giornata"   INTEGER,
    "id_sqA"    INTEGER,
    "id_sqB"    INTEGER,
    PRIMARY KEY("ID" AUTOINCREMENT)
);
Erling Olsen
  • 1
  • 4
  • 15
  • It doesn't seem clear, but to eliminate this obvious thing: you've added 2 columns and did or didn't re-create the table? – mechanical_meat Oct 13 '21 at 00:12
  • @mechanical_meat Are you talking about sq_idA and sq_idB? Yes, I have added 2 columns in the Table Frame. And I added sq_idA and sq_idB in the table as well. I did the same thing for the other data in the table and they all fit correctly, without any problems. The database crashed with sq_idA and sq_idB. Can you help me please? Thanks – Erling Olsen Oct 13 '21 at 00:17
  • 1
    Please try to create a [mcve]. It looks like you can probably remove all of the tkinter code since the question seems to be about SQLite. – Bryan Oakley Oct 13 '21 at 00:26
  • @BryanOakley If you copy and paste some code, it is reproducible. I entered the complete code. What else do I need to add? Thanks – Erling Olsen Oct 13 '21 at 00:29
  • @BryanOakley I also added the database code to make the code reproducible. True, the tkinter code is not about the question, but I would like to leave to offer better reproducibility (even graphics) to the people who will read. I hope you can help me please. Thanks – Erling Olsen Oct 13 '21 at 01:00
  • 1
    Ideally you can reduce the problem down to a test anyone can run. In the best case, reducing the code to the actual problem makes the actual problem clear before you post, and you can solve it yourself. – John Bayko Oct 13 '21 at 01:19
  • We don’t want your entire program. We need you to make a _minimal_ example with just enough code to reproduce the problem and nothing more. Please read [mcve], which explains how and why. – Bryan Oakley Oct 13 '21 at 01:23
  • @JohnBayko Difficult to reduce the code because it is all connected. Anyway thanks for the advice. You're kind. Could you help me anyway please? Thanks – Erling Olsen Oct 13 '21 at 01:24
  • Did you notice that `db = Database('/database.db')` and `con = sqlite3.connect('database.db')` may refer to different database files? – acw1668 Oct 13 '21 at 01:32
  • Be sure to use the current ANSI-92 standard of explicit joins and not in the deprecated implicit join in your SQL queries. See [Explicit vs implicit SQL joins](https://stackoverflow.com/q/44917/1422451). – Parfait Oct 13 '21 at 01:33
  • @acw1668 Yes. It's the same database file. Same path and same file. Before adding sq_idA and sq_idB everything worked fine, everything ok, no problems. Consequently, this is not the problem. The problem is something involving sq_idA and sq_idB. I tried to print the result and it prints correctly, but it does not insert and save in the database. Without sq_idA and sq_idB everything worked correctly, without problems. Could you help me please? – Erling Olsen Oct 13 '21 at 01:39
  • @Parfait Everything worked fine, without problems, before adding sq_idA and sq_idB. sq_idA, sq_idB have a simple SELECT ID_Sq FROM ARCHIVIO_Squadre_Campionato WHERE Nome_Squadra=?', (squadrafuori,)). So without joins – Erling Olsen Oct 13 '21 at 01:42
  • I have created database with the table definitions (there are errors in your table definitions) provided and I have no problem on inserting data into the table. – acw1668 Oct 13 '21 at 01:42
  • My suggestion did not relate directly to your error. Explicit and implicit joins return the same result but explicit joins are easier to read, maintain, and understand. – Parfait Oct 13 '21 at 01:44
  • @acw1668 The data in the table was all inserted correctly. I speak in the past tense. Since I have entered sq_idA and sq_idB, then the database freezes and does not insert. Are you also adding sq_idA and sq_idB? If I delete every trace of sq_idA and sq_idB code from inside main.py, db.py and from the database ... then the database no longer crashes and the data is inserted – Erling Olsen Oct 13 '21 at 01:46

1 Answers1

1

Essentially, you are opening two connections and cursors to the same database by the same user. This will impact subsequent operations depending on tasks undertaken.

db = Database('/database.db')            # OPENS AND PERSISTS CONNECTION
con = sqlite3.connect('database.db')     # OPENS ANOTHER CONNECTION
cursor = con.cursor()

Consider refactoring your code to run all database operations in the Database class. Specifically, create generalized methods for SELECT queries in Database. Note: Below is untested. Adjust as needed.

DB.PY (see two added methods)

import sqlite3

class Database:
    def __init__(self, db):
        self.con = sqlite3.connect(db)
        self.cur = self.con.cursor()
        sql = """
        CREATE TABLE IF NOT EXISTS employees(
            ID Integer Primary Key,
            campionato integer,
            giornata integer,
            calendario integer,
            ore integer,
            minuti integer,
            squadra_casa integer,
            squadra_fuori integer,
            ris_sq_casa integer,
            ris_sq_fuori integer,
            id_campionato integer,
            id_giornata integer,
            id_sqA integer,
            id_sqB integer
        )
        """
        self.cur.execute(sql)
        self.con.commit()


    # INSERT DATA
    def insert(
        self, campionato, giornata, calendario, 
        ore, minuti, squadra_casa, squadra_fuori, 
        ris_sq_casa, ris_sq_fuori, id_campionato, 
        id_giornata, id_sqA, id_sqB
    ):
        sql = (
            "INSERT INTO ARCHIVIO_Risultati "
            "VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        )
        self.cur.execute(
            sql,
            (campionato, giornata, calendario, ore, minuti, 
             squadra_casa, squadra_fuori, ris_sq_casa, 
             ris_sq_fuori, id_campionato, id_giornata, 
             id_sqA, id_sqB)
        )
        self.con.commit()

    # FETCH ALL DATA FROM DB
    def fetch(self):
        self.cur.execute("SELECT * from ARCHIVIO_Risultati")
        rows = self.cur.fetchall()
        # print(rows)
        return rows

    # RUN SELECT QUERIES TO FETCH ONE 
    def select_one_query(self, sql, params):
        self.cur.execute(sql, params)
        result = self.cur.fetchone()

        return result

    # RUN SELECT QUERIES TO FETCH ALL
    def select_all_query(self, sql, params):
        self.cur.execute(sql, params)
        result = self.cur.fetchall()
        return result

    # rest of code

MAIN.PY (mainly functions shown, no second connection or cursor)

...
db = Database('/database.db')

root = Tk()
root.title("Gestione partite")
root.geometry("1920x1080+0+0")
root.config(bg="white")
root.state("normal")

...

#Functions
def combo_campionati():
    campionato = combo_Campionato.get()
    sql = (
        'SELECT Nome_Campionato '
        'FROM ARCHIVIO_CAMPIONATI '
        'WHERE ?'
    )
    data = db.select_all_query(sql, (True,))
    result = [row[0] for row in data]
    return result


def id_campionati():
    campionato = combo_Campionato.get()
    sql = (
        'SELECT ID_Campionato '
        'FROM ARCHIVIO_Campionati '
        'WHERE Nome_Campionato = ?'
    )
    
    data = db.select_all_query(sql, (campionato,))
    result = [row[0] for row in data]

    return result[0]


def combo_squadre(event=None):
    campionato = combo_Campionato.get()

    sql = (
        'SELECT s.Nome_Squadra '
        'FROM ARCHIVIO_Squadre_Campionato s '
        'INNER JOIN ARCHIVIO_CAMPIONATI c '
        '  ON  s.ID_Campionato = c.ID_Campionato '
        '  AND c.Nome_Campionato = ?'
    )
    
    data = db.select_all_query(sql, (campionato,))
    result = [row[0] for row in data]

    combo_NomeSquadra_Casa['values'] = result
    combo_NomeSquadra_Fuori['values'] = result

    return result


def combo_giornateee(event=None):
    # get all Number_Round for selected tournament
    sql = (
        'SELECT Numero_Giornata '
        'FROM ARCHIVIO_Giornate r '
        'INNER JOIN ARCHIVIO_Campionati t '
        '   ON r.ID_Campionato = t.ID_Campionato '
        '   AND t.Nome_Campionato = ?'
    )

    data = db.select_all_query(sql, (campionato.get(),))
    result = [row[0] for row in data]

    combo_Giornate['value'] = result       # update combo_Rounds
    combo_Giornate.set('Select')           # reset Rounds selection

    return result

  
def id_giornate(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    sql = (
        'SELECT ID_Giornata '
        'FROM ARCHIVIO_Giornate r '
        'INNER JOIN ARCHIVIO_CAMPIONATI t '
        '   ON r.ID_Campionato = t.ID_Campionato '
        '   AND r.Numero_Giornata = ? '
        '   AND t.Nome_Campionato = ?'
    )
    
    result = db.select_one_query(
        sql, 
        (giornata.get(), campionato.get()) 
    )
    if result:
        print(result[0])

    return result[0] if result else None

def id_sqAAA(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    squadracasa = combo_NomeSquadra_Casa.get()

    sql = (
        'SELECT ID_Sq '
        'FROM ARCHIVIO_Squadre_Campionato '
        'WHERE Nome_Squadra = ?'
    )

    result = db.select_one_query(sql, (squadracasa,))
    if result:
        print(result[0])

    return result[0] if result else None


def id_sqBBB(event=None):
    # get the ID_Round based on selected tournament and Number_Round
    squadrafuori = combo_NomeSquadra_Fuori.get()

    sql = (
        'SELECT ID_Sq '
        'FROM ARCHIVIO_Squadre_Campionato '
        'WHERE Nome_Squadra = ?'
    )
 
    result = db.select_one_query(sql, (squadrafuori,))
    if result:
        print(result[0])

    return result[0] if result else None
       

def filtracampionati():
    tv.delete(*tv.get_children())
        
    campionati = combo_Campionato.get()
    giornate = combo_Giornate.get()
    sql = (
        'SELECT * '
        'FROM ARCHIVIO_Risultati '
        'WHERE campionato = ? '
        '  AND giornata = ?'
    )
    data = db.select_all_query(sql,(campionati, giornate,))

    for row in data:
        tv.insert("", END, values=row)

# rest of code

Final word

Please heed the zen of Python: Explicit is better than implicit.

  • Above code replaced implicit joins for explicit joins.
  • Consider replacing SELECT * FROM queries with explicit reference of columns to control order and existence (SELECT Col1, Col2, Col3, ...) for application.
  • In JOIN queries, always explicitly reference columns with table alias. You did not include r. and t. on some WHERE columns.
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thank you very much. You have been very kind. I appreciate. Now it is late at night in my country. I'll try the code calmly tomorrow and let you know. One question: why is the database crashed now with these last two fields sq_idA, sq_idB? Why didn't it crash before I added these two fields in the code? Thanks – Erling Olsen Oct 13 '21 at 02:14
  • Enthusiastic, I applied your changes. In the tree view I see the data, but I cannot enter anything, because the two main comboboxes do not work: combo_Campionato and combo_Giornate. If I open them inside they are empty – Erling Olsen Oct 13 '21 at 02:45
  • 1
    See edit. I had placed parameters in the first functions but removed them now aligned to your original. Not sure why those fields is reason for crash but it looks like you are running operations on the same table `ARCHIVIO_Risultati`, looping through results and append data to same table in different connections. Somewhere the traffic is causing lock out. – Parfait Oct 13 '21 at 19:49
  • I still have the same problem as the comment above. P.S: you said above "You did not include r. And t. On some WHERE columns.". Where have I not included them? (the problem is not this, because the database manager was working correctly, but I'm curious to know where I have not included r. and t.) – Erling Olsen Oct 15 '21 at 23:33
  • Looks like I had to fix the DB.py functions a bit and one in MAIN.py. I have tested with no error (after replacing the search icon image) now but I have no data to your tables. Re missing table aliases, see `WHERE` clauses of queries in your `combo_giornateee` and `id_giornate` methods. – Parfait Oct 16 '21 at 04:30
  • Unfortunately I have not solved the problem. Thanks all the same for your kindness. I still voted on your answer – Erling Olsen Oct 21 '21 at 22:09