Python -SQL String Escape Wildcards

Question

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]

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

Thank you kindly.

agelber · Accepted Answer · 2013-06-26 21:21:09Z

2

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)

answered Jun 26, 2013 at 21:21

agelber

3041 silver badge4 bronze badges

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

Comments

Joran Beasley · Accepted Answer · 2013-06-26 21:21:10Z

1

"%% %s"%("Hello",) two % signs should become one after applying the %s stuff ...

but really you should be using the built in query stuff stuff

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'
    );
"""
cursor.execute(QryString,matter)

although you still may need to do %% for a literal %

answered Jun 26, 2013 at 21:21

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

Collectives™ on Stack Overflow

Python -SQL String Escape Wildcards

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related