Python -SQL String Escape Wildcards

I tried to see if this question had been asked, which it probably has, but I couldn’t find an answer. I am querying MS SQL Server from a python script using pyodbc.

I am defining a function to query SQL. The query string has both a ‘%R_%’ which is meant as a wildcard for SQL to interpret, but also a ‘%s’ which is meant to place a variable in python between single quotes. It looks like this:

def numcust(matter):
    QryString = """
    select count(distinct user_id)
    from dbo.tbl_case_details
    where request like '%r_%'
    and project_id in
        (
        select distinct project_id
        from dbo.tbl_projects
        where matter_number = '%s'
        );
    """ % matter
    cursor.execute(QryString)
    row = cursor.fetchone()
    return row[0]

​x
 
def numcust(matter):    QryString = """    select count(distinct user_id)    from dbo.tbl_case_details    where request like '%r_%'    and project_id in        (        select distinct project_id        from dbo.tbl_projects        where matter_number = '%s'        );    """ % matter    cursor.execute(QryString)    row = cursor.fetchone()    return row[0]​

How can I escape the wildcards for r_ so that I can successfully pass it through to SQL?

Thank you kindly.

Answer

Double the % where you want to escape them (i.e. '%%r_%%'). Also, interpolate the string in the cursor.execute function like so:

cursor.execute(QryString, matter)

 
cursor.execute(QryString, matter)​

Advertisement

Answer