SQLite3 syntax error when trying to update to a specific record. How do I give command both to choos-CodePudding

Here is a functioning block of code that illustrates my database.
Problems arise with trying to edit a record using UPDATE with data in a list. I can create records from a list, but that doesn't include the 'WHERE oid = ? ' command to select which record to update.

import sqlite3


oid = 1

inpts = list()
inpts.append('Breadname')
inpts.append('33')
inpts.append('Flour')
inpts.append('Mix and Bake!')

edits = list()
edits.append('Brodname')
edits.append('25')
edits.append('Flower')
edits.append('Mix a lot and Bake!')


def make_SQL():          # create SQL file and a table named 'brods'
    conn = sqlite3.connect('formulas.sqlite')
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS brods 
    (fname TEXT, msr1 TEXT, ngr1 TEXT, notes TEXT)""")
    conn.commit()
    conn.close()

def fill_SQL(inpts):
    conn = sqlite3.connect('formulas.sqlite')
    cur = conn.cursor()
    cur.execute("""INSERT INTO brods 
    (fname, msr1, ngr1, notes) 
    VALUES (?,?,?,?)""",(inpts))
    conn.commit()


def save_edits_SQL(edits,oid):
    conn = sqlite3.connect('formulas.sqlite')
    cur = conn.cursor()
    cur.execute("""UPDATE brods 
    (fname, msr1, ngr1, notes) 
    VALUES (?,?,?,?) WHERE oid = ? """,(edits,oid))
    conn.commit()


def get_formula(oid):
    f_data = ()
    conn = sqlite3.connect('formulas.sqlite')
    cur = conn.cursor()
    cur.execute('SELECT * FROM brods WHERE oid = ?', (oid,))
    conn.commit()
    for row in cur:
        f_data = row
    conn.commit()
    conn.close()
    print('fdata',f_data)
    conn.close()
    return (f_data)

make_SQL()
fill_SQL(inpts)
save_edits_SQL(edits,oid)
get_formula(1)

running save_edits_SQL produces the following error message:

Traceback (most recent call last):
  File "/Users/patrickscott/PycharmProject/PY4EveryoneTutorial/Ch 3 Conditional Execution/Bakers Formulas/sandbox.py", line 62, in <module>
    save_edits_SQL(edits,oid)
  File "/Users/patrickscott/PycharmProject/PY4EveryoneTutorial/Ch 3 Conditional Execution/Bakers Formulas/sandbox.py", line 40, in save_edits_SQL
    cur.execute("""UPDATE brods 
sqlite3.OperationalError: near "(": syntax error

CodePudding user response：

That's not how update statements work.

You need to use SET, e.g something like this:

cur.execute("""UPDATE brods
               SET fname = ?,
                   msr1 = ?,
                   ngr1 = ?,
                   notes = ?
               WHERE oid = ?""",
            (*edits, oid))

cur.execute("""UPDATE brods
               SET (fname, msr1, ngr1, notes) = (?, ?, ?, ?)
               WHERE oid = ?""",
            (*edits, oid))

I also changed (edits, oid) to (*edits, oid) here to make sure we get a flat tuple instead of a nested one. You could use edits [oid] instead if you prefer.

Compare:

>>> ((1, 2, 3), 4)
((1, 2, 3), 4)
>>>
>>> (*(1, 2, 3), 4)
(1, 2, 3, 4)
>>>
>>> [1, 2, 3]   [4]
[1, 2, 3, 4]