Possible reasons for inconsistent query results

I have a bunch of queries to be executed against an Access database as part of my Python script. Unfortunately, queries that are used directly in MS Access give some output records, in a Python script it returns nothing (no errors). Database connection and general syntax should be fine as simple queries (like select one column from a table somewhere) work fine. Here is the code for one of these tasks:

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 these queries were generated from the query builder in MS Access, so I was wondering if this might be causing the differences, but on the other hand, it is still an access database.

Anyway, it seems that the problem is in SQL, so I would like to know what elements can lead to different conclusions between queries executed directly in MS Access and the pyopbc connection?

+3


source to share


1 answer


You are running into a LIKE character difference between requests running in access itself and requests running from an external application.

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

.



When you run a query from an external application (for example, your Python applications) you need to use the percent sign as a wildcard character: "14-17-2-03%"

.

+2


source







All Articles