How can I fix the 07002 too few parameters in this program?

0
votes

Trying to write a simple program to parse Excel sheets then store the data in a Access database.

I'm not entirely sure what the issue is, I've tried renaming parameters, double checking spelling, etc.

cursor.execute('select * from MSDB')
cursor.execute('''
    INSERT INTO MSDB ([SalesOrder], [SiteNumber], [SiteName], [Customer], [ShipDate])
    VALUES(SO, SNM, Custom, SD, SN)
              ''')
conn.commit()

else:
    continue

This gives me the error:

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

2
pythonsqlms-accesspyodbc
You'll need to concatenate the SQL string to evaluate the variables SO, SNM etc., they won't be evaluated by the SQL engine; or better yet, parameterise the query to avoid SQL injection. - Lee Mac

2 Answers

0
votes

you can use

sql = "INSERT INTO MSDB ([SalesOrder], [SiteNumber], [SiteName], [Customer],ShipDate])
VALUES (?,?,?,?,?)"
cursor.execute(sql,(SO, SNM, Custom, SD, SN))

it will help you

0
votes

I got it to work, for whatever reason I needed to convert ints to floats. Here was the final bit of code that worked for me.

params = (SalesO.value, SiteNum.value, SiteN.value, Cust.value, ShipD.value)
    params = [float(x) if type(x) is int else x for x in params] #Convert all int items to floats
    conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\llippincott\Desktop\MS DB_be.accdb;')
    cursor = conn.cursor()
    cursor.execute('select * from MSDB')
    cursor.execute("INSERT INTO MSDB (SalesOrder, SiteNumber, SiteName, Customer, ShipDate) VALUES (?,?,?,?,?)", (params))
    conn.commit()