Relational database with Foreign Key for a combobox (Nations / Cities). Problem

  python, python-2.7, python-3.x, sql, sqlite

In a relationship database (SQLite), with Foreign Key, i have a table called "Nations" containing Nations inserted in random order like France, Italy, England, Japan.

Then i have another table called "Cities" containing randomly placed cities like Tokyo, Paris, London, Rome, Bordeuax, Kyoto, Liverpool, Milan, etc.

I used a Foreign Key to relate cities to nations. I think i have done well, the problem is that I can’t enter every city in every related country. For example I cannot do that: England = London, Liverpool; Japan = Tokyo, Kyoto.

To view this relationship between country and city I use two comboboxes: I would like to select a country in the first combobox and then in the second combobox I would like to display the relative cities of the selected country. What am I doing wrong? Why doesn’t the relationship between cities and countries work? Can you answer me show me the code please? Thank you

This is the Nations database:

CREATE TABLE "Nations_Table" (
"ID_Nations"    INTEGER PRIMARY KEY AUTOINCREMENT,
"Nations"   TEXT
);

This is the Foreign Key Cities database

CREATE TABLE "Cities_Table" (
"ID_Cities" INTEGER PRIMARY KEY AUTOINCREMENT,
"Cities"    TEXT,
"ID_Nations"    INTEGER,
FOREIGN KEY("ID_Nations") REFERENCES "Nation"("ID_Nations") ON DELETE NO ACTION ON UPDATE NO ACTION
);

This is the Python code I use, but it doesn’t work correctly of course:

### COMBOBOX NATIONS ###
def combo_nations():
    cursor.execute('SELECT DISTINCT Nations_Table FROM Nation_Table')
    result=[row[0] for row in cursor]
    return result

 ### COMBOBOX CITIES ###
 def combo_cities(event=None):
     val = nations.get()
     cursor.execute('SELECT Cities_Table FROM xxxx WHERE xxxx = ?', (val,))
     result = [row[0] for row in cursor]
     citiesss['value'] = result
     citiesss.current(0)


nations=ttk.Combobox(window, width = 25)
nations['value'] = combo_nations()
nations.bind('<<ComboboxSelected>>', combo_cities)
nations.place(x=13, y=80)
nations.set("Choose Nations")

combo_cities=ttk.Combobox(window, width = 25)
combo_cities.place(x=13, y=120)
combo_cities.set("Choose Cities")

Source: Python Questions

LEAVE A COMMENT