Nsryjdtyk

import mysql.connector



config = {

    'user': 'root',

    'password': '*******',

    'host': '127.0.0.1',

    'database': 'mydb',

    'raise_on_warnings': True

}



cnx = mysql.connector.connect(**config)

cursor = cnx.cursor()



find_user = ("SELECT * FROM HM_Login WHERE Username = '%s' ")

data_Pupil = {

    'Username': "GJM"

}

cursor.execute(find_user, data_Pupil)



lit = cursor.fetchall()

print(lit)

cursor.close()

cnx.close()

I have a database that works and i am having a problem trying to search the database and pull one row of one column when i was inserting into the database the %S worked just fine but now it only works if i have a value inside the the query. this is using the mysql connector for python.

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

I am getting this error which is extremely insightful and not helpful at all if there is anything you can do to help it would mean a lot.

As the error statement says. You have an SQL error. You are trying to input a variable as a positional parameter but you've used a dictionary on a variables 'place'.

Instead you should use %s for variables and tuples with variables and then do the following:

find_user = "SELECT * FROM HM_Login WHERE Username = %s"

data_Pupil = ('GJM',)

cursor.execute(find_user, data_Pupil)

It is also possible to use dictionary - but you shouldn't. Despite that I'm still going to show it here as I had to dig into the explanation to understand why.

find_user = "SELECT * FROM HM_Login WHERE Username = '{Username}'".format(**data_Pupil)

data_Pupil = {

    'Username': "GJM"

    }

The above opens up for sql-injections, as I was told per the comments - and here is why; Say we have a username that is identical to the following:

username = "'MR SQL Injection');DROP TABLE HM_Login;"

That would result in an SQL Query that drops the table.

SELECT * FROM HM_Login WHERE Username = 'MR SQL Injection');DROP TABLE HM_Login;

To avoid sql-injection as above. Use the first solution

Your placeholder syntax is for positional parameters but you've used a dictionary. Replace that with a tuple:

find_user = ("SELECT * FROM HM_Login WHERE Username = %s")

data_pupil = ('GJM',)

cursor.execute(find_user, data_Pupil)