Problem in sqlite database architecture, what's wrong?

Question

Python

Salute to all. I am making a tkinter application for student accounting. There is a problem in the database, tell me what's wrong.

Database creation code:

class DB:
    def __init__(self):
        self.conn = sqlite3.connect('students.db')
        self.c = self.conn.cursor()

        self.c.execute(
            '''PRAGMA foreign_keys=on''')
        self.conn.commit()

        self.c.execute(
            '''CREATE TABLE IF NOT EXISTS gender(
            gender text)''')
        self.conn.commit()

        self.c.execute(
            '''INSERT OR IGNORE INTO gender (gender)
            VALUES ('М'), 
            ('Ж')''')
        self.conn.commit()

        self.c.execute(
            '''CREATE TABLE IF NOT EXISTS groupp(
            groupp text)''')
        self.conn.commit()

        self.c.execute(
            '''CREATE TABLE IF NOT EXISTS teacher(
            teacher text)''')

        self.c.execute(
            '''CREATE TABLE IF NOT EXISTS students(
            id integer primary key, 
            namme text, 
            groupp text,
            teacher text,
            nummber text,
            birth text,
            address text,
            gender text,
            FOREIGN KEY (groupp) REFERENCES groupp(groupp),
            FOREIGN KEY (teacher) REFERENCES teacher(teacher),
            FOREIGN KEY (gender) REFERENCES gender(gender))''')
        self.conn.commit()

    def insert_teacher(self, teacher):
        self.c.execute(
            '''INSERT INTO teacher(teacher)
            VALUES (?)''',
            (teacher, ))
        self.conn.commit()

    def insert_group(self, groupp):
        self.c.execute(
            '''INSERT INTO groupp(groupp)
            VALUES (?)''',
            (groupp, ))
        self.conn.commit()


    def insert_data(self, namme, groupp, teacher, nummber, birth, address, gender):
        self.c.execute(
            '''INSERT INTO students(namme, groupp, teacher, nummber, birth, address, gender)
            VALUES (?, ?, ?, ?, ?, ?, ?)''',
            (namme, groupp, teacher, nummber, birth, address, gender))
        self.conn.commit()

That is, to add a student to the system, his group and curator must already be created. What is the problem - when you enter a student into the database, the following error flies out:

Exception in Tkinter callback
Traceback (most recent call last):
  File "/usr/lib/python3.6/tkinter/__init__.py", line 1705, in __call__
    return self.func(*args)
  File "/home/overlord/PycharmProjects/student_registration/visual1.py", line 226, in <lambda>
    self.gender.get))
  File "/home/overlord/PycharmProjects/student_registration/visual1.py", line 77, in records
    self.db.insert_data(namme, groupp, teacher, nummber, birth, address, gender)
  File "/home/overlord/PycharmProjects/student_registration/visual1.py", line 349, in insert_data
    (namme, groupp, teacher, nummber, birth, address, gender))
sqlite3.OperationalError: foreign key mismatch - "students" referencing "gender"

Here is the code of the window for adding a student, can it be somehow related to the fact that it is the floor that goes through the Combobox?

class Child(tk.Toplevel):
    def __init__(self):
        super().__init__(root)
        self.init_child()
        self.view = app

    def init_child(self):
        self.title('Добавить студента.')
        self.geometry('400x420+400+300')
        self.resizable(False, False)

        label_namme = tk.Label(self, text='ФИО:')
        label_namme.place(x=50, y=50)
        label_groupp = tk.Label(self, text='Группа:')
        label_groupp.place(x=50, y=80)
        label_teacherr = tk.Label(self, text='Куратор:')
        label_teacherr.place(x=50, y=110)
        label_number = tk.Label(self, text='Номер телефона:')
        label_number.place(x=50, y=140)
        label_birth = tk.Label(self, text='Дата рождения:')
        label_birth.place(x=50, y=170)
        label_address = tk.Label(self, text='Адрес проживания:')
        label_address.place(x=50, y=200)
        label_genderr = tk.Label(self, text='Пол:')
        label_genderr.place(x=50, y=230)

        self.entry_namme = ttk.Entry(self)
        self.entry_namme.place(x=200, y=50)

        self.entry_groupp = ttk.Entry(self)
        self.entry_groupp.place(x=200, y=80)

        self.entry_teacherr = ttk.Entry(self)
        self.entry_teacherr.place(x=200, y=110)

        self.entry_numberr = ttk.Entry(self)
        self.entry_numberr.place(x=200, y=140)

        self.entry_birth = ttk.Entry(self)
        self.entry_birth.place(x=200, y=170)

        self.entry_address = ttk.Entry(self)
        self.entry_address.place(x=200, y=200)

        self.gender = ttk.Combobox(self, values=[u'М', u'Ж'])
        self.gender.current(0)
        self.gender.place(x=200, y=230)

        btn_cancel = ttk.Button(self, text='Закрыть', command=self.destroy)
        btn_cancel.place(x=300, y=270)

        self.btn_ok = ttk.Button(self, text='Добавить')
        self.btn_ok.place(x=220, y=270)
        self.btn_ok.bind('<Button-1>', lambda event: self.view.records(self.entry_namme.get(),
                                                                       self.entry_groupp.get(),
                                                                       self.entry_teacherr.get(),
                                                                       self.entry_numberr.get(),
                                                                       self.entry_birth.get(),
                                                                       self.entry_address.get(),
                                                                       self.gender.get()))

        self.grab_set()
        self.focus_set()

At the same time, I attach the code for adding a student to the database:

def records(self, namme, groupp, teacher, nummber, birth, address, gender):
        self.db.insert_data(namme, groupp, teacher, nummber, birth, address, gender)
        self.view_records()


    def insert_data(self, namme, groupp, teacher, nummber, birth, address, gender):
        self.c.execute(
            '''INSERT INTO students(namme, groupp, teacher, nummber, birth, address, gender)
            VALUES (?, ?, ?, ?, ?, ?, ?)''',
            (namme, groupp, teacher, nummber, birth, address, gender))
        self.conn.commit()

Answer 1

It throws an error because the gender insert query ('insert or ignore') tries to use keys, and they are not set in the table.
For example, so

CREATE TABLE IF NOT EXISTS gender(gender text primary key)

The rest is in structure. If you are still making a relational model, then the students table should store the id from these reference tables (groupp, teacher, etc.), and not the values ​​themselves. Then it will allow you to maintain the uniqueness of values, allow you to use the choice of values ​​​​from reference books (which will be these tables gender, teacher, etc.).
An example, for a teacher, is not just a table with teacher text, but a table with a key field, which is used to link to the student table and other editable fields.

CREATE TABLE IF NOT EXISTS teacher(
            teacher integer primary key,
            teacher_name text)

Then, for the teacher, it will be possible to add other fields that characterize him, change the name itself (first name / last name), while not breaking the key connection with the student table. Selecting each field for the students table will be a selection from the current values ​​of the corresponding lookup table, rather than a manual entry.
If a full-fledged relational model is not needed, then there is no point in making a separate table for each entity (in fact, they will not be used), you can store everything directly in the students table.