MySQL and Python Error : While I am trying to insert a row to my table from the Tkinter widget's(Entry, ComboBox, Radio Button), I'm getting an error

0
votes

While I am trying to insert a row to my table from the Tkinter entries widgets (Entry, ComboBox, Radio Button), I'm getting the following errors:

ERROR 1136(21S01): Column count doesn't match value count at row 1.

Column names:

Course, 
U_Id, 
Subject, 
Years, 
Semester, 
Student_Names, 
Roll_No, 
Gender, 
DOB, 
Email, 
Mobile, 
Address, 
Photo

where U_Id is auto increment,

and values: crsVar.get(), sbVar.get(), yrsVar.get(), smVar.get(),nVar.get(), rollVar.get(), genVar.get(), dVar.get(), eVar.get(), mobVar.get(), adVar.get(), rdVar.get()

Please help me out, this is my code

try:
    conn = mysql.connector.connect(host="localhost", username="root", 
    password="Sahil#12", database="attendancesystem")

    c = conn.cursor()
    c.execute('insert into `students_detail` values(crsVar.get(), sbVar.get(),
                        yrsVar.get(), smVar.get(), nVar.get(), rollVar.get(), 
                        genVar.get(), dVar.get(), eVar.get(), mobVar.get(),
                         adVar.get(), rdVar.get()))                          

    conn.commit()
    conn.close()
    messagebox.showinfo("Success", "Students details has been submitted", 
                                  parent=self.master)

except Exception as e:
    messagebox.showerror("Error", f"Due to {str(e)}")
2
pythonmysqltkintermysql-python
You have 13 columns, but only 12 values. Obviously you do not want to specify U_ID since it's an auto-inc column, but that means you have to specify the columns explicitely. – Mike Scotty
first of all you are using ' wrong – Joe Mo
Does this answer your question? How to insert new row to database with AUTO_INCREMENT column without specifying column names? – Cool Cloud
Use ''' if you are splitting a single query into multi-lines – Cool Cloud

2 Answers

0
votes

The issue here is that you have 12 values and 13 columns due to the auto-incremented U_Id. Your best option is to specify the columns of the table manually ie:

c.execute('insert into `students_detail` (Course, Subject, Years, ...)  values(crsVar.get(), sbVar.get(),
                    yrsVar.get(), ...))

(Note you do NOT include the U_Id field in either the columns or values).

Ther are alternatives: How to insert new row to database with AUTO_INCREMENT column without specifying column names? however specifying the column/value pairs is a more robust solution.

0
votes

Try it this way : (use %s to avoid SQL injection)

c.execute("INSERT INTO students_detail "                                      
    "(value1, value2, value3, value4, value5)"           
    "VALUES (%s, %s, %s, %s, %s) ",                                
    (                                                              
        widget[0].get(),                     #
        widget[1].get(),                     #
        widget[2].get(),                     #
        widget[3].get(),                     #
        widget[4].get(),                     #
    )) ## this widget.get() is only an example, you will need to change all these values