PypyODBC with parameters: [ODBC Microsoft Access Driver] Too few parameters. Expected 4

Question

I'm using pypyodbc to select data from an access database. I am using the following query with three parameters which have been specified.

I've tried a few varieties, but to no avail. I don't see anything wrong with my syntax.

SELECT [Date], [Time], [uSec], [threeR], [twoCV] 
FROM [table_a] 
WHERE (Date = ? AND Time > ?) 
OR (Date > ?)

Parameters are of the following types:

[datetime.date, datetime.time, datetime. date]

Which, when printed:

1900-09-16 ,  00:00:00, 1900-09-16

pypyodbc.DatabaseError: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.')

#-- Begin Python code sample
#-- Checks the DB file and retrieves data
def pullData(self):

    #-- Connect  to Access
    con = pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=F:/database.mdb')
    cur = con.cursor()

    #-- Get column list
    columnListODBC = '[thisDate], [thisTime]'
    for y in myTable.getColumns():
        columnListODBC = columnListODBC + ', [' + y + "]"

    #-- See footnote 1
    print(columnListODBC)

    #-- Get the most recent SQL entry
    for row in curSQL.execute('SELECT MAX(Datetime) FROM [' + _.getName() + ']'):
        xDateTime = datetime.datetime.strptime(row[0], "%Y-%d-%m %H:%M:%S")
        day = xDateTime.date() # Get only the DATE of the most recent entry
        time = xDateTime.time() # Get only the TIME of the most recent entry                

    #-- Pull all ODBC data
    queryString = 'SELECT ' + columnListODBC + ' FROM [' + _.getName() + '] WHERE (thisDate = ? AND thisTime > ?) OR (thisDate > ?)'

    #-- See footnote 2
    print(queryString, ", ", day, ", ", time)
    cur.execute(queryString, [day,time,day])

Print 1: [thisDate], [thisTime], [uSec], [threeR], [twoCV]

Print 2: SELECT [thisDate], [thisTime], [uSec], [threeR], [twoCV] FROM [table_a] WHERE (thisDate = ? AND thisTime > ?) OR (thisDate > ?) , 1900-09-16 , 00:00:00

Edit: While toying around it seems to successfully execute when I remove one of the columns. Although both columns exist in the source table. This doesn't answer the question as to why the original query does not execute.

SELECT [Date], [Time], [uSec], [twoCV] 
FROM [table_a] 
WHERE (Date = ? AND Time > ?) 
OR (Date > ?)

Edit 2: Changing the name of the Date and Time columns has not made a difference. The following still gives the error:

SELECT [thisDate], [thisTime], [uSec], [threeR], [twoCV] 
FROM [table_a] 
WHERE ([thisDate] = ? AND [thisTime] > ?) 
OR ([thisDate] > ?)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5.

Edit 3: Here is the design view of the table it is pulling from.

Possible duplicate of [Inserting values into a Access 2003 database from a Python application using pyodbc](http://stackoverflow.com/questions/25168610/inserting-values-into-a-access-2003-database-from-a-python-application-using-pyo) — Bryan, Oct 12 '15 at 15:01
Posting the python code that is generating this error would be helpful. — Bryan, Oct 13 '15 at 13:33

score 2 · Answer 1 · answered Nov 01 '15 at 12:38

Too supplement Bryan Eargle's answer: I encountered this just now. It simply turned out that a column name was wrong.

I get the following error when i purposely use two wrong column names:

Traceback (most recent call last):
  File "C:\...\some_code.py", line 74, in <module>
    table_headers, table_data = fetch_relations()
  File "C:\...\some_code.py", line 27, in fetch_relations
    cur.execute(sql);
  File "C:\Python34\lib\site-packages\pypyodbc.py", line 1605, in execute
    self.execdirect(query_string)
  File "C:\Python34\lib\site-packages\pypyodbc.py", line 1631, in execdirect
    check_success(self, ret)
  File "C:\Python34\lib\site-packages\pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python34\lib\site-packages\pypyodbc.py", line 966, in ctrl_err
    raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.')

Notice in the last line, Expected 2. aligns with the number of problematic columns. For me, the problem goes away when column names are correct.

score 1 · Answer 2 · answered Oct 12 '15 at 15:01

1

Date and Time are reserved words in Access, ensure the reserved words are escaped wherever used in the query:

SELECT [Date], [Time], [uSec], [twoCV] 
FROM [table_a] WHERE ([Date] = ? AND [Time] > ?) 
OR ([Date] > ?)

answered Oct 12 '15 at 15:01

Bryan

17,112
7
57
80

Thought this was the solution, however after changing the columns of both Date and Time (see edit) I'm still getting an error. However, it is now expecting 5 parameters rather than 4 (?) – cjhines Oct 13 '15 at 13:31

score 1 · Answer 3 · answered Nov 01 '15 at 15:59

The error derives from the first cursor query, since as you show with table design view (unless there are more fields your screenshot cuts off) there is no such column called [Datetime] in table_a:

for row in curSQL.execute('SELECT MAX(Datetime) FROM [' + _.getName() + ']'):

Consider changing the field to reflect new column name. Additionally, to use strptime() the original variable row[0] must be a string, so you may receive a Python TypeError. Jet/ACE datetime fields will import into Python as datetime fields already so no need of conversion:

for row in curSQL.execute('SELECT MAX(thisDate) FROM [' + _.getName() + ']'):
    xDateTime = row[0]
    day = xDateTime.date() 
    time = xDateTime.time()

PypyODBC with parameters: [ODBC Microsoft Access Driver] Too few parameters. Expected 4

3 Answers3