How do i solve the following sql syntax error

Question

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':Username, :passw, :addrss, :DOB, :emil, :ag)' at line 1

THE CODE:

def submit():
    
    my_cursor = mydb.cursor()

    #INSERT INTO TABLE

    my_cursor.execute("INSERT INTO madhav VALUES (:Username, :passw, :addrss, :DOB, :emil, :ag)",
        {
            'Username': Username.get(),
            'passw'   : passw.get(),
            'addrss'  : addrss.get(),
            'DOB'     : DOB.get(),
            'emil'    : emil.get(),
            'ag'      : ag.get()
        })


    mydb.commit()
    mydb.close()


    # Clear The Text Boxes
    Username.delete(0,END)
    passw.delete(0,END)
    addrss.delete(0,END)
    DOB.delete(0,END)
    emil.delete(0,END)
    ag.delete(0,END)

The above function is used to insert values into a database using a GUI

Answer 1

That's not how you use named parameters in mydb. The correct syntax for such a parameter is %(name)s. So, in your case:

my_cursor.execute("INSERT INTO madhav VALUES (%(Username)s, %(passw)s, %(addrss)s, %(DOB)s, %(emil)s, %(ag)s)",
    {
        'Username': Username.get(),
        'passw'   : passw.get(),
        'addrss'  : addrss.get(),
        'DOB'     : DOB.get(),
        'emil'    : emil.get(),
        'ag'      : ag.get()
    })

Answer 2

The values take type of variable, like string, integar etc. So you code will become

stmt= ( "INSERT INTO madhav (Username, passw, addrss, DOB, emil, ag) " "VALUES (%s, %s, %s, %s, %s, %s)" )

Because all fielda are string type so there is %s otherwise for integar it will be %i Next you can bound this stmt with data to execute.

data = (Username.get(), passw.get(), addrss.get(), DOB.get(),emil.get(),ag.get()) my_cursor.execute(stmt, data)

See these docs for more info https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html