CODEWITHSUNDEEP
pythonpython-3.xdatabasesqliteuser-interface
BIGPESH
BIGPESH

Reputation: 83

How to update sqlite entry with variables in Python

I currently have a sqlite table created with test entries and need to be able to update a entry's rack and rackloc locations within the DB using ask strings from the user (like below)

# Move asset to another location in depot
def move_data_to_rack():
    serial_to_move = askstring('Move Asset', 'Please enter the Serial Number you want to move')
    serial_to_move = serial_to_move.upper()
    to_rack = askstring('Move Asset', 'Please enter the Rack you want to move to')
    to_rack = to_rack.upper()
    to_rack_loc = askstring('Move Asset', 'Please enter the Rack LOC you want to move to')
    to_rack_loc = to_rack_loc.upper()
    cursor.execute('''UPDATE depot_racks SET rack, rackloc = ?, ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))
    db.commit()
    messagebox.showinfo('Serial Moved', serial_to_move + ' has now been moved to ' + to_rack + to_rack_loc)

The table is created like below...

# Depot Table creation
cursor.execute('''
            CREATE TABLE IF NOT EXISTS depot_racks(
                id INTEGER PRIMARY KEY,
                rack TEXT,
                rackloc TEXT,
                floorzone TEXT,
                product TEXT,
                serial TEXT,
                status TEXT,
                entdate TEXT,
                servdate TEXT,
                parts TEXT,
                pat TEXT)''')

When the function above is run, I get the below error...

Exception in Tkinter callback
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/tkinter/__init__.py", line 1883, in __call__
    return self.func(*args)
  File "/Users/grantpeach/Library/Mobile Documents/com~apple~CloudDocs/Programming/Python Programming/Personal Projects/Arjo RackSys/RackSysMain.py", line 104, in move_data_to_rack
    cursor.execute('''UPDATE depot_racks SET rack, rackloc = ?, ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))
sqlite3.OperationalError: near ",": syntax error

So the idea behind the function is that the user will add the serial number they want to UPDATE then they enter the new rack location (eg. RACK1) and a new rackloc location (eg. A).

This then should find the serial and only update the rack and rackloc part of the row with that serial.

Any help with this is much appreciated

Upvotes: 2

Views: 962

Answers (1)

forpas
forpas

Reputation: 164089

Each ? placeholder must be assigned to the corresponding column name:

cursor.execute('''UPDATE depot_racks SET rack = ?, rackloc = ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))

If your SQLite version is 3.15.0+ you could also use the Row Values syntax:

cursor.execute('''UPDATE depot_racks SET (rack, rackloc) = (?, ?) WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))

Upvotes: 2

Related Questions