Python operates MySQL database

Python connects to MySQL database

In the Python 3 ecosystem, the mainstream MySQL driver libraries aremysqlclientandpymysqlTwo kinds. The APIs of the two are almost identical, and the only difference is the imported module name. We choose to implement it in pure Pythonpymysql, it does not require compilation of C extensions, and can be installed successfully on Windows, macOS, and Linux with one click. It is very suitable for novices to get started and is also capable of quickly setting up a production environment.

This tutorial uses pre-createdhrs(Human Resources) database is taken as an example to demonstrate step by step the complete process and code of Python operating MySQL.

Step 1: Environment preparation and connection process

Install dependencies

If your MySQL is version 5.x, only installpymysqlThat’s it; if you are connecting to MySQL 8.x, additional installation is required.cryptography, to support the new version’s defaultcaching_sha2_passwordAuthentication method.

Execute in the terminal (or PyCharm built-in Terminal):

pip install pymysql cryptography

Operation process sorting

pymysqlThe standard operation can be summarized into 5 core steps. We use the express delivery process to help remember:

  1. Establish Connection - Open up an exclusive channel between you and the warehouse, and tell the program the warehouse address, port, account password, database name and code.
  2. Get Cursor - Get the warehouse manager's "scanner", and all subsequent additions, deletions, modifications and searches will depend on it.
  3. Execute SQL command - Send specific SQL statements to the database through the cursor.
  4. Processing results or transactions — If it is a write operation (insert, delete, modify), it is requiredcommitConfirm to save, if any problem occursrollbackCancel; if it is a query, usefetchThe series method takes the data.
  5. Close connection — Disconnect the connection immediately after use and release resources.

As long as you remember these five steps, you can quickly write schema-standard code for any database operation.

Step 2: Core Code Practice

Preparation: Create a dedicated database user

For security reasons, long-term use in production environments or learning is not recommended.rootSuper managed account. We first create a database in MySQL that only hashrsOrdinary users with basic library permissions:

-- 创建允许任意 IP 连接的 guest 用户
create user 'guest'@'%' identified by 'Guest.618';
-- 为 guest 用户授予 hrs 数据库的基础增删改查权限
grant insert, delete, update, select on `hrs`.* to 'guest'@'%';
-- 刷新权限，让配置立即生效
flush privileges;

Tips: The above operations requirerootThe account is executed in the MySQL command line or tools such as Navicat.

Insert a single piece of data

The following code simulates the scenario where the user enters department information and saves it to the database.

import pymysql

# 模拟用户输入
no = int(input('请输入部门编号: '))
name = input('请输入部门名称: ')
location = input('请输入部门所在地: ')

# 1. 建立连接（charset 使用 utf8mb4 支持完整的中文和 emoji）
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4'
)

try:
    # 2. 使用 with 获取游标，自动释放，无需手动关闭
    with conn.cursor() as cursor:
        # 3. 执行插入，%s 为占位符，pymysql 会自动转义，防止 SQL 注入
        affected_rows = cursor.execute(
            'insert into `tb_dept` (`dno`, `dname`, `dloc`) values (%s, %s, %s)',
            (no, name, location)
        )
        if affected_rows == 1:
            print('🎉 新增部门成功！')
    # 4. 提交事务：只有 commit 后数据才会真正写入
    conn.commit()
except pymysql.MySQLError as err:
    # 出现异常时回滚
    conn.rollback()
    print(f'⚠️ 操作失败：{type(err)} - {err}')
finally:
    # 5. 关闭连接
    conn.close()

Efficiency Tips: If you want to insert thousands of pieces of data in batches, it is recommended to usecursor.executemany(sql, data_list), the speed can be increased dozens of times.

Delete data

Demo starts here autocommit=True is suitable for simple scenarios of automatic submission of a single SQL statement (multi-step operations are not recommended for direct use).

import pymysql

no = int(input('请输入要删除的部门编号: '))

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4',
    autocommit=True      # 开启自动提交
)

try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'delete from `tb_dept` where `dno` = %s',
            (no,)           # 只有一个参数时也必须写成元组
        )
        if affected_rows == 1:
            print('🎉 删除部门成功！')
        else:
            print('⚠️ 未找到该部门！')
