inconsistent results from LIKE query: pyodbc vs. Access

Question

I got a bunch of queries that should be executed in an Access database as a part of my Python script. Unfortunately, queries that used directly in MS Access are giving some records of output, in Python script return nothing (no error either). Connection with database and general syntax should be fine as simple queries (like select one column from table where something) are working just fine. Here is a code with one of these given queries:

import pyodbc

baza = r"C:\base.mdb"
driver = "{Microsoft Access Driver (*.mdb, *.accdb)}"

access_con_string = r"Driver={};Dbq={};".format(driver, baza)
cnn = pyodbc.connect(access_con_string)
db_cursor = cnn.cursor()

expression = """SELECT F_PARCEL.PARCEL_NR, F_PARCEL_LAND_USE.AREA_USE_CD, F_PARCEL_LAND_USE.SOIL_QUALITY_CD, F_ARODES.TEMP_ADRESS_FOREST, F_SUBAREA.AREA_TYPE_CD, F_AROD_LAND_USE.AROD_LAND_USE_AREA, F_PARCEL.COUNTY_CD, F_PARCEL.DISTRICT_CD, F_PARCEL.MUNICIPALITY_CD, F_PARCEL.COMMUNITY_CD, F_SUBAREA.SUB_AREA
FROM F_PARCEL INNER JOIN (F_PARCEL_LAND_USE INNER JOIN ((F_ARODES INNER JOIN F_AROD_LAND_USE ON F_ARODES.ARODES_INT_NUM = F_AROD_LAND_USE.ARODES_INT_NUM) INNER JOIN F_SUBAREA ON F_ARODES.ARODES_INT_NUM = F_SUBAREA.ARODES_INT_NUM) ON (F_PARCEL_LAND_USE.SHAPE_NR = F_AROD_LAND_USE.SHAPE_NR) AND (F_PARCEL_LAND_USE.PARCEL_INT_NUM = F_AROD_LAND_USE.PARCEL_INT_NUM)) ON F_PARCEL.PARCEL_INT_NUM = F_PARCEL_LAND_USE.PARCEL_INT_NUM
WHERE (((F_ARODES.TEMP_ADRESS_FOREST) Like ?) AND ((F_AROD_LAND_USE.AROD_LAND_USE_AREA)<?) AND ((F_ARODES.TEMP_ACT_ADRESS)= ?))
ORDER BY F_PARCEL.PARCEL_NR, F_PARCEL_LAND_USE.SHAPE_NR;"""

rows = db_cursor.execute(expression, ("14-17-2-03*", 0.0049, True)).fetchall()

for row in rows:
    print row

cnn.close()

I know that those queries were generated from query builder in MS Access, so I was wondering that maybe this results in differences, but on the other hand this is still access database.

Anyway it seems, that the problem is in SQL, so I would like to know what elements could possibly result in different output between queries executed directly in MS Access and by pyodbc connection?

Answer 1

You are getting tripped up by the difference in LIKE wildcard characters between queries run in Access itself and queries run from an external application.

When running a query from within Access itself you need to use the asterisk as the wildcard character: "14-17-2-03*".

When running a query from an external application (like your Python app) you need to use the percent sign as the wildcard character: "14-17-2-03%".