Python: Reading an Access database

Question

This Python script has worked beautifully until today. I recently added a column to the table listed in the FROM clause. However, it is not the field listed in the error message.

I have also tried adding pyodbc.pooling = False to the code but that changed nothing.

I am at a loss and any help would be greatly appreciated.

# Load the needed packages
import pyodbc
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns

# Define the connection parameters for MS Access
myDataSources = pyodbc.dataSources()
access_driver = myDataSources['MS Access Database']
file = '\Datasets\BookData.accdb'

# Connect to MS Access
cnxn = pyodbc.connect(Driver = access_driver, DBQ = file)

# Create a query which pulls in the correct data from MS Access
query = pd.read_sql_query('''SELECT * FROM table''', cnxn)

# Close the connection to the MS Access database
cnxn.close()

Error Message

DatabaseError: Execution failed on sql 'SELECT * FROM table': ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'different_table.[field] could refer to more than one table listed in the FROM clause of your SQL statement. (-3007) (SQLExecDirectW)")

What is the data field type you added? Is it a multi-value field, an attachment type, etc.? — Parfait
– Parfait, Commented Feb 13, 2020 at 21:40
Parfait, it was just a simple iif statement in the MS Access query that multiplied a qty by 2 if a certain requirement was met. I have since deleted the field and I am still getting the same error when trying to load in Python. — 007Dawg
– 007Dawg, Commented Feb 13, 2020 at 21:47
Parfait, I completed the compact & repair as you suggested. unfortunately, I still get the same error message when running the python script. — 007Dawg
– 007Dawg, Commented Feb 14, 2020 at 13:38
Is table in FROM actually a table or stored query? Try to explicitly specify columns and not use * in SELECT. — Parfait
– Parfait, Commented Feb 14, 2020 at 14:16

Parfait · Accepted Answer · 2020-02-15 17:37:12Z

As discussed, likely table in SQL query is not actual table but a stored query or view (as used in other databases) which is queryable like a table. Access raises an error because somewhere in query you reference a duplicate field without aliasing table. For example, Customers and Orders tables can have a field named CustomerID and you run this form:

SELECT CustomerID, ...
FROM Customers c
INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID

Or even ran this query:

SELECT *
FROM Customers c
INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID

Then in Python you call this form which raises the error due to multiple sources for CustomerID:

SELECT * FROM myStoredQuery

Why did this error raise now and not before? Because you may have added same named columns in underlying tables and then used * in SELECT clause of top query. So any new column added to any referenced table is pulled into final query.

To resolve, consider aliasing any potential name collisions. And as advised in most applications running SQL (beyond Python):

Always explicitly identify columns in SELECT clause and do not abbreviate with *.
Always period qualify the table sources of columns when more than one table is referenced in query (i.e., c.CustomerID or o.CustomerID) such as with joins.

See adjusted SQL:

SELECT c.CustomerID AS Customer_CustomerID, 
       o.CustomerID AS Order_CustomerID, 
       ...
FROM Customers c
INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID

While it seems tedious to write out every column, doing so helps in maintainability as you control and clearly see intended output and account for restructured changes like new, dropped, duplicate, or special named columns. Maybe too you do not need all columns in Pandas and so can omit them in SELECT.

Finally, remember SQL is a declarative, special purpose language designed to send explicit commands to database engine in readable form for user. The engine uses different code to handle the requests. For Access' Jet/ACE Engine being Window .dlls this may be C or C++.

Collectives™ on Stack Overflow

Python: Reading an Access database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related