how to insert values into user inputted column using pyodbc

Question

im trying to insert values into a column that a user has asked to be changed which is called surnamereq and the user change which is called name1. money_spent is the name of the table and first_name is the column that the user is changing the value of.

This is how it should be written in SQL(i think):

INSERT INTO money_spent(first_name) WHERE last_name = surnamereq VALUES(name1)

This is what ive got in python:

cursor.execute("INSERT INTO money_spent(first_name) WHERE last_name = ?, surnamereq VALUES(name1)")

Thanks

• Gord Thompson: If you are updating an existing row in the table then you need to use an UPDATE statement, not an INSERT statement. (INSERT is for adding a new row.)
• Ryan pietras: I have this now:
• Ryan pietras: cursor.execute(""" UPDATE money_spent SET first_name = ?, fname WHERE last_name = ?, surnamereq""")
• Ryan pietras: And that gives me a pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect (17) (SQLExecDirectW)')
• Ryan pietras: It might be a problem because im using python variables in the cursor.execute command, if so how should i have an input for the user to assign a variable to?

Answer 1

The documentation for the .execute method shows that the form of the method call is

execute(sql, *parameters)

and states that

The optional parameters may be passed as a sequence, as specified by the DB API, or as individual values.

So, you could either do

surnamereq = 'Thompson'
fname = 'Gord'
#
sql = "UPDATE money_spent SET first_name = ? WHERE last_name = ?"
params = (fname, surnamereq)
cursor.execute(sql, params)

or

surnamereq = 'Thompson'
fname = 'Gord'
#
sql = "UPDATE money_spent SET first_name = ? WHERE last_name = ?"
cursor.execute(sql, fname, surnamereq)

Note that the second approach is a pyodbc-specific extension to the DB API.

• Ryan pietras: What should i do if i get a Previous SQL was not a query. Error?
• Ryan pietras: It does it when i use the for row in cursor.fetchall(): print (row) command at the end.