I’m new to python and I want to update every record that has count 0 in the database. I have tried a lot can’t find anything like help.
for row in cur.fetchall(): if row[3] == 0: cur.execute("UPDATE tble SET count = 1 WHERE name = %s" %row[1])
Advertisement
Answer
Assuming your table has this structure:
CREATE TABLE `test` ( `sno` int(11) NOT NULL, `name` varchar(50) NOT NULL, `count` int(11) NOT NULL, `dtCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP );
Here is the simple code code-
import pymysql conn = pymysql.connect(host='localhost', unix_socket='', user='USER', passwd='PASSWORD', db='DATABASENAME') cur = conn.cursor() cur.execute("SELECT * FROM test") for r in cur: curr = conn.cursor() sql = """UPDATE test SET count = 1 WHERE name = '%s'""" % r[1] # print(sql) try: # Execute the SQL command curr.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() curr.close() cur.close() conn.close()
Also, since you mentioned that you are new to python remember to commit, every time, whenever you run INSERT, UPDATE or DELETE like queries.
Hope it helps.