Master MySQL with Python: A Step‑by‑Step CRUD Tutorial

Introduction

When working with Python, saving data to text files is convenient, but for large datasets a database is needed. This article introduces MySQL and shows how to use it with Python.

1. Install the pymysql module

Install and import pymysql:

pip install pymysql
import pymysql

2. Create a database

Use a client such as SqlYong to create a database named people: CREATE DATABASE IF NOT EXISTS people; This creates the people database.

3. Create a table and insert data

USE people;
CREATE TABLE IF NOT EXISTS student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME CHAR(10) UNIQUE,
    score INT NOT NULL,
    tim DATETIME
) ENGINE=INNODB CHARSET=utf8;
INSERT INTO student(NAME,score,tim) VALUES('fasd',60,'2020-06-01');
SELECT * FROM student;

The student table is created and a row is inserted.

4. Connect Python to MySQL

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')

The connection succeeds and prints MySQL version and host information.

5. Use a cursor to execute operations

5.1 Create cursor

cur = db.cursor()

5.2 Insert data (single row)

sql = "INSERT INTO student(NAME,score,tim) VALUES ('任性的90后boy',100,now())"
cur.begin()
try:
    cur.execute(sql)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    cur.close()
    db.close()

5.3 Insert data (custom input)

import pymysql, time
tt = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')
cur = db.cursor()
cur.begin()
s = input('string:')
d = input('number:')
sql = "INSERT INTO student(NAME,score,tim) VALUES('%s','%s','%s')"
try:
    cur.execute(sql % (s, d, tt))
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    cur.close()
    db.close()

5.4 Bulk insert with executemany

import pymysql, time
start = time.time()
tt = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')
cur = db.cursor()
cur.begin()
sql = "INSERT INTO student(NAME,score,tim) VALUES (%s,%s,%s)"

def get():
    data = []
    for y in range(1, 100000):
        if y >= 100:
            name = 'user-' + str(y)
            score = str(float('%.f' % (y % 100)))
        else:
            name = 'user-' + str(y)
            score = str(y)
        data.append((name, score, tt))
    return data

try:
    data = get()
    cur.executemany(sql, data)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    print('Insertion complete')
    cur.close()
    db.close()
    end = time.time()
    print('Time used:', end - start)

5.5 Update data

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')
cur = db.cursor()
cur.begin()
sql = "UPDATE student SET name='zjj' WHERE score=100"
try:
    cur.execute(sql)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    cur.close()
    db.close()

5.6 Delete a specific row

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')
cur = db.cursor()
cur.begin()
sql = "DELETE FROM student WHERE name='fasd';"
try:
    cur.execute(sql)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    cur.close()
    db.close()

To remove all rows, use TRUNCATE student;. To drop the table entirely, use DROP TABLE IF EXISTS student; (generally not recommended).

5.7 Query data

import pymysql, time
tt = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='people')
cur = db.cursor()
cur.begin()
sql = "SELECT * FROM student;"
try:
    cur.execute(sql)
    res = cur.fetchall()
    for row in res:
        print(row)
except Exception as e:
    print(e)
    db.rollback()
else:
    db.commit()
finally:
    cur.close()
    db.close()

Conclusion

When scraping large amounts of data, a database like MySQL provides a convenient and efficient way to store and manage the information, complementing Python’s data‑processing capabilities.