How do I pass a variable in an SQL query in python

Question

nameEnt = nameEntered.get()

print(nameEnt)

sql = "SELECT * FROM attendance WHERE name="%s"
val = (nameEnt)
print(mycursor.execute(sql, val))
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

I would like to pass the string in 'nameEnt' into the SQL query using python. I'm currently using the mysql-connector package. The program keeps telling me that my syntax is incorrect. I can execute the query in SQL directly without any problem.

I have also tried

sql = "SELECT * FROM attendance WHERE name= "+nameENt

You have an issue with your `"`... Syntax highlighting will give you a hint. ;) try: `"SELECT * FROM attendance WHERE name=%s"` — Thomas, Aug 11 '21 at 14:35
Have a look at the [official docs](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) — Maurice Meyer, Aug 11 '21 at 14:36
You don't need quotes around `%s` if you're passing a query parameter separately. — khelwood, Aug 11 '21 at 14:38

score 1 · Answer 1 · answered Aug 14 '21 at 09:26

1

you can try:

sql = "SELECT * FROM attendance WHERE name = %s",(nameEnt)

Or:

sql = "SELECT * FROM attendance WHERE name = {}".format(nameEnt)

answered Aug 14 '21 at 09:26

JulianBPL

145
1
9

score 0 · Answer 2 · answered Aug 11 '21 at 14:43

Do not combine SQL strings with data unless you know what you are doing. Doing so is a sure way to get yourself an SQL injection vulnerability.

Your original code was almost correct. First, as the comments noted, you don't need the quotes around %s:

sql = "SELECT * FROM attendance WHERE name=%s"

Then, your second parameter to cursor.execute() is a tuple, but in Python, to make a single-element tuple, wrapping it in brackets isn't enough:

my_element = 12345
not_a_tuple = (my_element)
type(not_a_tuple) == int

real_tuple = (my_element,)  # note the comma at the end
type(real_tuple) == tuple

Applying these to your code, you get:

nameEnt = nameEntered.get()

print(nameEnt)

sql = "SELECT * FROM attendance WHERE name=%s"
val = (nameEnt,)
print(mycursor.execute(sql, val))
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

How do I pass a variable in an SQL query in python

2 Answers2