python mysql error " mysql.connector.errors.ProgrammingError: Parameters for query must be list-CodePudding

I am so confused in regards to this issue, as I have followed recommendations from other posts yet am unable to get my program to work!

        sql = "SELECT UserID FROM userdetails WHERE Username = %(value)s 
        AND Password = %(value2)s "
        params = {'value': username, 'value2': password}

        mycursor.executemany(sql, (params))

        mydb.commit()

I believe the error to be related to this section of the code yet I am unable to see any mistakes to fix in order to make the program work again!

Any help is very appreciated, thank you in advance :)

CodePudding user response：

You shouldn´t use the executemany because it is used to execute the same statement (with different parameters) multiple times and usually used for INSERTs or UPDATEs, not for SELECTs. You need to use the execute only:

 sql = "SELECT UserID FROM userdetails WHERE Username = %(value)s 
        AND Password = %(value2)s "
        params = {'value': username, 'value2': password}

 mycursor.execute(sql, (params))    
 mydb.commit()

Also, after a SELECT statement, you don´t need to do a commit (there is nothing to commit), but you will need to fetch the results of your query, so it will look like this:

 sql = "SELECT UserID FROM userdetails WHERE Username = %(value)s 
        AND Password = %(value2)s "
        params = {'value': username, 'value2': password}

 mycursor.execute(sql, (params))    
 data = mycursor.fetchall()

CodePudding user response：

you are probably right, but without a working example all we can do is speculate, i think the issue is your parameters are not passed as a tuple but rather as a dictionary, try this instead:

    sql = "SELECT UserID FROM userdetails WHERE Username = %s 
           AND Password = %s "
    params = (username, password)

    mycursor.executemany(sql, params)