Sqlite3 Table users has 7 columns but 6 values were supplied

Question

Please I am new to python and sqlite3, I have tried to find the possible cause why the registration form won't submit, it keeps giving me an error. and the supplied values keep counting down

c.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?, ?)", (element)) sqlite3.OperationalError: table users has 7 columns but 6 values were supplied

   # Importing Tkinter framework
            fr
    
    om tkinter import *
        from tkinter import ttk
        # Import sqlite3
        import sqlite3
        
        
        def setup_db():
            # Open db
            global conn
            conn = sqlite3.connect('shengen.db')
            # Create a cursor
            global c
            c = conn.cursor()
        
            # Create the table if it doesn't exist
            try:
                c.execute("""CREATE TABLE if not exists users(
                    ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    fname TEXT NOT NULL,
                    email TEXT NOT NULL,
                    password TEXT NOT NULL,
                    cPassword TEXT NOT NULL,
                    sex INTEGER NOT NULL,
                    country TEXT NOT NULL
                    );""")
        
                conn.commit()
        
            except sqlite3.OperationalError:
                print("ERROR: Table not Created")
        
        
        def reg_submit():
            # Insert record into the db
            new_user = [fname.get(), email.get(), password.get(),
                        cPassword.get(), sex.get(), country.get()]
        
            for element in new_user:
                c.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?, ?)", (element))
        
            conn.commit()
            c.close()
            conn.close()
        
        
        def register():
            root1 = Toplevel(root)
            root1.geometry("900x700")
            root1.title("Registration Page")
            root1.iconbitmap("")
        
            global fname
            global email
            global password
            global cPassword
            global sex
            global sex1
            global country
        
            fname = StringVar()
            email = StringVar()
            password = StringVar()
            cPassword = StringVar()
            sex = IntVar()
            sex1 = IntVar()
            country = StringVar()
        
            Label(root1, text="", bg="grey", height="2",
                  width="900", font=("Calibri", 14)).pack()
        
            label_0 = Label(root1, text="Registration Form",
                            font=("bold", 24))
            label_0.place(x=250, y=53)
        
            label_1 = Label(root1, text="FullName:", width=20, font=("bold", 10))
            label_1.place(x=195, y=130)
        
            entry_1 = Entry(root1, width=40, textvariable=fname)
            entry_1.place(x=320, y=130)
        
            label_2 = Label(root1, text="Email:", width=20, font=("bold", 10))
            label_2.place(x=205, y=180)
        
            entry_2 = Entry(root1, width=40, textvariable=email)
            entry_2.place(x=320, y=180)
        
            label_3 = Label(root1, text="Password:", width=23, font=("bold", 10))
            label_3.place(x=180, y=230)
        
            entry_3 = Entry(root1, width=40, textvariable=password)
            entry_3.place(x=320, y=230)
        
            Label_6 = Label(root1, text="Confirm Password:",
                            width=25, font=("bold", 10))
            Label_6.place(x=150, y=280)
        
            entry_4 = Entry(root1, width=40, textvariable=cPassword)
            entry_4.place(x=320, y=280)
        
            label_4 = Label(root1, text="Gender:", width=20, font=("bold", 10))
            label_4.place(x=200, y=330)
            Radiobutton(root1, text="Male", padx=5, textvariable=sex,
                        value=1).place(x=320, y=330)
            Radiobutton(root1, text="Female", padx=20, textvariable=sex1,
                        value=2).place(x=400, y=330)
            label_5 = Label(root1, text="Country", width=20, font=("bold", 10))
            label_5.place(x=200, y=380)
            list1 = ['Afghanistan', 'Angola', 'Algeria', 'Albania', 'Akrotiri', 'Armenia', 'Austria', 'Australia', 'Azerbaijan', 'American Samoa', 'Antarctica',
                     'Argentina', 'Belgium', 'Borkina Faso', 'Benin', 'Bolivia', 'Botswana', 'Brazil', 'Bulgaria', 'Burundi', 'China', 'Canada', 'Cameroon', 'Chade',
                     'Chile', 'Colombia', 'Comoros', 'Congo Democatic Republic', 'Costa Rica', 'Cape Verde', 'Crotia', 'Cuba', 'Czech Republic', 'India', 'UK', 'Nepal', 'Iceland', 'South Africa', 'Ghana', 'Kenya', 'Germany', 'North Korea',
                     'Netherland', 'Iceland', 'Poland']
            droplist = OptionMenu(root1, country, *list1)
            droplist.config(width=40)
            country.set('Select Country')
            droplist.pack()
            droplist.place(x=320, y=380)
            btn_submit = Button(root1, text='Submit', width=20, bg='red',
                                fg='white', font=(14), command=reg_submit)
            btn_submit.pack()
            btn_submit.place(x=360, y=470)
        
        
        def login():
            pass
        
        
        def window():
            global root
            root = Tk()
            root.geometry("900x700")
            root.title("Welcome Page")
            root.iconbitmap("")
        
            Label(root, text="Welcome To Python Visa Application Portal! \n\nTo check your visa application status, file a new application or update your application, \nLogin or Create an account.",
                  fg="white", bg="grey", height="6", width="900", font=("Calibri", 14)).pack()
            Label(root, text="").pack()
            Label(root, text="").pack()
            Label(root, text="").pack()
            Label(root, text="").pack()
            Label(root, text="").pack()
            Button(root, text="Login", width=20, font=(
                "bold", 14), command=login).pack()
            Label(root, text="").pack()
            Button(root, text="Create Account", width=20,
                   font=("bold", 14), command=register).pack()
            Label(root, text="").pack()
            Label(root, text="").pack()
            Label(root, text="Copyright 2020. All Rights Reserved \nWith Luv From Group 3",
                  font=("Calibri", 8)).pack()
        
            root.mainloop()
        
        
        setup_db()
        window()

Answer 1

If you don't provide all the columns (here you don't provide ID), you should specify column names before values:

INSERT INTO users (column1,column2 ,..) VALUES( value1, value2 ,...);

Answer 2

The loop below is wrong - in this loop you call the INSERT 6 times.

Each time with 1 string (name,email,..).

You need to call the INSERT once and use new_user as second arg of c.execute

new_user = [fname.get(), email.get(), password.get(),
                        cPassword.get(), sex.get(), country.get()]
        
for element in new_user:
     c.execute("INSERT INTO users VALUES (?, ?, ?, ?, ?, ?)", (element))

Answer 3

I actually think instead of looping through the list, just do:

values = fname.get(), email.get(), password.get(),cPassword.get(), sex.get(), country.get()
c.execute("INSERT INTO users(fname,email,password,cPassword,sex,country) VALUES (?, ?, ?, ?, ?, ?)", values)
c.execute('commit')

All im doing here is specifying which column to insert into, and the fact that your table has 7 columns(including the oid) but just 6 values are given might be the issue. And im not looping as i believe @balderman said the issue was calling INSERT 6 times.

Let me know if this fixed the error

Cheers