Python3 中,我们可以使用mysqlclient
或者pymysql
三方库来接入 MySQL 数据库并实现数据持久化操作。二者的用法完全相同,只是导入的模块名不一样。我们推荐大家使用纯 Python 的三方库pymysql
,因为它更容易安装成功。
接入MySQL
在命令行或者 PyCharm 的终端中通过下面的命令安装pymysql
,如果需要接入 MySQL 8,还需要安装一个名为cryptography
的三方库来支持 MySQL 8 的密码认证方式。
pip install pymysql cryptography
步骤
使用pymysql
操作 MySQL 的步骤如下所示:
- 创建连接。MySQL 服务器启动后,提供了基于 TCP (传输控制协议)的网络服务。我们可以通过
pymysql
模块的connect
函数连接 MySQL 服务器。在调用connect
函数时,需要指定主机(host
)、端口(port
)、用户名(user
)、口令(password
)、数据库(database
)、字符集(charset
)等参数,该函数会返回一个Connection
对象。 - 获取游标。连接 MySQL 服务器成功后,接下来要做的就是向数据库服务器发送 SQL 语句,MySQL 会执行接收到的 SQL 并将执行结果通过网络返回。要实现这项操作,需要先通过连接对象的
cursor
方法获取游标(Cursor
)对象。 - 发出 SQL。通过游标对象的
execute
方法,我们可以向数据库发出 SQL 语句。 - 如果执行
insert
、delete
或update
操作,需要根据实际情况提交或回滚事务。因为创建连接时,默认开启了事务环境,在操作完成后,需要使用连接对象的commit
或rollback
方法,实现事务的提交或回滚,rollback
方法通常会放在异常捕获代码块except
中。如果执行select
操作,需要通过游标对象抓取查询的结果,对应的方法有三个,分别是:fetchone
、fetchmany
和fetchall
。其中fetchone
方法会抓取到一条记录,并以元组或字典的方式返回;fetchmany
和fetchall
方法会抓取到多条记录,以嵌套元组或列表装字典的方式返回。 - 关闭连接。在完成持久化操作后,请不要忘记关闭连接,释放外部资源。我们通常会在
finally
代码块中使用连接对象的close
方法来关闭连接。
示例
建库
CREATE TABLE `book` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '图书名称',
`author` varchar(10) DEFAULT NULL COMMENT '作者',
`price` decimal(10,0) DEFAULT NULL COMMENT '价格',
`publish_date` datetime DEFAULT NULL COMMENT '上架日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
insert 插入数据
"""
插入数据
"""
import pymysql
import datetime
name = input("请输入书名:")
auth = input("请输入作者:")
price = float(input("请输入价格:"))
nowTime = datetime.datetime.now()
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'INSERT INTO book (name,author,price,publish_date) VALUES (%s, %s, %s, %s)',
(name, auth, price, nowTime)
)
if affected_rows == 1:
print('添加图书成功!!!')
# 4. 提交事务(transaction)
conn.commit()
except pymysql.MySQLError as err:
# 4. 回滚事务
conn.rollback()
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
如果要插入大量数据,建议使用游标对象的executemany
方法做批处理(一个insert
操作后面跟上多组数据),大家可以尝试向一张表插入10000条记录,然后看看不使用批处理一条条的插入和使用批处理有什么差别。游标对象的executemany
方法第一个参数仍然是 SQL 语句,第二个参数可以是包含多组数据的列表或元组。
delete 删除数据
"""
删除数据
"""
import pymysql
id = int(input('输入图书id: '))
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'delete from book where `id`= %s',
(id)
)
if affected_rows == 1:
print('删除图书成功!!!')
# 4. 提交事务
conn.commit()
finally:
# 5. 关闭连接释放资源
conn.close()
说明:如果不希望每次 SQL 操作之后手动提交或回滚事务,可以
connect
函数中加一个名为autocommit
的参数并将它的值设置为True
,表示每次执行 SQL 成功后自动提交。但是我们建议大家手动提交或回滚,这样可以根据实际业务需要来构造事务环境。如果不愿意捕获异常并进行处理,可以在try
代码块后直接跟finally
块,省略except
意味着发生异常时,代码会直接崩溃并将异常栈显示在终端中。
update 修改数据
"""
修改数据
"""
import pymysql
id = input('请输入图书ID: ')
name = input('请输入书名: ')
author = input('请输入作者: ')
price = float(input('请输入价格: '))
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'update book set `name`=%s, `author`=%s, price = %s where `id`=%s',
(name, author, price, id)
)
if affected_rows == 1:
print('图书信息更新成功!!!')
# 4. 提交事务
conn.commit()
except pymysql.MySQLError as err:
# 4. 回滚事务
conn.rollback()
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
select 查询数据
"""
查询数据
"""
import pymysql
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
cursor.execute('select name,author,price from book')
# 4. 通过游标对象抓取数据
row = cursor.fetchone()
while row:
print(row)
row = cursor.fetchone()
except pymysql.MySQLError as err:
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
说明:上面的代码中,我们通过构造一个while
循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。因为如果使用fetchall
一次性将所有记录抓取到一个嵌套元组中,会造成非常大的内存开销,这在很多场景下并不是一个好主意。如果不愿意使用while
循环,还可以考虑使用iter
函数构造一个迭代器来逐行抓取数据,有兴趣的读者可以自行研究。
分页查询
"""
分页查询
"""
import pymysql
page = int(input('页码: '))
size = int(input('大小: '))
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
cursor.execute(
'select name,author,price from book limit %s,%s',
((page - 1) * size, size)
)
# 4. 通过游标对象抓取数据
for emp_dict in cursor.fetchall():
print(emp_dict)
finally:
# 5. 关闭连接释放资源
conn.close()
数据库数据导出到Excel
import openpyxl
import pymysql
# 创建工作簿对象
workbook = openpyxl.Workbook()
# 获得默认的工作表
sheet = workbook.active
# 修改工作表的标题
sheet.title = '员工基本信息'
# 给工作表添加表头
sheet.append(('ID','书名', '作者', '价格', '日期'))
# 1. 创建连接(Connection)
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='cj123456789',
database='test', charset='utf8mb4')
try:
# 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 通过游标对象执行SQL语句
cursor.execute(
'select id,name,author,price,publish_date from book'
)
# 通过游标抓取数据
row = cursor.fetchone()
while row:
# 将数据逐行写入工作表中
sheet.append(row)
row = cursor.fetchone()
# 保存工作簿
workbook.save('books.xlsx')
except pymysql.MySQLError as err:
print(err)
finally:
# 关闭连接释放资源
conn.close()