How to use select statements with variables in mysql in python

Question

I want to use a select statement with a variable within the where clause. Ive done resarch on this looking at How to use variables in SQL statement in Python? and Inserting Variables MySQL Using Python, Not Working. Ive tried to implement the solutions provided but its not working. Error code

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

Heres the code:

name = input("Write your name")
mycursor.execute("SELECT name FROM workers WHERE symbol=?", name)

What am i doing wrong?

score 3 · Accepted Answer · edited May 23 '17 at 10:27

3

Okay Spike7 this is exactly what is ideal,

mycursor.execute("SELECT name FROM workers WHERE symbol=%s", (name,))

or

mycursor.execute("SELECT name FROM workers WHERE symbol=?", (name,))

The accepted answer at the first link you given explain evertything.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 28 '15 at 15:53

Kishor Pawar

3,386
3
28
61

Still getting syntax errors, shoudi take away the symbol? – Spike7 Nov 28 '15 at 16:12
@Spike7 the query you have written mean select all the names from workers table if the value in symbol column matches the value of the name variable – Kishor Pawar Nov 28 '15 at 16:39
Yeah it was my fault. When i was looking at the questions it said symbol and i thought it was some mysql language. i changed it to name and it worked fine – Spike7 Nov 28 '15 at 17:06

score 2 · Answer 2 · answered Nov 28 '15 at 15:25

2

The MySQL libraries use %s as the placeholder.

mycursor.execute("SELECT name FROM workers WHERE symbol=%s", name)

Despite the similarity, this isn't string substitution.

answered Nov 28 '15 at 15:25

Daniel Roseman

588,541
66
880
895

I am afraid this will return a tuple – Kishor Pawar Nov 28 '15 at 15:38
Thanks but Im getting a syntax error "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 '%s' at line 1" – Spike7 Nov 28 '15 at 15:38
This works for me, having `datetime` instead of `name (string)`. Thx. – Makaroni Nov 19 '20 at 09:29

score 2 · Answer 3 · answered Nov 20 '18 at 23:32

2

You could use

mycursor.execute("SELECT name FROM workers WHERE symbol={}".format(name))

This will only work as long as your variable name is not string

answered Nov 20 '18 at 23:32

Spandyie

914
2
11
23

score -1 · Answer 4 · answered Nov 28 '15 at 15:11

-1

mycursor.execute("SELECT name FROM workers WHERE symbol=%s"% name)

That's it

answered Nov 28 '15 at 15:11

Kishor Pawar

3,386
3
28
61

I get this error ysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'symbol' in 'where clause' – Spike7 Nov 28 '15 at 15:19
1

listen to your error... it suggests there is no such column as symbol. – ggle Nov 28 '15 at 15:21
1

@Rhys exactly, Spike recheck the columns available – Kishor Pawar Nov 28 '15 at 15:21
1

This is **not** it. Do not recommend this, ever. – Daniel Roseman Nov 28 '15 at 15:24
@DanielRoseman your explaination will help us understand python better. – Kishor Pawar Nov 28 '15 at 15:33
@KishorPawar @DanielRoseman, I believe Dan's point is that using `%s` instead of `?` means you have no protection against SQL Injection. – ggle Nov 28 '15 at 18:34
@Rhys oh...with ` %s` this any string can be replaced. – Kishor Pawar Nov 30 '15 at 05:40

How to use select statements with variables in mysql in python

4 Answers4