How to format UPDATE query referencing column names?

Question

My function iterates a list of column names contained in a table and runs a select query. It then iterates through each record and compares the field value with a cleansed version. If they differ I need to run an UPDATE query writing the cleansed value to that record. I cannot get the UPDATE query to work. Another function that does something similar using the following query:

dbcursor.execute('''UPDATE alib set live = (?) WHERE rowid = (?);''', (islive, row_to_process))

The difference is necessity of iterating column names. I've come to understand you cannot pass variables as field names to a SELECT statement, thus I've built the query string dynamically. That works. The update query is a different story:

for text_tag in text_tags:

    dbcursor.execute('''CREATE INDEX IF NOT EXISTS dedupe_tag ON alib (?) WHERE (?) IS NOT NULL;''', (text_tag, text_tag))
    print(f"- {text_tag}")

    ''' get list of matching records '''
    ''' as you cannot pass variables as field names to a SELECT statement build the query string dynamically then run it '''
    query = f"SELECT rowid, {text_tag} FROM alib WHERE {text_tag} IS NOT NULL;"
    dbcursor.execute(query)

    ''' now process each matching record '''
    records = dbcursor.fetchall()
    records_returned = len(records) > 0
    if records_returned:
          
        for record in records:
            <SNIP>

                if final_value != stored_value_sorted:

                    ''' write out {final_value} to column {text_tag} for record = rowid '''
                    row_to_process = record[0]
                    query = SEE QUERIES BELOW THIS CODE SNIPPET
                    print(query) # temp code to see what query is generated
                    dbcursor.execute(query)

I attempted 3 variations of UPDATE query and each throws a different error. The value to be written can have any number of \, ', ", [, ], (, ) and punctuation marks. Line 77 is dbcursor.execute(query):

query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)

('UPDATE alib SET artist = (?) WHERE rowid = (?);', ('8:58\\\\The Unthanks', 305091))
Traceback (most recent call last):
  File "<string>", line 80, in <module>
  File "<string>", line 77, in dedupe_fields
TypeError: execute() argument 1 must be str, not tuple

query = f"UPDATE alib SET {text_tag} = '{final_value}' WHERE rowid = {row_to_process};"

UPDATE alib SET recordinglocation = 'Ashwoods, Stockholm\\Electric Lady Studioss, Stockholm\\Emilie's, Stockholm\\Ingrid Studioss, Stockholm\\Judios Studioss, Stockholm\\Nichols Canyon Houses, Stockholm\\Ocean Way Studioss, Stockholm\\RAK Studios, London\\Studio De La Grande Armée, Paris\\The Villages, Stockholm\\Vox Studioss, Stockholm' WHERE rowid = 124082;
Traceback (most recent call last):
  File "<string>", line 80, in <module>
  File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near "s": syntax error

query = f"UPDATE alib SET {text_tag} = {final_value} WHERE rowid = {row_to_process};"

UPDATE alib SET artist = 8:58\\The Unthanks WHERE rowid = 305091;
Traceback (most recent call last):
  File "<string>", line 80, in <module>
  File "<string>", line 77, in dedupe_fields
sqlite3.OperationalError: near ":58": syntax error

Answer 1

The problem is with how your query variable is defined:

This is a tuple:

query = f"UPDATE alib SET {text_tag} = (?) WHERE rowid = (?);", (final_value, row_to_process)

Therefore this:

dbcursor.execute(query)

is effectively this

dbcursor.execute((sql, values))

so you are passing a single object (the tuple) to execute, which assumes it is the query string and so errors.

Instead, unpack the tuple so that execute receives two separate parameters:

dbcursor.execute(*query)