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()

Variables aren't replaced inside strings. You should use a prepared statement. — Barmar
– Barmar, Commented Dec 13, 2016 at 20:30

Oliver Fisher · Accepted Answer · 2016-12-14 19:30:33Z

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()

answered Dec 14, 2016 at 19:30

Oliver Fisher

232 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex G Rice · Accepted Answer · 2016-12-13 22:27:55Z

0

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

answered Dec 13, 2016 at 22:27

Alex G Rice

1,57911 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using variables to search using sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related