Problem concatenate str to database for recall the two ID in button Insert

Question

I created these two functions, adding various + in curson.execute:

def getIDCampionato(nome_campionato):
    cursor.execute('SELECT ID_Campionato FROM ARCHIVIO_Campionati WHERE Nome_Campionato = "' + nome_campionato + '";')
    result=[row[0] for row in cursor]
    return result

def getIDGiornata(id_campionato, giornata):
    cursor.execute('SELECT Numero_Giornata FROM ARCHIVIO_Giornate WHERE Relaz_Campionato = "' + id_campionato + '" AND Numero_Giornata = "' + giornata + '";')
    result=[row[0] for row in cursor]
    return result

but i get the error:

python TypeError: can only concatenate str (not "list") to str

ERROR: The error is where I wrote "+ nome_campionato + '";' and "+ id_campionato + '" AND Numero_Giornata = "' + giornata + '";". Previously I did not find errors when there was only Select, From and Where (when there were not yet the various +), so i believe the sql is correct.

WHAT SHOULD I DO? This is not needed, because the error is above, but I add this code for a better completeness of the question. The two functions are used to recall the id of the tournament (campionato) and the id of the giornata (round), extracted from the sqlite database, in relation to each corresponding giornata (i.e. weekly round with different matches inside). Why do I want to recall the two ID? Because there are two comboboxes: campionato (tournament) and giornata (38 for each campionato/tournament). The two comboboxes are combined. I select the campionato (tournament) and then select one of the 38 rounds (giornata). In each round, empty, I can insert different records (down TABLE "ARCHIVIO_Risultati"). Each tournament will have 38 different rounds. So for Sql identification reasons, I created the two aforementioned functions. Subsequently they are called up here, they are called within the function of the "Add / Add" button to add records in the database (through the manual compilation of textobox and combobx).

def add_employee():
    id_camp = getIDCampionato(combo_Campionato.get())
    id_giornata = getIDGiornata(id_camp, combo_Giornate.get())
    
    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("Error")
        return
    db.insert(id_camp, id_giornata, 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())
    messagebox.showinfo("Record ok")
    clearAll()
    dispalyAll()

For example, I want to obtain, through the two campionato and giornata comboboxes, this:

Campionato: Serie A | Giornata 1 >>>>>> I insert various matches and info
Campionato: Serie A | Giornata 2 >>>>>> I insert various matches and info
Campionato: Serie A | Giornata ... >>>>>> I insert various matches and info
Campionato: Serie A | Giornata 38 >>>>>> I insert various matches and info

Campionato: Serie B | Giornata 1 >>>>>> I insert various matches and info
Campionato: Serie B | Giornata 2 >>>>>> I insert various matches and info
Campionato: Serie B | Giornata ... >>>>>> I insert various matches and info
Campionato: Serie B | Giornata 38 >>>>>> I insert various matches and info

To make you better understand, here are the database tables:

#Tournaments (Campionati)
CREATE TABLE "ARCHIVIO_Campionati" (
    "ID_Campionato" INTEGER, (example 435)
    "Nome_Campionato"   TEXT, (example Serie A)
    PRIMARY KEY("ID_Campionato" AUTOINCREMENT)
);

#Rounds (Giornate)
CREATE TABLE "ARCHIVIO_Giornate" (
    "ID_Giornata"   INTEGER,
    "Relaz_Campionato"  INTEGER, (example 435)
    "Numero_Giornata"   INTEGER, (1 to 38)
    FOREIGN KEY("Relaz_Campionato") REFERENCES "ARCHIVIO_Campionati"("ID_Campionato"),
    PRIMARY KEY("ID_Giornata" AUTOINCREMENT)
);

#Data to be saved in each of the 38 different rounds (Giornate). Each Tournaments consists of 38 rounds
CREATE TABLE "ARCHIVIO_Risultati" (
    "ID"    INTEGER,
    "campionato"    INTEGER,
    "giornate"  INTEGER,
    "calendario"    INTEGER,
    "ore"   NUMERIC,
    "minuti"    NUMERIC,
    "squadra_casa"  INTEGER,
    "squadra_fuori" INTEGER,
    "ris_sq_casa"   NUMERIC,
    "ris_sq_fuori"  NUMERIC,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

Can you show me how I can correct the two functions (at the beginning of the question) getIDCampionato and getIDGiornata please? Thanks

Answer 1

As per the first error you have displayed, make sure you are not passing a list to these functions:

[ nome_campionato single string ] *not part of code

[ id_campionato & giornata single integer values ] *not part of code

import traceback

def getIDCampionato(nome_campionato):
    if isinstance(nome_campionato,str):
        try:
            query1 = "SELECT ID_Campionato FROM ARCHIVIO_Campionati WHERE Nome_Campionato = ?;"
            cursor.execute(query1, (nome_campionato,)) # if error, try passing these params in a list like -> [nome_campionato]
            result=[row[0] for row in cursor]
            return result
        except:
            print('Error generated for ->', nome_campionato, '\n')
            print(traceback.format_exc())
    else:
        print('This needs to be a string ->', nome_campionato)
        return 0

# as per the error generated this function should work fine, try it standalone
def getIDGiornata(id_campionato, giornata):
    if isinstance(id_campionato, int) and isinstance(giornata, int):
        query2 = "SELECT Numero_Giornata FROM ARCHIVIO_Giornate WHERE Relaz_Campionato = ? and Numero_Giornata = ?;"
        cursor.execute(query2, (id_campionato, giornata,)) # [id_campionato, giornata]
        result=[row[0] for row in cursor]
        return result
    else:
        print('Both need to be integer values -> id_campionato = ', id_campionato, ' || giornata = ', giornata)
        return 0

Try the updated functions above.

The reason it is not printing is that we were confirming the datatype directly, so that seems to be correct, the issue is something else! still to get an idea I have added some more print statements.

I have added commas as suggested by error resolution

try calling the function getIDGiornata(id_campionato, giornata) separately to see if it works, if not add the try-except block as the previous function and post an image.