0

I have the following table: http://prntscr.com/gc9oat

I'm trying to update the column NOTIFIED as I loop through rows in this table called notifyTable on the condition of 

if coinMarketValue > value and notified == 0:

This is the code I currently have

connection = sqlite3.connect('notify.db')

c = connection.cursor()    
c.execute('SELECT * FROM notifyTable')
data = c.fetchall()


for row in data:
    authorID = row[1]
    coin = row[2]
    operator = row[3]
    value = row[4]
    coinMarketValue = row[5]
    notified = row[6]

    if operator == '<':           
        if coinMarketValue < value and notified == 0: #notified hasnt been set true 
            print("coin market value is less than value so updating notifed to True")             
            connection.execute("UPDATE notifyTable set notified = 'True' WHERE coinMarketValue < value AND notified == 0")
            connection.commit()

Right, now the code goes through each row and if the condition is True.

coinMarketValue < value AND notified == 0

Then it will update the all the Notified columns to True - instead of just updating the current row.

So, how can I only update Notified on the row the script is currently working on (instead of updating all the rows)

Thanks

smye
  • 79
  • 1
  • 1
  • 10

1 Answers1

1

If taskname is the primary key you can do this:

for row in data:
    taskname = row[0]
    # ...

    if operator == '<':           
        if coinMarketValue < value and notified == 0: #notified hasnt been set true 
            # ...
            connection.execute("""UPDATE notifyTable 
                                  SET notified = 'True' 
                                  WHERE coinMarketValue < value 
                                  AND notified = 0
                                  AND taskName = ?""",(taskname,))
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Oh my, I completely forgot I even had that there nor even considered using it like that. Thank you. This will only change one row, assuming that the task name is unique all other three conditions are True, correct? – smye Aug 23 '17 at 22:57
  • You're very welcome. That is correct it will change only one row assuming the task name is unique. – mechanical_meat Aug 23 '17 at 23:01