python操作mysql

前言
在 Python3 中，我们可以使用mysqlclient或者pymysql三方库来接入 MySQL 数据库并实现数据持久化操作。二者的用法完全相同，只是导入的模块名不一样。我们推荐大家使用纯 Python 的三方库pymysql，因为它更容易安装成功。下面我们仍然以之前创建的名为hrs的数据库为例，为大家演示如何通过 Python 程序操作 MySQL 数据库实现数据持久化操作。

接入MySQL
首先，我们可以在命令行或者 PyCharm 的终端中通过下面的命令安装pymysql，如果需要接入 MySQL 8，还需要安装一个名为cryptography的三方库来支持 MySQL 8 的密码认证方式。

pip install pymysql cryptography

主要流程：
创建链接—>获取游标---->发出sql---->结果执行---->关闭连接
下面进入代码实战
插入操作

"""
上面的代码中，我们通过构造一个while循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。
因为如果使用fetchall一次性将所有记录抓取到一个嵌套元组中，会造成非常大的内存开销，这在很多场景下并不是一个好主意。
如果不愿意使用while循环，还可以考虑使用iter函数构造一个迭代器来逐行抓取数据，
"""
import pymysql# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4')
try:# 2. 获取游标对象（Cursor）with conn.cursor() as cursor:# 3. 通过游标对象向数据库服务器发出SQL语句cursor.execute('select `no`, `name`, `location` from `tb_dept`')# 4. 通过游标对象抓取数据row = cursor.fetchone()while row:print(row)row = cursor.fetchone()
except pymysql.MySQLError as err:print(type(err), err)
finally:# 5. 关闭连接释放资源conn.close()

批量插入

import pymysql# 提示用户输入多条部门信息
data_list = []
while True:no = input('请输入部门编号（输入q结束）: ')if no.lower() == 'q':breakname = input('请输入部门名称: ')location = input('请输入部门所在地: ')# 将输入的数据存储为元组并添加到列表中data_list.append((int(no), name, location))# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4')try:# 2. 获取游标对象（Cursor）with conn.cursor() as cursor:# 3. 通过游标对象向数据库服务器发出批量插入的SQL语句affected_rows = cursor.executemany('insert into `tb_dept` values (%s, %s, %s)',data_list)if affected_rows == len(data_list):print('批量新增部门成功，共插入 {} 条数据！'.format(affected_rows))# 4. 提交事务（transaction）conn.commit()except pymysql.MySQLError as err:# 4. 回滚事务conn.rollback()print(type(err), err)
finally:# 5. 关闭连接释放资源conn.close()

删除操作

import pymysqlno = int(input('部门编号: '))# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4',autocommit=True)
try:# 2. 获取游标对象（Cursor）with conn.cursor() as cursor:# 3. 通过游标对象向数据库服务器发出SQL语句affected_rows = cursor.execute('delete from `tb_dept` where `no`=%s',(no, ))if affected_rows == 1:print('删除部门成功!!!')
finally:# 5. 关闭连接释放资源conn.close()

修改操作

import pymysqlno = int(input('部门编号: '))
name = input('部门名称: ')
location = input('部门所在地: ')# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4')
try:# 2. 获取游标对象（Cursor）with conn.cursor() as cursor:# 3. 通过游标对象向数据库服务器发出SQL语句affected_rows = cursor.execute('update `tb_dept` set `name`=%s, `location`=%s where `no`=%s',(name, location, no))if affected_rows == 1:print('更新部门信息成功!!!')# 4. 提交事务conn.commit()
except pymysql.MySQLError as err:# 4. 回滚事务conn.rollback()print(type(err), err)
finally:# 5. 关闭连接释放资源conn.close()

查询操作

"""
上面的代码中，我们通过构造一个while循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。
因为如果使用fetchall一次性将所有记录抓取到一个嵌套元组中，会造成非常大的内存开销，这在很多场景下并不是一个好主意。
如果不愿意使用while循环，还可以考虑使用iter函数构造一个迭代器来逐行抓取数据，
"""
import pymysql# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4')
try:# 2. 获取游标对象（Cursor）with conn.cursor() as cursor:# 3. 通过游标对象向数据库服务器发出SQL语句cursor.execute('select `no`, `name`, `location` from `tb_dept`')# 4. 通过游标对象抓取数据row = cursor.fetchone()while row:print(row)row = cursor.fetchone()
except pymysql.MySQLError as err:print(type(err), err)
finally:# 5. 关闭连接释放资源conn.close()

分页查询

import pymysqlpage = int(input('页码: '))
size = int(input('大小: '))# 1. 创建连接（Connection）
con = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8')
try:# 2. 获取游标对象（Cursor）with con.cursor(pymysql.cursors.DictCursor) as cursor:# 3. 通过游标对象向数据库服务器发出SQL语句cursor.execute('select `no`, `name`, `location` from `tb_dept` order by `no` desc limit %s,%s',((page - 1) * size, size))# 4. 通过游标对象抓取数据for emp_dict in cursor.fetchall():print(emp_dict)
finally:# 5. 关闭连接释放资源con.close()

将数据库数据导入到excel中

import openpyxl
import pymysql# 创建工作簿对象
workbook = openpyxl.Workbook()
# 获得默认的工作表
sheet = workbook.active
# 修改工作表的标题
sheet.title = '员工基本信息'
# 给工作表添加表头
sheet.append(('工号', '姓名', '地址'))
# 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='123456',database='db2', charset='utf8mb4')
try:# 获取游标对象（Cursor）with conn.cursor() as cursor:# 通过游标对象执行SQL语句cursor.execute('select `no`, `name`, `location` ''from `tb_dept`')# 通过游标抓取数据row = cursor.fetchone()while row:# 将数据逐行写入工作表中sheet.append(row)row = cursor.fetchone()# 保存工作簿workbook.save('hrs.xlsx')
except pymysql.MySQLError as err:print(err)
finally:# 关闭连接释放资源conn.close()