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?

Community · Accepted Answer · 2017-05-23 10:27:11Z

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, 2017 at 10:27

CommunityBot

11 silver badge

answered Nov 28, 2015 at 15:53

Kishor Pawar

3,5463 gold badges34 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Spike7 Over a year ago

Still getting syntax errors, shoudi take away the symbol?

Kishor Pawar Over a year ago

@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

Spike7 Over a year ago

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

Daniel Roseman · Accepted Answer · 2015-11-28 15:25:52Z

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, 2015 at 15:25

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

3 Comments

Kishor Pawar Over a year ago

I am afraid this will return a tuple

Spike7 Over a year ago

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"

Makaroni Over a year ago

This works for me, having datetime instead of name (string). Thx.

Spandyie · Accepted Answer · 2018-11-20 23:32:11Z

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, 2018 at 23:32

Spandyie

9652 gold badges11 silver badges25 bronze badges

Comments

Kishor Pawar · Accepted Answer · 2015-11-28 15:11:07Z

-1

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

That's it

answered Nov 28, 2015 at 15:11

Kishor Pawar

3,5463 gold badges34 silver badges64 bronze badges

7 Comments

Spike7 Over a year ago

I get this error ysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'symbol' in 'where clause'

ggle Over a year ago

listen to your error... it suggests there is no such column as symbol.

Kishor Pawar Over a year ago

@Rhys exactly, Spike recheck the columns available

Daniel Roseman Over a year ago

This is not it. Do not recommend this, ever.

Kishor Pawar Over a year ago

@DanielRoseman your explaination will help us understand python better.

|

Collectives™ on Stack Overflow

How to use select statements with variables in mysql in python

4 Answers 4

3 Comments

3 Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related