python mysqldb query with where

Question

I use MySQLDB to query some data from database, when use like in sql, I am confused about sql sentence.

As I use like, so I construct below sql which can get correct result.

cur.execute("SELECT a FROM table WHERE b like %s limit 0,10", ("%"+"ccc"+"%",))

Now I want to make column b as variable as below. it will get none

cur.execute("SELECT a FROM table WHERE %s like %s limit 0,10", ("b", "%"+"ccc"+"%"))

I searached many website but not get result. I am a bit dizzy.

Answer 1

In the db-api, parameters are for values only, not for columns or other parts of the query. You'll need to insert that using normal string substitution.

column = 'b'
query = "SELECT a FROM table WHERE {} like %s limit 0,10".format(column)
cur.execute(query, ("%"+"ccc"+"%",))

You could make this a bit nicer by using format in the parameters too:

cur.execute(query, ("%{}%".format("ccc",))

Answer 2

The reason that the second query does not work is that the query that results from the substitution in the parameterised query looks like this:

select a from table where 'b' like '%ccc%' limit 0,10

'b' does not refer to a table, but to the static string 'b'. If you instead passed the string abcccba into the query you'd get a query that selects all rows:

cur.execute("SELECT a FROM table WHERE %s like %s limit 0,10", ("abcccba", "%"+"ccc"+"%"))

generates query:

SELECT a FROM table WHERE 'abcccba' like '%ccc%' limit 0,10

From this you should now be able to see why the second query returns an empty result set: the string b is not like %ccc%, so no rows will be returned.

Therefore you can not set values for table or column names using parameterised queries, you must use normal Python string subtitution:

cur.execute("SELECT a FROM table WHERE {} like %s limit 0,10".format('b'), ("abcccba", "%"+"ccc"+"%"))

which will generate and execute the query:

SELECT a FROM table WHERE b like '%ccc%' limit 0,10

Answer 3

You probably need to rewrite your variable substitution from

cur.execute("SELECT a FROM table WHERE b like %s limit 0,10", ("%"+"ccc"+"%"))

to

cur.execute("SELECT a FROM table WHERE b like %s limit 0,10", ("%"+"ccc"+"%",))

Note the trailing comma which adds a last empty element, which makes sure the tuple that states variables is longer than 1 element. In this example the string concatenation isn't even necessary, this code says:

cur.execute("SELECT a FROM table WHERE b like %s limit 0,10", ("%ccc%",))

• Jerry YY Rain: Thanks for remind, actually, I have a typo in post, I will update it. it is not related with trailing comma.