Mysql not able to insert row in table

Question

I am trying to insert rows into an empty table. I am doing this by mysql statements inside python code. But when I am trying to insert a row I am getting error. Please help.

Note that mostly I am inserting fresh data, so almost always the else part gets executed.

Python code:

def dbsupport_update_table(dbcon, name, exam_report):
    """
    Update current table and modified date in database
    for the name.
    """
    dbcur = dbcon.cursor()

    now = dt.utcnow()

    dbtable = get_latest_table_data()
    dbdict = dbtable.set_index('name').T.to_dict('list')

    if name in dbdict.keys():
        # Modify existing data
        sql=("update student_table set last_updated_date='%s', first_marks=%g, second_marks=%g, third_marks=%g, "
                "fourth_marks=%g where name='%s'" 
                % (now,  exam_report[0],  exam_report[1],  exam_report[2],
                     exam_report[3], name))
    else:
        # Insert fresh row
        column_str = ("name, last_updated_date, first_marks, second_marks, third_marks, fourth_marks")  
        insert_str = (name, 0, exam_report[0], exam_report[1], exam_report[2], exam_report[3])
        sql = ("insert into student_table (%s) values (%s)" % (column_str, insert_str))

    dbcur.execute(sql)
    #dbcur.executemany(sql, daily_data)
    dbcon.commit()

    print("Successfully added data for %s to DB" % (name))

When executing I am getting below error.

File "data_retrieval_new.py", line 96, in dbsupport_update_table
    if name in dbdict.keys():
  File "C:\Users\admin\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
    res = self._query(query)
  File "C:\Users\admin\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
    db.query(q)
  File "C:\Users\admin\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 226, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")

The table is defined as thus:

+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| id                | int(11)       | NO   | PRI | NULL    | auto_increment |
| name              | varchar(20)   | YES  |     | NULL    |                |
| last_updated_date | datetime      | NO   |     | NULL    |                |
| first_marks       | decimal(19,4) | YES  |     | NULL    |                |
| second_marks      | decimal(19,4) | YES  |     | NULL    |                |
| third_marks       | decimal(19,4) | YES  |     | NULL    |                |
| fourth_marks      | decimal(19,4) | YES  |     | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+

Answer 1

"Column count doesn't match value count" occurs when you run an insert statement and the number of columns doesn't match the number of values, for example:

insert into person(name)          values('John',23,'new York')
insert into person(name,age,city) values('John')

Look at the contents of the sql variable you're actually executing and I think you'll pretty quickly see the problem

The next thing you should do, and this is a vital thing you must do forever more in your programming career, is absorb and apply the knowledge at https://bobby-tables.com

If you do that, not only will your problem go away, but your code will be considerably more resistant to hacking

---

Edit:

I'm not a python dev - this is the first python I've ever written. I think, based on the advice from bobby-tables.com it would go like this:

else:
    # Insert fresh row 
    val = (name, 0, exam_report[0], exam_report[1], exam_report[2], exam_report[3])
    sql = ("insert into student_table (name, last_updated_date, first_marks, second_marks, third_marks, fourth_marks) values (%s, %s, %s, %s, %s, %s)")

dbcur.execute(sql, vals)

But based on MySQL parameterized queries I think this might be better:

vals_dictionary = {
    'nam': name,
    'lud': 0,
    'fim': exam_report[0],
    'sem': exam_report[1],
    'thm': exam_report[2],
    'fom': exam_report[3]
}
dbcur.execute ("""
    insert into student_table (name, last_updated_date, first_marks, second_marks, third_marks, fourth_marks) 
    values (%(nam)s, %(lud)s, %(fim)s, %(sem)s, %(thm)s, %(fom)s)        
        """, vals_dictionary)

As noted, I'm not a python dev, so this might need some tweaking