Query Works in Access, not with pyodbc

[Saintroi]

Environment

• Python: 32-bit, 3.6
• pyodbc: 4.0.23
• OS: Windows 10
• DB: Access / SQL
• driver: Timberline Office

Issue

This is my query:

SELECT CURRENT_JCT_TRANSACTION.Job,CURRENT_JCT_TRANSACTION.Cost_Code,CURRENT_JCT_TRANSACTION.Category,CURRENT_JCT_TRANSACTION.Amount,CURRENT_JCT_TRANSACTION.Invoice,CURRENT_JCT_TRANSACTION.Vendor,CURRENT_JCT_TRANSACTION.Description,CURRENT_JCT_TRANSACTION.Transaction_Date,CURRENT_JCT_TRANSACTION.Units,CURRENT_JCT_TRANSACTION.Unit_Cost,CURRENT_JCT_TRANSACTION.Transaction_Type,MASTER_APM_INVOICE.Invoice_File_Links FROM CURRENT_JCT_TRANSACTION LEFT JOIN MASTER_APM_INVOICE
ON CURRENT_JCT_TRANSACTION.Invoice=MASTER_APM_INVOICE.Invoice AND CURRENT_JCT_TRANSACTION.Vendor=MASTER_APM_INVOICE.Vendor WHERE CURRENT_JCT_TRANSACTION.Job='17-025' AND (CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'AP Cost' OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'JC Cost' OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'PR Cost')

Generated with pypika.

This works if I run it directly in Access, pulls all of the data I expect from our Sage Accounting database. When running with cursor.execute, I get nothing back.

Here's some other pieces of the code i'm testing with:

def __init__(self):
    print("Connecting to DB...")
    self.cnxn = pyodbc.connect(DSN = 'sageData', UID = 'censored', PWD= 'censored', autocommit=True)
    print("Connected")
    self.cursor = self.cnxn.cursor()

def fetch(self, query):
    cursor = self.cursor
    cursor.execute(query) #"select Amount, Description from MASTER_APM_INVOICE"
    columns = [column[0] for column in cursor.description]
    print(cursor.rowcount)
    data = []
    row = cursor.fetchone()
    while row:
      print("exists")
      data.append(dict(zip(columns, row)))
      row = cursor.fetchone()
    print(data)
    return data

Maybe this is me being dumb but I can run simpler queries in this fashion so I don't understand why this one isn't working.

[gordthompson]

When you "run it directly in Access" are you using a pass-through query or a regular select query with ODBC linked tables?

[Saintroi]

The latter. I add the tables from an external data source->odbc data source and then just create a query and paste the query I generated with pypika into access and run.

[gordthompson]

Is your copy of Access the 32-bit version (same "bitness" as the Python you are using)?

[Saintroi]

Yes! The ODBC driver i'm using is only available in 32-bit. I attempted to install and use turbodbc but it only supports 64-bit and thus can't see my DSN.

[gordthompson]

A hunch: Try executing

pyodbc.pooling = False

before calling pyodbc.connect and see if that helps.

[gordthompson]

Another test for diagnostic purposes: Save the following code as "sagetest.vbs" ...

Option Explicit
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=sageData;UID=censored;PWD=censored"
Dim sql
sql = _
          "SELECT CURRENT_JCT_TRANSACTION.Job, " _
        & "    CURRENT_JCT_TRANSACTION.Cost_Code, " _
        & "    CURRENT_JCT_TRANSACTION.Category, " _
        & "    CURRENT_JCT_TRANSACTION.Amount, " _
        & "    CURRENT_JCT_TRANSACTION.Invoice, " _
        & "    CURRENT_JCT_TRANSACTION.Vendor, " _
        & "    CURRENT_JCT_TRANSACTION.Description, " _
        & "    CURRENT_JCT_TRANSACTION.Transaction_Date, " _
        & "    CURRENT_JCT_TRANSACTION.Units, " _
        & "    CURRENT_JCT_TRANSACTION.Unit_Cost, " _
        & "    CURRENT_JCT_TRANSACTION.Transaction_Type, " _
        & "    MASTER_APM_INVOICE.Invoice_File_Links " _
        & "FROM CURRENT_JCT_TRANSACTION " _
        & "LEFT JOIN MASTER_APM_INVOICE ON CURRENT_JCT_TRANSACTION.Invoice = MASTER_APM_INVOICE.Invoice " _
        & "    AND CURRENT_JCT_TRANSACTION.Vendor = MASTER_APM_INVOICE.Vendor " _
        & "WHERE CURRENT_JCT_TRANSACTION.Job = '17-025' " _
        & "    AND ( " _
        & "        CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'AP Cost' " _
        & "        OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'JC Cost' " _
        & "        OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'PR Cost' " _
        & "        ) "
