Objective and Prerequisites

Objective: This segment introduces how to update and retrieve the updated records from a table in MySQL using Python.

Prerequisites: PyMySQL and CSV modules are installed, and MySQL Database is up and running in the background. Also, the database DB and table holders (with records in it) are present in MySQL. If not, then refer to the Python Database Connectivity segment in sequence.

Start MySQL
kodingwindow@kw:~$ sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36-2ubuntu3 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| DB                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE DB;
Database changed

mysql> SHOW TABLES;
+--------------+
| Tables_in_DB |
+--------------+
| holders      |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM holders;
+-------------+---------------+----------+--------+
| account_no  | name          | bank     | amount |
+-------------+---------------+----------+--------+
| 25622348989 | James Moore   | Barclays |   5000 |
| 25622348990 | Donald Taylor | Citi     |   7000 |
| 25622348991 | Edward Parkar | ICICI    |  95000 |
| 25622348992 | Ryan Bakshi   | Citi     |  50000 |
| 25622348993 | Marie Peters  | DZBank   |  12250 |
+-------------+---------------+----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT name, amount FROM holders;
+---------------+--------+
| name          | amount |
+---------------+--------+
| James Moore   |   5000 |
| Donald Taylor |   7000 |
| Edward Parkar |  95000 |
| Ryan Bakshi   |  50000 |
| Marie Peters  |  12250 |
+---------------+--------+
5 rows in set (0.00 sec)
Python MySQL to update the table records
kw.py
import pymysql
import csv

tablename="holders"
update=(2000, 25622348993) #packing
(bonus, accno)=update #unpacking

try:
    db=pymysql.connect(host='localhost', user='root', password='kodingwindow', database='DB')
    print("Connection Established")
    cursor=db.cursor()

    def RetrieveRecords():
        cursor.execute("SELECT * FROM {}".format(tablename))
        print("\nRetrived updated records from a table")
        for records in cursor:
            print(records)
    
    query="UPDATE {} SET amount = amount + %s WHERE account_no = %s".format(tablename)
    cursor.execute(query,update)
    db.commit()
    RetrieveRecords()
        
    query="UPDATE {} SET amount = amount + %s".format(tablename)
    cursor.execute(query,bonus)
    db.commit()
    RetrieveRecords()
    
    db.close()
except pymysql.Error as err:
    print("Error: {}".format(err))
Explanation:
When the first update query executed with WHERE clause, account number 25622348993 updated with an amount of 2000 as a bonus. On the other hand, when the second update query executed without WHERE clause, then all the records updated with an amount of 2000 as a bonus. Hence the final amount 16250 for account number 25622348993.
Output
kodingwindow@kw:~$ python3 kw.py
Connection Established

Retrived updated records from a table
(25622348989, 'James Moore', 'Barclays', 5000)
(25622348990, 'Donald Taylor', 'Citi', 7000)
(25622348991, 'Edward Parkar', 'ICICI', 95000)
(25622348992, 'Ryan Bakshi', 'Citi', 50000)
(25622348993, 'Marie Peters', 'DZBank', 14250)

Retrived updated records from a table
(25622348989, 'James Moore', 'Barclays', 7000)
(25622348990, 'Donald Taylor', 'Citi', 9000)
(25622348991, 'Edward Parkar', 'ICICI', 97000)
(25622348992, 'Ryan Bakshi', 'Citi', 52000)
(25622348993, 'Marie Peters', 'DZBank', 16250)
MySQL Instance
mysql> SELECT * FROM holders;
+-------------+---------------+----------+--------+
| account_no  | name          | bank     | amount |
+-------------+---------------+----------+--------+
| 25622348989 | James Moore   | Barclays |   7000 |
| 25622348990 | Donald Taylor | Citi     |   9000 |
| 25622348991 | Edward Parkar | ICICI    |  97000 |
| 25622348992 | Ryan Bakshi   | Citi     |  52000 |
| 25622348993 | Marie Peters  | DZBank   |  16250 |
+-------------+---------------+----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT name, amount FROM holders;
+---------------+--------+
| name          | amount |
+---------------+--------+
| James Moore   |   7000 |
| Donald Taylor |   9000 |
| Edward Parkar |  97000 |
| Ryan Bakshi   |  52000 |
| Marie Peters  |  16250 |
+---------------+--------+
5 rows in set (0.00 sec)
What Next?
Python MongoDB to connect, create database, and collection Python MongoDB to insert and retrieve the documents from a collection
Advertisement