Elegant way to update multiple values for Sqlite3 Python
I have a database Sqlite3
called MYTABLE as follows:
My goal is to update the COUNT column values by simply adding the existing value to the new value.
There will be two inputs that I will get:
- First, a list of IDs that I need to update for the COUNT column . For example: ['1', '3', '2', '5']
- And second, the number of counters added to each identifier in the list above.
So far, the best I can think of is:
#my input1, the list of IDs that need updating
id_list = ['1','2','5','3']
#my input2, the value to be added to the existing count value
new_count = 3
#empty list to store the original count values before updating
original_counts = []
#iterate through the input1 and retrieve the original count values
for item in id_list :
cursor = conn.execute("SELECT COUNT from MYTABLE where ID=?",[item])
for row in cursor:
original_counts.append(row[0])
#iterate through the input1 and update the count values
for i in range(len(id_list )):
conn.execute("UPDATE MYTABLE set COUNT = ? where ID=?",[original_counts[i]+new_count ,mylist[i])
Is there a better / more elegant and more efficient way to achieve what I want?
UPDATE 1:
I tried this based on N Reed's answer (not quite the same) like this and it worked!
for item in mylist:
conn.execute("UPDATE MYTABLE set VAL=VAL+? where ID=?",[new_count,item])
Check it out , we can update the value in Sqlite3
based on its current value (which I didn't know)
source to share
You want to create a query that looks like this:
UPDATE MYTABLE set COUNT = COUNT + 5 where ID in (1, 2, 3, 4)
I don't know what python is, but you probably want the code in python to look something like this:
conn.execute("UPDATE MYTABLE set COUNT = COUNT + ? where ID in (?)", new_count , ",".join(mylist))
Keep in mind that there is a limit to the number of items you can have in a list of ids with sqllite (I think this is something like 1000)
Also, be very careful with SQL injection when building queries this way. You probably want to make sure that all the items in the list have already escaped somewhere else.
I also recommend not to have a column named "count" as that is a keyword in sql.
source to share