CODEWITHSUNDEEP
pythonmysqlsql
David542
David542

Reputation: 110093

How to properly escape %s in sql query

I have the following sql:

sql = '''SELECT DISTINCT artwork_apple_url FROM main_catalog 
         WHERE (artwork_url IS NULL OR artwork_url NOT LIKE '%s3-%')
           UNION ALL
         SELECT DISTINCT artwork_apple_url FROM main_collectioninstance 
         WHERE (artwork_url IS NULL OR artwork_url NOT LIKE '%s3-%')
      '''
cursor.execute(sql)

This gives me a formatting error -- How would I get the LIKE %s3-%' in my sql (s3 is part of an Amazon url).

Upvotes: 0

Views: 139

Answers (2)

unutbu
unutbu

Reputation: 879113

If you use parametrized SQL, the quoting of the arguments will be done by the DB adapter for you. It's easier and helps prevent SQL injection. Note that the appropriate placemarker (e.g. the %s) depends on the DB adapter you are using. %s is appropriate for MySQLdb, but ? is the placemarker symbol used by oursql, for example.

sql = '''SELECT DISTINCT artwork_apple_url FROM main_catalog 
         WHERE (artwork_url IS NULL OR artwork_url NOT LIKE %s)
           UNION ALL
         SELECT DISTINCT artwork_apple_url FROM main_collectioninstance 
         WHERE (artwork_url IS NULL OR artwork_url NOT LIKE %s)
      '''
cursor.execute(sql, ['%s3-%']*2)

Upvotes: 3

Sean Azlin
Sean Azlin

Reputation: 916

Place a backward slash before each percentage symbol you wish to escape and insert this function on the same line: ESCAPE '\'

From here: http://www.ehow.com/how_8601061_escape-special-character-sql.html?dmsp=manual

Upvotes: 0

Related Questions