Skip to content
Advertisement

python MySQL update specific column fetchall()

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement