Query a mysql table for a dynamic column name and its value in python?

Question

I want to search a mysql table for rows where the specified column has a particular value. For example, given the input string memory=2048 it will search for the rows that have "2048" as the value of memory column and it will print them.

This is code that I have tried but it print outs nothing.

input = input()
tag = input.split("=")
desc = tag[1]
tag = tag[0]

mycursor = mydb.cursor()
sql = "(SELECT * FROM comp WHERE %s LIKE %s)"
val = (tag, desc)
mycursor.execute(sql, val)
res = mycursor.fetchall()
for x in res:
    print(x)

Secondly I tried this code to see where is the problem :

input = input()
tag = input.split("=")
desc = tag[1]
tag = tag[0]

mycursor = mydb.cursor()
sql = "(SELECT * FROM comp WHERE memory LIKE '2048')"
mycursor.execute(sql)
res = mycursor.fetchall()
for x in res:
    print(x)

It gives the desired output. So my problem is when I am trying to get the column name with %s it comes as 'memory' and It couldn't finds it, since the name of the column is memory. Is there a way to get rid of the '' chars ?

confirmation of inputs

first %s takes the value of tag which is memory in my example, the second one takes desc which is 2048. — Triath
– Triath, Commented Aug 13, 2019 at 13:12
can you try it all in one line to see if it works? mycursor.execute("SELECT * FROM comp WHERE %s LIKE %s", (tag, desc)) — Alexandre Voyer
– Alexandre Voyer, Commented Aug 13, 2019 at 13:32
can you confirm that tag = input.split("=") actually outputs an array and tag and desc are strings ? Otherwise %s won't be replaced. — Alexandre Voyer
– Alexandre Voyer, Commented Aug 13, 2019 at 13:38

Nickolay · Accepted Answer · 2019-08-15 09:31:02Z

1

Looking at the mysql.connector's execute() documentation it appears to use %s as placeholders for bind parameters.

So your execute("SELECT * FROM comp WHERE %s LIKE %s", ("memory", "2048")) call ends up running like the following SQL:

SELECT * FROM comp WHERE 'memory' LIKE '2048'

obviously returning 0 rows.

You need to put the literal column name into the query text before invoking execute():

sql = "SELECT * FROM comp WHERE %s LIKE %s" % (tag, "%s")
# =>  "SELECT * FROM comp WHERE memory LIKE %s"
mycursor.execute(sql, (desc, ))

edited Aug 15, 2019 at 9:31

answered Aug 14, 2019 at 12:49

Nickolay

32.5k13 gold badges111 silver badges195 bronze badges

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

2 Comments

Triath Over a year ago

It gives this error: 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

Nickolay Over a year ago

Sorry, you have to write it as execute(sql, (desc, )) stackoverflow.com/questions/44267501/… - updated the answer

Collectives™ on Stack Overflow

Query a mysql table for a dynamic column name and its value in python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related