I'm trying to insert values into MySQL table in Python, but I keep getting a error when I try it

Question

I have created a table named 'Patient':

    import mysql.connector as mysql

    db=mysql.connect(host="localhost", user="root", password="xxxx",
                    database='project')

    cursor = db.cursor()


    pat = 'create table Patient(ID char(10) primary key,Token int(10),Name         
    varchar(20),Phone int(10),Email char(20),Age int(3),BG_needed 
    char(3),Quantity char(2),Gender char(1),Date date)'

    cursor.execute(pat)

    sql = 'Insert into 
    Patient(ID,Token,Name,Phone,Email,Age,BG_needed,Quantity,Gender) 
    values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'

    val = ('pat1','2','Aaron','93242995','[email protected]','20','B-','3L','M',                       
    '2022-10-01')
    cursor.execute(sql, val)

    db.commit()

    for x in cursor:
    print(x)

And I'm getting the output as:

DataError: Column count doesn't match value count at row 1

Can you please help me find the error?

I'm sorry if you think I'm asking a silly question, I'm just in 11th grade, and this topic wasn't taught to us. I'm trying to learn this on my own...

Answer 1

There are too many problems in your script. Your number of parameters don't match.

import mysql.connector as mysql

db = mysql.connect(host="localhost", user="root", 
password="xxxx",database='project')

cursor = db.cursor()


pat = 'create table Patient(ID char(10) primary key,Token int(10),Name 
varchar(20),Phone int(10),Email char(20),Age int(3),BG_needed 
char(3),Quantity char(2),Gender char(1),Date date)'

cursor.execute(pat)

sql = 'Insert into 
Patient(ID,Token,Name,Phone,Email,Age,BG_needed,Quantity,Gender,Date) 
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
val = ('pat1','2','Aaron','93242995','[email protected]','20','B- 
','3L','M','2022-10-01')
cursor.execute(sql, val)

db.commit()

for x in cursor:
    print(x)

It was an easy fix. Hope that you find it useful