CODEWITHSUNDEEP
pythonmysql
David542
David542

Reputation: 110312

Formatting sql %like%

I am trying to do the following query:

select count(*) from video where territories like %ZW%

Here is what I currently have, but it's raising an error:

for territory_code in ALL_TERRITORIES:
    sql = "select count(*) from video where territories like %{}%".format(territory_code)
    cursor.execute(sql)

What am I doing wrong here, and how would I properly escale the %% ?

Upvotes: 1

Views: 79

Answers (4)

Mati36
Mati36

Reputation: 1

Maybe you could use the simple quotation marks after the like:

"select count(*) from video where territories like '%{}%'"

Upvotes: 0

mikea80
mikea80

Reputation: 127

you are missing '' single quotes around the %%. Use this instead:

"select count(*) from video where territories like '%{}%'"

Upvotes: 0

Justin O Barber
Justin O Barber

Reputation: 11601

An even better way to do this is as follows:

sql = "select count(*) from video where territories like %s"
cursor.execute(sql, ('%' + territory + '%',))

With this approach, you will be able to parameterize your query without worrying about escapes and, more importantly, without worrying about security vulnerabilities.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108736

They way you're doing this, you need a literal string with single quotes.

 select count(*) from video where territories like '%ZW%'

Upvotes: 1

Related Questions