PyODBC query with wildcards

Question

I'm trying to run SQL query with LIKE operator through Python to find any values that have "test" in any position. The problem seems to be with the formatting of what comes after the LIKE operator. There's no error messages, queries are just empty.

The SQL query that I'm trying to mimic is as follows, and works on when executed on Access.

SELECT Areas.ID, Areas.Name
FROM Areas
WHERE Name LIKE '*test*'

Here's how the connection and test data is made. No issue in there.

import pyodbc

# Connect to database
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\Temp\TestDB.accdb;'
    r'Uid=;'
    r'Pwd=;'
    )

# Make cursor
connection = pyodbc.connect(conn_str)
connection.setencoding('utf-8')
cursor = connection.cursor()

# Create test table
cursor.execute("CREATE TABLE Areas (ID integer, Name varchar(255))")
connection.commit()

# Create test data 
cursor.execute("INSERT INTO Areas (ID, Name) VALUES (1,'Example_1');")
cursor.execute("INSERT INTO Areas (ID, Name) VALUES (2,'Example_test_2');")
cursor.execute("INSERT INTO Areas (ID, Name) VALUES (3,'Example_3');")
connection.commit()

# Query filter
Filter = "'*test*'"

Attempt 01

query_01 = cursor.execute(r"""
        SELECT Areas.ID, Areas.Name
        FROM Areas
        WHERE Name LIKE {Filter}
        """.format(Filter=Filter)).fetchall()
for row in query_01:
    print(row)

Attempt 02

query_02 = cursor.execute(r"""
        SELECT Areas.ID, Areas.Name
        FROM Areas
        WHERE Name LIKE ?
        """,("%{}%".format(filter),)).fetchall()
for row in query_02:
    print(row)

Attempt 03, I would like the filter to be variable but even "hard coded" does not work.

query_03 = cursor.execute(r"""
        SELECT Areas.ID, Areas.Name
        FROM Areas
        WHERE Name LIKE '*test*'
        """).fetchall()
for row in query_03:
    print(row)

To be sure something is working, I ran this and it prints the row.

query_04 = cursor.execute(r"""
        SELECT Areas.ID, Areas.Name
        FROM Areas
        WHERE Name = 'Example_test_2'
        """).fetchall()
for row in query_04:
    print(row)

The ideal solution would be that the filter variable could be just a string, without the wildcards. How should I format the filter variable and the query?

Answer 1

For historical reasons, LIKE queries run from within the Access UI default to using * and ? as the wildcard characters. However, external applications using ODBC to query an Access database must use the more common % and _ wildcard characters.

Also, the parameter value must contain the wildcard character(s). (A LIKE condition without wildcard characters is just the same as an = condition.) The parameter placeholder in the SQL command text must be a bare question mark ?.

Finally, do not use connection.setencoding('utf-8'). Access stores text values as Unicode, but it does not use UTF-8 encoding. The default pyodbc encoding (UTF-16) works just fine.

So what you're looking for is

filter = 'test'
sql = "SELECT Areas.ID, Areas.Name FROM Areas WHERE Areas.Name LIKE ?"
param = f'%{filter}%'
rows = cursor.execute(sql, param).fetchall()