CODEWITHSUNDEEP
sqlpython-3.xms-accesspyodbc
Tom Gresavage
Tom Gresavage

Reputation: 41

pyodbc with MS Access expecting too many parameters

I am trying to query an Access DB for results in a certain timeframe. As I am querying, I would like to automagically add a column to the output specifying which week (from a range of weeks) the data belongs to. The automagic column is generated by passing a parameter to the select statement. I keep going back and forth between two errors and I can't figure out why.

ERROR 1) Too few parameters.

ERROR 2) Syntax error in JOIN

My python code:

ERROR 1 Code:

qry = '''
    SELECT [PreQuery].[defect], SUM([PreQuery].[qty_rej] + [PreQuery].[qty_rew]) AS [qty_defect], ? AS [wk_no]
    FROM (
        SELECT [Sort].[Date of Sort] AS [srt_date], [Part].[Part Name] AS [prt_no], [Defect Table].[Defect2] AS [defect], [Defect Table].[Quantity Rejected] AS [qty_rej], [Defect Table].[Quantity Reworked] AS [qty_rew]
        FROM (([Defect Table] LEFT JOIN [Sort] ON [Defect Table].[Sort Number] = [Sort].[Sort Number]) LEFT JOIN [Part] ON [Sort].[Part Number] = [Part].[Part Number])) AS PreQuery LEFT JOIN [Defects] ON [Defects].[Defect Code] = [PreQuery].[Defect2]
    WHERE (([Sort].[Date of Sort] BETWEEN ? AND ?) AND ([Part].[Part Name] LIKE ? & '_TZ______'))
    GROUP BY [defect], [wk_no];'''

Raises the following error:

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8. (-3010) (SQLExecDirectW)')

Removing the aliases from [Sort].[Date of Sort] and [Part].[Part Name] reproduces the same error except the driver expects 6 parameters instead of 8 as before. In either case, there are only four ?s in the whole statement so the DB should be expecting 4 params.

ERROR 2 Code:

qry = '''
    SELECT ? AS [wk_no], [defect], SUM([qty_rej] + [qty_rew]) AS [qty_defect]
    FROM (
        SELECT [Sort].[Date of Sort], [Part].[Part Name], [Defect Table].[Defect2] AS [defect], [Defect Table].[Quantity Rejected] AS [qty_rej], [Defect Table].[Quantity Reworked] AS [qty_rew]
        FROM (([Defect Table] LEFT JOIN [Sort] ON [Defect Table].[Sort Number] = [Sort].[Sort Number])
        LEFT JOIN [Part] ON [Sort].[Part Number] = [Part].[Part Number]))
    LEFT JOIN [Defects] ON [Defects].[Defect Code] = [Defect Table].[Defect2]
    WHERE (([Sort].[Date of Sort] BETWEEN ? AND ?) AND ([Part].[Part Name] LIKE ? & '_TZ______'))
    GROUP BY [defect], [wk_no];'''

This raises:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation. (-3510) (SQLExecDirectW)')

I checked the inner select statement by itself and it executes perfectly fine in either case. Furthermore I couldn't see any obvious syntax errors (and neither could PyCharm checking with MySQL dialect).

Upvotes: 0

Views: 497

Answers (1)

Parfait
Parfait

Reputation: 107767

Overall, there are several issues among both query attempts:

  1. Concatenate the literal '_TZ______' to your last parameter value as the placeholder, ?, must stand on its own.
  2. You need to assign a table alias to derived table, PreQuery, like you did in first query.
  3. The JOIN must be on the new column alias, defect, not Defect2.
  4. Move WHERE clause inside subquery.
  5. Remove the wk_no in GROUP BY as it is not an actual field. Constants do not need GROUP BY.

Consider below syntax adjustment using table aliases for reduced length and readability. Of course without data, I cannot adequately test. Hence, you may need to adjust further:

qry = """
    SELECT ? AS [wk_no], [defect], SUM([qty_rej] + [qty_rew]) AS [qty_defect]
    FROM
      (
        SELECT s.[Date of Sort], p.[Part Name], d.[Defect2] AS [defect], 
               d.[Quantity Rejected] AS [qty_rej], d.[Quantity Reworked] AS [qty_rew]
        FROM (([Defect Table] d LEFT JOIN [Sort] s ON d.[Sort Number] = s.[Sort Number])
               LEFT JOIN [Part] p ON s.[Part Number] = p.[Part Number])
        WHERE ((s.[Date of Sort] BETWEEN ? AND ?) AND (p.[Part Name] LIKE ?))
      ) As PreQuery
    LEFT JOIN [Defects] ON [Defects].[Defect Code] = [PreQuery].[defect]        
    GROUP BY [defect];
"""   

cursor.execute(qry, myparams)  # WHERE LAST PARAM HAS '_TZ______' CONCATENATED TO ORIG VALUE

Upvotes: 1

Related Questions