Python操作MySQL数据库实战：连接、增删改查与事务处理

远山来客

发布时间：2026-10-13 | 3人浏览过

原创

本文详解Python 3环境下使用PyMySQL连接MySQL数据库的完整流程。涵盖数据库创建、表结构查看、数据插入与查询操作，重点解析事务回滚机制对自增ID的影响，以及利用executemany实现高效批量数据插入的实战技巧。

一、数据库基本操作

1. 若需支持在数据库中存储中文字符，可在创建数据库时指定字符集，使用如下命令：

create database zcl charset utf8;

2. 查看students表的字段结构，命令如下：

desc students;

3. 查看创建students表的完整SQL语句，命令如下：

show create table students;

4. 删除名为zcl的数据库，命令如下：

drop database zcl;

5. 向students表中新增一个名为nal的字符型字段，命令如下：

alter table students add column nal char(64);　

PS: 本人是很讨厌上面这种“简单解释+代码”的博客。其实我当时在mysql终端写了很多的实例，不过因为当时电脑运行一个看视频的软件，导致我无法Ctrl+C/V。现在懒了哈哈~~

二、python连接数据库

Python 3版本已不再支持mysqldb模块，其官方推荐的替代模块为PyMySQL。本文示例基于Python 3.4环境编写。

1. 安装pymysql模块，命令如下：

pip3 install pymysql

2. 连接数据库并插入数据的完整实例如下：

import pymysql
#生成实例，连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
#插入数据
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Jack','man',25,1351234,"CN"))
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Mary','female',18,1341234,"USA"))
conn.commit() #实例提交命令 
cur.close()
conn.close()
print(reCount)

查看插入结果：

mysql> select* from students;
+----+------+-----+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+------+-----+-----+-------------+------+
| 1 | zcl | man | 22 | 15622341234 | NULL |
| 2 | alex | man | 30 | 15622341235 | NULL |
+----+------+-----+-----+-------------+------+
rows in set

3. 从数据库获取数据的代码如下：

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
cur = conn.cursor()
reCount = cur.execute('select* from students')
res = cur.fetchone() #获取一条数据
res2 = cur.fetchmany(3) #获取3条数据
res3 = cur.fetchall() #获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()
cur.close()
conn.close()

输出结果如下：

(1, 'zcl', 'man', 22, '15622341234', None)
((2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA'))
()

三、事务回滚

事务回滚操作需在数据正式写入数据库前执行，因此代码中conn.rollback()必须位于conn.commit()之前。只要数据尚未提交，即可执行回滚。但需注意，即使回滚成功，自增主键ID仍会递增。具体示例如下：

插入3条数据（注意事务回滚逻辑）：

import pymysql
#连接数据库zcl
conn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Jack', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)', ('Jack2', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Mary', 'female', 18, 1341234, "USA"))
conn.rollback() #事务回滚
conn.commit() #实例提交命令 
cur.close()
conn.close()
print(reCount)

对比执行命令前与执行命令后（包含回滚操作）的数据状态，注意ID号的变化：。未执行上述代码与执行上述代码的结果是一样的，因为事务已经回滚，故students表实际不会增加数据！

mysql> select* from students;
+----+------+--------+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+------+--------+-----+-------------+------+
| 1 | zcl | man | 22 | 15622341234 | NULL |
| 2 | alex | man | 30 | 15622341235 | NULL |
| 5 | Jack | man | 25 | 1351234 | CN |
| 6 | Mary | female | 18 | 1341234 | USA |
+----+------+--------+-----+-------------+------+
rows in set

若需提交数据（不包含回滚操作），只需将上述代码第11行（conn.rollback()）注释即可，执行结果如下：

mysql> select* from students;
+----+-------+--------+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+-------+--------+-----+-------------+------+
| 1 | zcl | man | 22 | 15622341234 | NULL |
| 2 | alex | man | 30 | 15622341235 | NULL |
| 5 | Jack | man | 25 | 1351234 | CN |
| 6 | Mary | female | 18 | 1341234 | USA |
| 10 | Jack | man | 25 | 1351234 | CN |
| 11 | Jack2 | man | 25 | 1351234 | CN |
| 12 | Mary | female | 18 | 1341234 | USA |
+----+-------+--------+-----+-------------+------+
rows in set

总结：虽然事务回滚了，但ID还是自增了，不会因回滚而取消，但这不影响数据的一致性（底层的原理我不清楚~）

四、批量插入数据

import pymysql
#连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
li = [
 ("cjy","man",18,1562234,"USA"),
 ("cjy2","man",18,1562235,"USA"),
 ("cjy3","man",18,1562235,"USA"),
 ("cjy4","man",18,1562235,"USA"),
 ("cjy5","man",18,1562235,"USA"),
]
#插入数据
reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', li)
#conn.rollback() #事务回滚
conn.commit() #实例提交命令
cur.close()
conn.close()
print(reCount)

在PyCharm环境下运行上述批量插入代码，输出结果为：5

在MySQL终端中查看插入后的数据表，显示如下：

mysql> select* from students; 　 #插入数据前
+----+-------+--------+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+-------+--------+-----+-------------+------+
| 1 | zcl | man | 22 | 15622341234 | NULL |
| 2 | alex | man | 30 | 15622341235 | NULL |
| 5 | Jack | man | 25 | 1351234 | CN |
| 6 | Mary | female | 18 | 1341234 | USA |
| 10 | Jack | man | 25 | 1351234 | CN |
| 11 | Jack2 | man | 25 | 1351234 | CN |
| 12 | Mary | female | 18 | 1341234 | USA |
+----+-------+--------+-----+-------------+------+
rows in set


mysql> 
mysql> select* from students;　　　#插入数据后
+----+-------+--------+-----+-------------+------+
| id | name | sex | age | tel | nal |
+----+-------+--------+-----+-------------+------+
| 1 | zcl | man | 22 | 15622341234 | NULL |
| 2 | alex | man | 30 | 15622341235 | NULL |
| 5 | Jack | man | 25 | 1351234 | CN |
| 6 | Mary | female | 18 | 1341234 | USA |
| 10 | Jack | man | 25 | 1351234 | CN |
| 11 | Jack2 | man | 25 | 1351234 | CN |
| 12 | Mary | female | 18 | 1341234 | USA |
| 13 | cjy | man | 18 | 1562234 | USA |
| 14 | cjy2 | man | 18 | 1562235 | USA |
| 15 | cjy3 | man | 18 | 1562235 | USA |
| 16 | cjy4 | man | 18 | 1562235 | USA |
| 17 | cjy5 | man | 18 | 1562235 | USA |
+----+-------+--------+-----+-------------+------+
rows in set

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持本站！