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

show the "CREATE TABLE" SQL and explain what is the data type that is returned from `Username.get()` etc. — balderman, Dec 19 '20 at 19:18
...as an aside, why are you leaving letters out? `passw` is no easier to type than `password`, and the latter is _much_ clearer. Ditto `emil` vs `email`. In fact, the full words are probably _easier_ to type since they are real words that you may have muscle memory for. Clarity is important. Saving a byte here or there in your source code provide no benefits whatsoever. — ChrisGPT was on strike, Dec 19 '20 at 19:20
my_cursor.execute("CREATE TABLE madhav (name VARCHAR(255), password VARCHAR(255), address VARCHAR(255), DT DATE , email VARCHAR(255), age INTEGER(10), user_id INTEGER AUTO_INCREMENT Primary key)") this is the create table sql — tyzion, Dec 19 '20 at 19:25

Mureinik · Answer 1 · 2020-12-19T19:41:49.507

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()
    })

edited Dec 19 '20 at 19:41

answered Dec 19 '20 at 19:20

Mureinik

297,002
52
306
350

thank you for the code but another error popped up saying mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '' at line 1 .........im pretty new to this so sorry if the error/doubt is stupid – tyzion Dec 19 '20 at 19:41
@tyzion I missed the closing `)` at the end of the `values` clause, my apologies. Edited and fixed. – Mureinik Dec 19 '20 at 19:42
@Mureinik Why the `%` and the `s` ..If you could add that in the answer, it would help a lot passers-by like me.. Thanks. – Abhishek Rai Dec 19 '20 at 19:48
@AbhishekRai As I noted, that's the syntax mydb uses for named parameters. Not quite sure how I can clarify this further... – Mureinik Dec 19 '20 at 19:49
1

@Mureinik Well, This comment of yours will help passers-by. :) – Abhishek Rai Dec 19 '20 at 19:50

Hamza Mughal · Answer 2 · 2020-12-19T19:37:53.067

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

How do i solve the following sql syntax error

2 Answers2