I made next Query to update a row in my DB.
def saveData(title, LL, LR, RL, RR, distanceBack):
c.execute("UPDATE settings SET (?,?,?,?,?,?) WHERE name=?",(title, LL, LR, RL, RR, distanceBack, title))
conn.commit()
I always get next error: sqlite3.OperationalError: near "(": syntax error I know something isn't correct with the question marks. I can't find out what the exact solution is. Can somebody explain me what the problem is?
You could use this SQL syntax:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
for example, if you have a table called Category and you want to edit the category name you can use:
c.execute("UPDATE CATEGORY SET NAME=? WHERE ID=?", (name,category_id))
WHERE:
Category is a table that contains only two items: (ID, NAME) with ID PRIMARY KEY.
You can try this simply adding column name as a string in the query
import sqlite3
def updatedata():
conn=sqlite3.connect('SCHOOL.db')
cur=conn.cursor()
rno=int(input('Enter roll number whose data to be changed'))
column=input("enter column to be updated")
value=int(input("enter value to set"))
query='UPDATE STUDENT SET '+column+ ' = ? WHERE Rollno = ?'
cur.execute(query,(value,rno))
conn.commit()
print("Record Updated successfully")
conn.close()
To answer your question (and if anyone else is wondering)
Can somebody explain me what the problem is?
I believe it most likely has to do with what version of SQLite your python installation is using.
The official SQLite documentation says in its UPDATE documentation:
Beginning in SQLite version 3.15.0 (2016-10-14), an assignment in the SET clause can be a parenthesized list of column names on the left and a row value of the same size on the right.
So you can check what version of SQLite your Python installation is using like this:
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.8.11' # return value from my python 2.7.14 installation
Which means I won't be able to use the syntax SET (column1, column2) = (value1, value2) and instead I'd have to use the syntax SET column1 = value1, column2 = value2. Hope this helps.
It may be depend on versions used! The following code works for:
because I am using the latest: wx.python 4.2.1 (2023-06-07)
sqlUPDPrepared = 'UPDATE settings SET title=?,LL=?,LR=?,RL=?,RR=?,distanceBack=? WHERE name=?'
# note: no braket after SET, ...
paramList = [title, LL, LR, RL, RR, distanceBack, title]
# note: the last parameter in the list is for the where clause!
# note: cursor.execute expects a tuple for the 2nd parameter!*
updateTuple = tuple(item for item in paramList) # = (title, LL, LR, RL, RR, distanceBack, title)
c.execute(sqlUPDPrepared, updateTuple)
As far as I know the
UPDATE query must look like UPDATE table SET (key=value, key2=value2, ...) WHERE <condition>
Try to modify query to something like
c.execute("UPDATE settings SET (title=?,LL=?,LR=?,RL=?,RR=?,distanceBack=?) WHERE name=?",(title, LL, LR, RL, RR, distanceBack, title))
c.execute("UPDATE settings SET ({},{},{},{},{},{}) WHERE name={}".format(title, LL, LR, RL, RR, distanceBack, title)). When using string formatting to form queries that are executed by a DB you have to be careful of the injections you are allowing. If this will be used by other people too, you have to do some reading..UPDATEsyntax is wrong - w3schools.com/sql/sql_update.asp