Python: pyodbc, Microsoft Access: update dates in table, Too few Parameters

Question

Versions in use: python 3.7.2rc1 and Microsoft Access 2003

I am trying to get a specific record based on an ID and date. If I delete the date parameters it works fine. But using the mydate variable I get the error as shown below.

price_list__only = date_out[0]
#print('pricelist = '+str(price_list__only))
mydate = datetime.date(2018, 12, 1)
#print(mydate)

sql = '''
SELECT * FROM view_price_list WHERE SuppID = ? and "Price List Date" =     ?
'''
parmeters = (price_list__only, mydate)
cursor.execute(sql,parmeters)

Error output: pyodbc.Error: ('07002',[07002], [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3

Answer 1

You need to "bracketise" field names with spaces (or better: avoid these):

SELECT * FROM view_price_list WHERE SuppID = ? and [Price List Date] = ?

or perhaps correct the typos:

SELECT * FROM view_price_list WHERE SuppID = ? and Price_List_Date = ?

Answer 2

Unlike most relational databases following ANSI SQL-1992, MS Access SQL (generally ANSI -89 Level 1 compliant) does not use double quotes for identifiers such as tables or columns. In Access (like Python), double quotes are equivalent to single quotes to enclose string literals.

Therefore, you must escape identifiers with spaces or special characters ?!@#$%^&*()_+, or matching reserved words. Either use square brackets, a feature shared with SQL Server and SQLite:

sql = '''SELECT * FROM view_price_list 
         WHERE SuppID = ? and [Price List Date] = ?
      '''

Or use backticks, a feature shared with MySQL:

sql = '''SELECT * FROM view_price_list 
         WHERE SuppID = ? and `Price List Date` = ?
      '''