Using variables to search using sql

Question

I am in the process of making a python program which you can enter, edit and delete data using sqlite. i know this is probably a basic issue but when i try to edit data using my variables it comes out with an error saying changename is not a column. Any ideas

def userchange():
 search = input("please enter a name to search for")
 changename = input("please enter name to change it to")
 sql = """UPDATE users SET FirstName = (changename) WHERE FirstName = (search)"""
 cursor.execute(sql)
 conn.commit()

• Barmar: Variables aren't replaced inside strings. You should use a prepared statement.

Answer 1

thanks for the help in the end i found that this method worked best

def edituser(self):
 search = self.entryVariable9.get()
 search1 = self.entryVariable10.get()
 changename = self.entryVariable11.get()
 cursor.execute("""UPDATE users SET FirstName = ? WHERE FirstName = ? AND Secondname = ?""", (changename, search, search1))
 conn.commit()

Answer 2

The execute method should accept parameters to replace into the query. https://www.python.org/dev/peps/pep-0249/#id15

For example if you are using pyscopg2 it would be like

def userchange():
 search = input("please enter a name to search for")
 changename = input("please enter name to change it to")
 sql = """UPDATE users SET FirstName = %s WHERE FirstName = %s"""
 cursor.execute(sql, (search, changeme))
 conn.commit()

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint. -- pyscopg2 docs