Formatting sql %like%

Question

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 %% ?

Answer 1

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.

Answer 2

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

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

Answer 3

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

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

Answer 4

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

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