Problem in a function that extracts and saves fields from a database table to another-CodePudding

If in the combo_Nations combobox I select a specific Country (the country name is extracted from the "All_Nations" table in the "Nations_name" column), I would like to get the corresponding ID_Nations of the respective selected country (ID_Nations is found in the same table "All_Nations"). The ID will be automatically inserted in another table of the database together with other fields, after clicking on the "Add" button with the function def_add. (If you are wondering why I need to automatically insert the ID_Nation from another table, the reason is that I need it for relational purposes for a Foreign Key)

So I would like to take this data circled in red from the screenshot (I cannot attach images here): screenshot. Basically the table is this:

CREATE TABLE "All_Nations" (
    "ID_Nations"    INTEGER, >>> example: 453
    "Nations_name"  INTEGER, >>> example: England
    PRIMARY KEY("ID_Nations" AUTOINCREMENT)
);

So the combobox combo_Nations with the def combo_nations function fetches Nations_name only. While def id_nations should extract ID_Nations corresponding to the selected Nation from the combobox. EXAMPLE: For example, if I select England in the combobox, id_nations should automatically save me 453. The two data will be saved in a new table thanks to the function def add () (I only wrote part of this code so as not to dwell on it, showing you the minimum to understand what I serves, because it works correctly). The new table will save a row in which there will be: ID, Nations_name, ID_Nations, other data of various kinds.

The error I get is this TypeError: 'list' object is not callable, to be precise this is it:

   db.insert(nations.get(),.....other data .get(), id_campionati_value())
TypeError: 'list' object is not callable

I don't know if I wrote the def id_nations function incorrectly, if the problem is in db.insert or if the problem is both

HERE'S THE CODE. There is some problem in the def id_nations function I created:

def id_nations():
    nations = combo_Nations.get()
    cursor.execute('SELECT ID_Nations FROM All_Nations WHERE Nations_name=?',(nations,))
    result = cursor.fetchone()
    return result

#this is ok. no problem
def combo_nations():
    campionato = combo_Nations.get()
    cursor.execute('SELECT Nations_name FROM All_Nations')
    result=[row[0] for row in cursor]
    return result

#Combobox Nations
lbl_Nations = Label(root, text="Nations", font=("Calibri", 11), bg="#E95420", fg="white")
lbl_Nations.place(x=6, y=60)
combo_Nations = ttk.Combobox(root, font=("Calibri", 11), width=30, textvariable=nations, state="readonly")
combo_Nations.place(x=180, y=60)
combo_Nations.set("Select")
combo_Nations['values'] = combo_nations()
combo_Nations.bind('<<ComboboxSelected>>', combo_city)

The data will be saved here (everything works fine):

def add():
    id_campionati_value=id_campionati()
    
    db.insert(nations.get(),.....other data .get(), id_campionati_value())
    messagebox.showinfo("Success", "Record Inserted")
    clearAll()
    dispalyAll()

Theoretically I understand what the problem is, but I don't know how to solve it. I'm just starting out with Python. Can you show me the solution in the answer? Thanks

P.S: I removed unnecessary parts of code so as not to lengthen the question, but there is everything you need to understand. Thanks

UPDATE Rest of the code to insert data into the database. I write it in a somewhat confusing way because I have two files (main.py and db.py) and the code is split. If I write all the code of the two files it is too long.

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

    nations.set(row[1])
    ....
    id_nations.set(row[10])



    # Insert Function
    def insert(self, nations,....., id_nations):
        self.cur.execute("insert into All_Nations values (NULL,?,?,?,?,?,?,?,?,?,?)",
                         (nations,..., id_nations))
        self.con.commit()

CodePudding user response：

Based on the error, id_campionati_value is a list. When you do id_campionati_value() you're calling the list, and as the error says, you can't call the list.

Since you seem to need a single value, you first need to define id_nations to return the id rather than a list. It would look something like in the following example, returning the first column of the matching row.

I would also suggest renaming the function to make it more clear that it's fetching something from the database:

def get_id_nations():
    nations = combo_Nations.get()
    cursor.execute('SELECT ID_Nations FROM All_Nations WHERE Nations_name=?',(nations,))
    result = cursor.fetchone()
    return result[0]

Then, you can use this value when calling db.insert:

id_nations = get_id_nations()
db.insert(nations.get(),.....other data .get(), id_nations)