Dim rst
Set rst = CreateObject("ADODB.Recordset")
rst.Open sql, con
Dim n
n = 0
Do Until rst.EOF
    n = n + 1
    rst.MoveNext
Loop
rst.Close
con.Close
Wscript.Echo n & " record(s) found"

... and then run it from a command prompt using the 32-bit version of CSCRIPT.EXE

C:\Users\Gord>\Windows\SysWOW64\cscript.exe sagetest.vbs

That will show us whether a direct ODBC connection works properly without using pyodbc.

[Saintroi]

@gordthompson Thanks for getting back to me, just got a chance to test this. The pooling option didn't change anything, I ran the test and got 0 records found so I suppose that means it isn't a pyodbc problem.

Just seems very odd that I can run it in Access. I'll test with that file and see if I can find where my query breaks.

[v-chojas]

Try to collect an ODBC trace, once with it working and once without.

[gordthompson]

@Saintroi re: "seems very odd that I can run it in Access"

That's because an Access query with ODBC linked tables is treated just like a query with native Access tables in that the query is processed by Microsoft's ACE (formerly "Jet") database engine. ACE parses the query, generates the execution plan, and then retrieves the relevant information from each individual table. By contrast, your current pyodbc code simply passes the whole query verbatim (including JOIN clauses, etc.) and lets the ODBC target database parse, optimize, and execute the query, and then pull the results from the individual tables as needed.

So, you seem to have uncovered a deficiency in the Timberline Office ODBC driver, and you might want to report it to them. In the meantime, however, you might still be able to use Python to query the data by using an Access database (with ODBC linked tables) as an intermediary. Your pyodbc code would need to use pyodbc.pooling = False and your connection string (or DSN) would need to use the Microsoft Access Driver.

[Saintroi]

@v-chojas If you can explain how to do that, I can!

@gordthompson That makes a lot of sense, I will attempt to contact them and let them know. I may try what you have suggested.

I am being pressed for time on this project, and as a result of my tests I have discovered that the last part of the where clause

AND (CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'AP Cost' OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'JC Cost' OR CURRENT_JCT_TRANSACTION.Transaction_Type LIKE 'PR Cost')

Is the problem section, without that I seem to be getting data just fine. So I can read in all of that data and filter out that field manually in Python for now, as a temporary fix.

I really appreciate all of your help, I will continue to iterate on this over the next week and report back, that is if you don't mind leaving this issue open for a bit.

[gordthompson]

A LIKE condition without wildcards is equivalent to an = condition so you might want to see if hacking the conditions to be

... AND (CURRENT_JCT_TRANSACTION.Transaction_Type = 'AP Cost' OR ...

does the trick.

[Saintroi]

I did try that before, but just to be sure I tried it again and actually managed to make it work! I think when I tried it before I was still using 'AP Cost' with a capital C when the actual data is 'AP cost' lower-case.

The LIKE statement doesn't work either way, although in Access it doesn't seem to care if it's lower or upper case. My initial attempt was to use LIKE 'cost' which worked in Access but not in code.

The problem now being that I'm not sure that AP JC and PR are realistically the only cost values I'm going to need, which is why I was initially trying to use cost so if it ends up being that I need more values than those 3 I may have to go back to what I did yesterday, which was filtering out anything that didn't contain 'cost' when parsing the data afterwards.

Either way I think that's my problem to figure out from here on, thanks so much @gordthompson you're a saint and a scholar.

[natajacks]

When you "run it directly in Access" are you using a pass-through query or a regular select query with ODBC linked tables?

Hi @gordthompson , is it possible to run a pass-through query with its own ODBC connection established in Access from pyodbc?

It's clunky, but right now Access is my only means of connecting to multiple data sources, and I'd like to run my queries from Python.

In other words, I would like to use Python to run pass-through queries in Access that have various ODBC connections.

Thank you

[gordthompson]

@natajacks - I just tried it and it worked for me.

In Access:

Set cdb = CurrentDb
Set q = cdb.QueryDefs("my_ptq")
?q.SQL
SELECT @@VERSION AS ver
?q.Connect
ODBC;DSN=mssql_199;UID=scott;PWD=tiger^5HHH;Trusted_Connection=No;DATABASE=test;UseFMTONLY=Yes;

In Python:

import pyodbc

cnxn = pyodbc.connect(
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"Dbq=C:\Users\Public\Database1.accdb;")
crsr = cnxn.cursor()
result = crsr.execute("SELECT ver FROM my_ptq").fetchval()
print(result)
"""
Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
    Mar  4 2020 00:59:26 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
"""