Working with dates in Access using pyodbc giving "Too few parameters" error
Question: I am using Python with a pyodbc import.

I am using Microsoft Office 2013 64bit.

I am attempting to query an accdb database to select distinct dates within a
range and assign them to a cursor so I can then append them to a list.

My Access database has a table named Closing_prices, and a column named Date_,
which has the data type "Date/Time".

My code is as follows:

    
    
    cursor=conx.cursor()
    query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011' and Date_ < '30/04/2014'"
    cursor.execute(query)
    dates=list()
    for date in cursor:
       dates.append(date[0])
    

However I am receiving the error message:

    
    
    Traceback (most recent call last):
      File "C:/Users/Stuart/PycharmProjects/untitled/Apache - Copy.py", line 20, in <module>
    cursor.execute(query)
    pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')
    

As Date_ is a datetime, I have also tried:

    
    
    query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011 00:00:00' and Date_ < '30/04/2014 00:00:00'"
    

When I run:

    
    
    cursor = conx.cursor()
    query="select Date_ FROM Closing_prices"
    cursor.execute(query)
    
    for row in cursor:
        print row
    
    print type(row[0])
    

I get the following output as an example:

    
    
    (datetime.datetime(2014, 3, 24, 0, 0), )
    (datetime.datetime(2014, 3, 25, 0, 0), )
    (datetime.datetime(2014, 3, 26, 0, 0), )
    (datetime.datetime(2014, 3, 27, 0, 0), )
    

I am relatively new to Python and even newer to SQL queries, so could someone
please point out where I am going wrong, and perhaps how I can change my code
to help me append the distinct dates into a list as desired.

Many thanks.


Answer: To

  1. save yourself the hassle of finding the applicable date delimiter, and 
  2. promote good coding practice 

you should simply use a _parameterized query_ like this:

    
    
    db = pyodbc.connect(connStr)
    crsr = db.cursor()
    sql = """
    SELECT DISTINCT Date_ FROM Closing_prices WHERE Date_ >= ? AND Date_ < ?
    """
    params = (datetime.date(2011, 8, 10), datetime.date(2014, 4, 30))
    crsr.execute(sql, params)
    

