CODEWITHSUNDEEP
pythonsqlite
EMJAY
EMJAY

Reputation: 5

Update a column in SQL database using Python, sqlite3 and a list

I have looked at other questions similar to this but some are ambiguous with little explanation of the answer. What I have is a sql database with 6 tables containing n number of columns. I want to update, not insert, a column in one of the tables with data from a python list. This is a list called gbufids and consists of integers, but each entry is not contained in Tuples i.e [0,1,2,3] not [(1,), (2,), (3,)]. The table name I want to change is called Versions and the column is called ObjectID. I tried various ways I found, but none did the job in the way it needed to be done. I used the following, which I believe should convert each item in the list to a tuple before updating the column (ObjectID) in the table (Versions): cur.executemany('UPDATE Versions set ObjectID=?', ((val,) for val in gbufids))

This is followed by a commit and a close. I have read that I need a WHERE clause if I am using UPDATE, but I was a bit confused about row. To me, a row is all cells in a row, but this is probably not what is meant. If anyone can provide an answer that is specific to my db, tables and column, it will be appreciated

Upvotes: 0

Views: 1950

Answers (1)

glibdud
glibdud

Reputation: 7840

An UPDATE statement without a WHERE clause will cause all rows of the table to be updated with each value you pass into the executemany, meaning you'll end up with all rows populated with the last value you passed. In order to match each value with a particular row, you'll need some way to tell the database which row you want to replace in each run of the statement. If you're sure the length of your list of updated values is equal to the number of rows in the table, and the order of your list is the same as the order of the rows you'd get with a simple SELECT * FROM table, then the easiest way would be to get the list of rowids in the table:

rowids = [row[0] for row in cur.execute('SELECT rowid FROM Versions')]

Then you can pair up rowids and gbufids and pass that to your executemany:

cur.executemany('UPDATE Versions SET ObjectID=? WHERE rowid=?', zip(gbufids, rowids))

Upvotes: 1

Related Questions