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, Aug 13 '19 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, Aug 13 '19 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, Aug 13 '19 at 13:38
Thanks! Can you use `?` instead of `%s` ? Not sure which library you're using, but that might work. — Alexandre Voyer, Aug 13 '19 at 13:54
are you sure that `execute` takes both 1 arguments and 2 arguments as a function? I would guess not.. — WiseDev, Aug 13 '19 at 13:54
I am using mysql.connector only, and when i used ? it says Not all parameters were used in the SQL statement. — Triath, Aug 14 '19 at 08:30
Yes there is no problem with execute it takes it perfectly well. — Triath, Aug 14 '19 at 08:31

Nickolay · Accepted Answer · 2019-08-15T09:31:02.683

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 '19 at 09:31

answered Aug 14 '19 at 12:49

Nickolay

31,095
13
107
185

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 – Triath Aug 15 '19 at 09:26
Sorry, you have to write it as `execute(sql, (desc, ))` https://stackoverflow.com/questions/44267501/mysql-connector-you-have-an-error-in-your-sql-syntax-near-s-at-line-1 - updated the answer – Nickolay Aug 15 '19 at 09:30

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

1 Answers1