Using PyMySQL in Python for MySQL Database Operations: Connection, CRUD, Transactions, and Error Handling

We can use MySQL alone for data analysis or combine it with Python for richer applications such as simple CRUD operations.

First, install the PyMySQL package, which provides methods for operating MySQL from Python.

Typical Python database workflow consists of four steps: import the DB package, connect to the database, perform CRUD operations, and close the connection.

Database connection

Before connecting, ensure that on your local MySQL you have created a database and a table, for example:

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
);

Use a user such as "testuser" with password "123456" (or root) and grant appropriate privileges.

Create database table

After a successful connection, you can create the EMPLOYEE table with cursor.execute(...) as shown in the screenshot.

Insert operation

Insert records into EMPLOYEE using an SQL INSERT statement, for example:

INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('John', 'Doe', 30, 'M', 5000);

Query operation

Python can fetch a single row with fetchone() or all rows with fetchall() . The rowcount attribute reports the number of rows affected.

Example query to retrieve employees with salary greater than 1000:

SELECT * FROM EMPLOYEE WHERE INCOME > 1000;

Update operation

Update the SEX field to 'M' and increment AGE by 1 for all rows:

UPDATE EMPLOYEE SET SEX='M', AGE=AGE+1;

Delete operation and transactions

Delete rows where AGE > 20:

DELETE FROM EMPLOYEE WHERE AGE > 20;

Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability). In Python DB‑API, a transaction starts automatically when a cursor is created; commit() finalizes changes and rollback() reverts them.

Error handling

The DB‑API defines a set of exceptions (e.g., DatabaseError , IntegrityError , etc.) for handling database errors, as illustrated in the accompanying table.