Why wont this loop?-CodePudding

Newby working my way through python and sql with mariadb. Why wont this loop? It updates the first record only. Im pretty hack at this...

cursor1.execute("select recordid, mvavgvol, quote_usd_volume_change_24h from pumped")
records = cursor1.fetchall()

for x in records:

            rid = (x[0])
            m = (x[1])
            o = (x[2])

            if (m >= o):
                  result = 0
            else:
                  result = 1

upd_data=(result,rid)
sql1 = ("UPDATE pumped SET mvavgvolcheck = %s WHERE recordid = %s")
cursor2.execute(sql1,upd_data)
conn.commit()

CodePudding user response：

conn = msql.connect(host=Host,port=Port, user=User, password=Password, database=database)
cursor1 = conn.cursor()
cursor2 = conn.cursor()

cursor1.execute("select recordid, mvavgvol, quote_usd_volume_change_24h from pumped")
records = cursor1.fetchall()

for x in records:

            rid = (x[0])
            m = (x[1])
            o = (x[2])

            if (m >= o):
                  result = 0
                  cursor2.execute("UPDATE pumped SET mvavgvolcheck = %s WHERE recordid = %s",(result, rid))
                  conn.commit()

            else:
                  result = 1
                  cursor2.execute("UPDATE pumped SET mvavgvolcheck = %s WHERE recordid = %s",(result, rid))
                  conn.commit()

CodePudding user response：

Since you are fetching multiple rows you have to store the fetched values in an array and use cursor's executemany() method instead.

✂

data= []
for x in records:
            rid = (x[0])
            result= int(x[1] > x[2])
            data = [(result, rid)]

cursor.executemany(UPDATE pumped SET  mvavgvolcheck = %s WHERE recordid = %s", data);

✂

When using mariadb python module (MariaDB Connector/Python) this is much more effective since it reduces network traffic: instead of sending n update commands in a loop (where n is the number of rows in table pumped) only one command will be send to the server.