Updating a single column with a list of strings-CodePudding

UPDATE: How do I specify that the first item in my list should be used to update the first row of the column and so on, until the end of the list (which contains the name number of items as there are rows in the column)?

sc_id = 1
for x in generator_list:
    c.execute('UPDATE breakdown SET characters = ? 
    WHERE rowid = sc_id', x)
    sc_id  = 1

gives the error: sqlite3.OperationalError: no such column: sc_id

PREVIOUS QUESTION: Why when I executemany UPDATE a column in SQLite3 it writes the last row to all rows?

I'm using :

c.executemany('UPDATE breakdown SET characters = ?', generator_list)

generator_list is constructed by:

generator_expression = ((val,) for val in updated_local_sc_char_lists)
generator_list = list(generator_expression)

updated_local_sc_char_lists = ['1-HARPER, RORY, THOMAS', 'AUGUST, THOMAS']

generator_list = [('1-HARPER, RORY, THOMAS',), ('AUGUST, THOMAS',)]

After I execute UPDATE and query selectall it returns:

[('AUGUST, THOMAS',), ('AUGUST, THOMAS',)]

CodePudding user response：

The problem is, that you dont specify a where condition in your sql statement. At the moment you send two updates. The first one changes all rows to ('1-HARPER, RORY, THOMAS',) The second statement then changes all rows to ('AUGUST, THOMAS',).

What you need to add is "WHERE" to specify, which rows should be changed. You probably have some kind of row id or other unique identifier for each row. If you tell us that identifier, I can help you modify your sql statement.

CodePudding user response：

The answer to the updated question is to just turn sc_id into another parameter I'm passing:

sc_id = 1
for x in generator_list:
    c.execute('UPDATE breakdown SET characters = ? 
    WHERE rowid = ?;', (x[0], sc_id))
    sc_id  = 1