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.

Daniel Roseman · Accepted Answer · 2015-02-13 09:53:38Z

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",))

answered Feb 13, 2015 at 9:53

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

Comments

mhawke · Accepted Answer · 2015-02-13 10:08:30Z

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

Karel Kubat · Accepted Answer · 2015-02-13 09:43:23Z

0

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%",))

answered Feb 13, 2015 at 9:43

Karel Kubat

1,67511 silver badges12 bronze badges

1 Comment

Jerry YY Rain Over a year ago

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

Collectives™ on Stack Overflow

python mysqldb query with where

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related