Objective and Prerequisites

Objective: This segment introduces how to insert and retrieve the 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 are created 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> DESC holders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint(20)  | NO   | PRI | NULL    |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| bank       | varchar(10) | YES  |     | NULL    |       |
| amount     | bigint(20)  | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
Python MySQL to insert and retrieve the records from a table
In the following program, we will be inserting records into a table using CSV (comma-separated values) file. Once all the records inserted, we will retrieve those records using MySQL SELECT statement and WHERE clause.
kw.csv
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
kw.py
import pymysql
import csv

tablename="holders"

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

    with open('kw.csv') as csvfile:
        reader=csv.reader(csvfile, delimiter=',')
        next(csvfile) #skip header row
        for row in reader:
            query="INSERT INTO {}(account_no, name, bank, amount) VALUES(%s, %s, %s, %s)".format(tablename)
            cursor.execute(query,row)
            db.commit()
    print("Records inserted successfully\n")
    
    print("Retrived records from a table")
    cursor.execute("SELECT * FROM {}".format(tablename))
    for records in cursor:
        print(records)
    
    print("\nRetrived records from a table")
    cursor.execute("SELECT * FROM {} WHERE amount >= 10000".format(tablename))
    for records in cursor:
        print(records)
    
    db.close()
except pymysql.Error as err:
    print("Error: {}".format(err))
Output
kodingwindow@kw:~$ python3 kw.py
Connection Established
Records inserted successfully

Retrived 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', 12250)

Retrived records from a table
(25622348991, 'Edward Parkar', 'ICICI', 95000)
(25622348992, 'Ryan Bakshi', 'Citi', 50000)
(25622348993, 'Marie Peters', 'DZBank', 12250)
MySQL Instance
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 * FROM holders WHERE amount >= 10000;
+-------------+---------------+--------+--------+
| account_no  | name          | bank   | amount |
+-------------+---------------+--------+--------+
| 25622348991 | Edward Parkar | ICICI  |  95000 |
| 25622348992 | Ryan Bakshi   | Citi   |  50000 |
| 25622348993 | Marie Peters  | DZBank |  12250 |
+-------------+---------------+--------+--------+
3 rows in set (0.00 sec)
What Next?
Python MySQL to update the table records Python MongoDB to connect, create database, and collection Python MongoDB to insert and retrieve the documents from a collection
Advertisement