Master Python MySQL Integration with PyMySQL: Install, Connect, and CRUD Guide

Install PyMySQL Library

To operate MySQL databases with Python, first install the pymysql package using pip install pymysql. If the standard installation fails, download a suitable wheel from https://www.lfd.uci.edu/~gohlke/pythonlibs/ .

Connect Python to MySQL

Key connection parameters:

host : IP address or hostname of the MySQL server.

user : Username for authentication.

password : Corresponding password.

port : Server port, default 3306.

db : Target database name.

charset : Character set (e.g., utf8) to avoid garbled text when the client and server use different encodings.

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3300, db='spiders', charset='utf8')

Simple Query Example

# Import module
import pymysql

# Connect and create cursor
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='spiders', charset='utf8')
cursor = db.cursor()

# Execute SQL
sql = "select * from student"
cursor.execute(sql)

data = cursor.fetchone()
print(data)

db.close()

Result:

Cursor Common Methods

execute(query, args) : Execute a single SQL statement; returns affected rows.

executemany(query, args) : Execute the same statement repeatedly with a list of parameters.

fetchone() : Retrieve one result row.

fetchmany(size) : Retrieve a specified number of rows.

fetchall() : Retrieve all remaining rows.

Create Table (建)

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='huangwei', charset='utf8')

cursor = db.cursor()
sql = """create table person(
        id int auto_increment primary key not null,
        name varchar(10) not null,
        age int not null) charset=utf8"""
cursor.execute(sql)

db.close()

Insert Data (增)

Single row insertion:

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='huangwei', charset='utf8')
cursor = db.cursor()
sql = 'insert into person(name,age) values ("猪八戒",8000)'
try:
    cursor.execute(sql)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()

db.close()

Batch insertion:

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='huangwei', charset='utf8')
cursor = db.cursor()
sql = 'insert into person(name,age) values(%s,%s)'
datas = [('牛魔王',9000),('铁扇公主',8000),('玉皇大帝',6000)]
try:
    cursor.executemany(sql, datas)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()

db.close()

Update Data (改)

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='huangwei', charset='utf8')
cursor = db.cursor()
sql = 'update person set age=%s where name=%s'
try:
    cursor.execute(sql, [90000, "玉皇大帝"])
    db.commit()
    print("更新成功")
except:
    print("更新失败")
    db.rollback()

db.close()

Delete Data (删)

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456',
                     port=3306, db='huangwei', charset='utf8')
cursor = db.cursor()
sql = 'delete from person where age=8000'
try:
    cursor.execute(sql)
    db.commit()
    print("删除成功")
except:
    print("删除失败")
    db.rollback()

db.close()

Data Retrieval with Pandas

import pymysql
import pandas as pd

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

df_male = pd.read_sql("select * from student where ssex='男'", db)
display(df_male)

df_female = pd.read_sql("select * from student where ssex='女'", db)
display(df_female)

Result:

Summary

PyMySQL enables transaction support by default; remember to call db.commit() after INSERT, UPDATE, or DELETE operations.

Wrap data‑modifying statements in try…except blocks and use db.rollback() on failure to maintain data integrity.