python MySQL更新特定列fetchall() [英] python MySQL update specific column fetchall()

问题描述

我是 Python 新手，我想更新数据库中每条计数为 0 的记录.我试了很多都找不到帮助之类的东西.

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])

推荐答案

假设您的表具有以下结构:

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()

此外，既然您提到您是 Python 新手，请记住每次运行 INSERT、UPDATE 或 DELETE 之类的查询时提交.

Also, since you mentioned that you are new to python remember to commit, every time, whenever you run INSERT, UPDATE or DELETE like queries.

希望有帮助.

这篇关于python MySQL更新特定列fetchall()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！