finally:
    conn.close()

Update data

Update operations still require manual transaction management to ensure data consistency.

import pymysql

no = int(input('请输入要修改的部门编号: '))
name = input('请输入新的部门名称: ')
location = input('请输入新的部门所在地: ')

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        affected_rows = cursor.execute(
            'update `tb_dept` set `dname` = %s, `dloc` = %s where `dno` = %s',
            (name, location, no)
        )
        if affected_rows == 1:
            print('🎉 更新部门信息成功！')
    conn.commit()
except pymysql.MySQLError as err:
    conn.rollback()
    print(f'⚠️ 操作失败：{err}')
finally:
    conn.close()

Query data

pymysqlThree methods for extracting results are provided, which can be flexibly selected according to the amount of data:

  • fetchone(): Returns tuples row by row, with minimal memory overhead and suitable for large data sets.
  • fetchmany(size=n): Get n records at a time.
  • fetchall(): Returns all results at once, suitable for small data sets.
1. Ordinary cursor reads line by line
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        cursor.execute('select `dno`, `dname`, `dloc` from `tb_dept`')
        print('📋 部门列表：')
        row = cursor.fetchone()
        while row:
            print(f'  - 编号：{row[0]} | 名称：{row[1]} | 地点：{row[2]}')
            row = cursor.fetchone()
except pymysql.MySQLError as err:
    print(f'⚠️ 查询失败：{err}')
finally:
    conn.close()
2. Dictionary cursor paging query

The default cursor returns tuple, and the field order depends on the SQL statement, which is not very readable. usepymysql.cursors.DictCursorThe results can be returned in the form of a dictionary, with the key name being the field name, which is more clear and intuitive.

import pymysql

page = int(input('请输入页码: '))
size = int(input('请输入每页大小: '))

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4'
)

try:
    # 指定游标类型为 DictCursor
    with conn.cursor(pymysql.cursors.DictCursor) as cursor:
        cursor.execute(
            'select `eno`, `ename`, `job`, `sal` from `tb_emp` order by `sal` desc limit %s, %s',
            ((page - 1) * size, size)
        )
        emp_list = cursor.fetchall()
        if emp_list:
            print(f'📋 第{page}页员工列表（按薪资降序）：')
            for emp in emp_list:
                print(
                    f'  - 工号：{emp["eno"]} | '
                    f'姓名：{emp["ename"]} | '
                    f'职位：{emp["job"]} | '
                    f'月薪：{emp["sal"]}'
                )
        else:
            print('⚠️ 该页无数据！')
finally:
    conn.close()

Step 3: Practical Case—Export Data to Excel

In daily work, it is often necessary to export database tables into Excel reports. Pure Python is used here.openpyxllibrary to implement.

Install openpyxl

pip install openpyxl

Export code

Goal: totb_empandtb_deptAfter natural connection, the employee’s job number, name, position, monthly salary, allowance, and department information are exported as员工表.xlsx。

import openpyxl
import pymysql

# 1. 创建工作簿并修改工作表标题
wb = openpyxl.Workbook()
ws = wb.active
ws.title = '员工基本信息'

# 2. 写入表头
ws.append(('工号', '姓名', '职位', '月薪', '补贴', '部门'))

# 3. 连接数据库查询数据
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='guest',
    password='Guest.618',
    database='hrs',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # coalesce(comm, 0)：将 NULL 补贴替换为 0
        cursor.execute(
            'select `eno`, `ename`, `job`, `sal`, coalesce(`comm`, 0), `dname` '
            'from `tb_emp` natural join `tb_dept`'
        )
        # 4. 逐行写入 Excel
        row = cursor.fetchone()
        while row:
            ws.append(row)
            row = cursor.fetchone()
    # 5. 保存文件
    wb.save('员工表.xlsx')
    print('🎉 Excel 导出成功！文件名为「员工表.xlsx」')
except pymysql.MySQLError as err:
    print(f'⚠️ 导出失败：{err}')
finally:
    conn.close()

After class exercises

Try the opposite: Batch import the data in the Excel file into the specified table in MySQL. (Tip: Useopenpyxlofiter_rowsRead Excel content and combinecursor.executemany()Implement efficient batch insertion. )