mr cul mr cul - 1 year ago 71
SQL Question

Why is this Python error code: Too Few parameters. Expected1. (-3010) occurring when using pyodbc?

I am writing a python script that executes some SQL code however whenever I call the function containing this SQL it gives me the error:


File "Z:/ ... /Script.py", line 40, in Function

cnxn.execute("""INSERT INTO Table1 VALUES (007, Bond);""")

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


I have researched this problem and found other posts with the same error but I can't seem to apply the answers given to my code. For example:
facing Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')" error in python program

import pyodbc
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=Z:\ ... \DB.accdb;'
)
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
cnxn.execute("""INSERT INTO Table1 VALUES (007, Bond);""")


I originally had only single quotes and tried what Navnath said in his answer and use triple quotes but that changed nothing. I believe that my SQL syntax is correct so I don't understand why this approach isn't working and why this error is coming up. Any help would be greatly appreciated!

Answer Source

Your problem seems to be that you're running the .execute() method from the connection rather than from the cursor you have opened. Also, it's always better to be explicit and use bound variables. Try this?

import pyodbc
conn_str = (
        r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
        r'DBQ=Z:\ ... \DB.accdb;'
        )
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()

crsr.execute(
    """
    INSERT INTO Table1 (agent_number, agent_last_name) VALUES (?, ?)
    """,
    ('007', 'Bond')
)

Depending on your connection, you may also need to do a .commit() afterwards, since you're inserting, updating, or deleting data. Good luck!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download