OverIQ.com

Updating Data using Connector/Python

Last updated on July 27, 2020


In the last lesson, we saw how to insert rows in the tables. In this lesson, we will see examples of how to update data.

Update Single Row #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
import mysql.connector

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor(buffered=True)

sql1 = "update category set name=%s WHERE ID=2"

data1 = ('CSS',)

cursor.execute(sql1, data1)

db.commit()  # commit the changes

print("Rows affected:", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

Rows affected: 1

Updating rows in bulk #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
import mysql.connector
from datetime import datetime, timedelta

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor(buffered=True)

sql1 = "update post set date=%s"

data1 = [
            (datetime.now().date() + timedelta(days=10),), 
        ]

cursor.executemany(sql1, data1)

db.commit()  # commit the changes

print("Rows affected:", cursor.rowcount)

cursor.close()
db.close()

Expected Output:

Rows affected: 6