[Python进阶] Python操作MySQL数据库：pymysql

7.7 操作MySQL数据库：pymysql

7.7.1 准备工作(创建mysql数据库)

PHPStudy介绍：
phpstudy是一款非常有用的PHP开发工具，旨在帮助开发者更加便捷地进行PHP程序的开发与调试。它提供了一个友好的图形用户界面，使得用户能够方便地进行配置和管理本地的PHP开发环境。
phpstudy还提供了一些实用的功能和工具，帮助开发者更加高效地编写和调试PHP程序。它包含了代码编辑器，用户可以方便地编写和编辑PHP代码。并且，phpstudy还内置了调试工具，可以帮助开发者定位和解决程序中的Bug。此外，phpstudy还提供了数据库管理工具，使得开发者能够方便地管理和操作MySQL数据库。
总之，phpstudy是一款非常有用的PHP开发工具。它提供了快速搭建开发环境的能力，并且提供了许多实用的功能和工具，帮助开发者更加高效地编写和调试PHP程序。
Navicat Premium介绍：
Navicat Premium是一款数据库管理工具，它提供了方便快捷的数据库管理和开发功能，支持多种数据库类型，包括MySQL、PostgreSQL、Oracle等等。
Navicat Premium具有直观的图形用户界面和强大的功能，可以帮助用户轻松地创建、编辑和管理数据库，同时提供了数据导入、导出、同步和备份功能，使得数据的处理更加高效和灵活。
此外，Navicat Premium还支持多种语言，可以轻松适应不同地区和领域的需求。总的来说，Navicat Premium是一款功能强大、易用可靠的数据库管理工具。
下载安装并启动MySQL数据库：
1、进入PHPStudy官网：https://www.xp.cn/download.html
2、下载PHPStudy并安装
3、启动PHPStudy中首页，点击启动MySQL，如下图：

4、此时，PHPStudy已经自动创建了MySQL数据库，用户和密码可以在数据库页面查看。如下图：

5、下载并安装Navicat Premium（MySQL数据库可视化操作工具）
6、安装pymysql库：

pip install pymysql

7、通过Navicat Premium连接刚才创建的数据库，创造一些测试表和数据进去。

7.7.2 用Python操作MySQL数据库

7.7.2.1 连接数据库

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
cursor.execute("SELECT VERSION()")  # 执行查询
# 获取查询结果
print(f"Database version : {cursor.fetchone()[0]}")
db.close()  # 关闭连接

7.7.2.2 创建表

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
cursor.execute('DROP TABLE IF EXISTS employee')  # 使用execute()方法执行SQL，如果表存在则将其删除
# 使用预处理语句创建表
sql = """CREATE TABLE `employee` (
  `first_name` varchar(255) DEFAULT NULL COMMENT '姓',
  `last_name` varchar(255) DEFAULT NULL COMMENT '名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(255) DEFAULT NULL COMMENT '性别',
  `income` varchar(255) DEFAULT NULL COMMENT '收入'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
cursor.execute(sql)  # 执行SQL语句
db.close()  # 关闭数据库连接

7.7.2.3 表_插入数据

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
sql = "INSERT INTO employee(first_name, last_name, age, sex, income) VALUES ('张', '三', 38, '女', 15000)"  # SQL语句：向数据表中插入数据
try:
    cursor.execute(sql)  # 执行SQL语句
    db.commit()  # 提交事务到数据库执行
except:
    db.rollback()  # 如果发生错误则执行回滚操作
db.close()  # 关闭数据库连接

7.7.2.4 表_查询数据

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
sql = "SELECT * FROM employee WHERE income > 1000 "  # SQL语句：查询
try:
    cursor.execute(sql)  # 执行SQL语句
    results = cursor.fetchall()  # 获取所有的记录列表
    for row in results:
        print(row)  # 打印列表元素
        first_name = row[0]  # 姓
        last_name = row[1]  # 名
        age = row[2]  # 年龄
        sex = row[3]  # 性别
        income = row[4]  # 收入
        print(first_name, last_name, age, sex, income)  # 打印列表元素
except:
print('无法获取到数据！')
db.close()  # 关闭数据库连接

7.7.2.5 表_修改数据

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
sql = 'UPDATE employee SET age = age + 5 WHERE sex = "女"'  # SQL语句，执行更新操作
try:
    cursor.execute(sql)  # 执行SQL语句
    db.commit()  # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
db.close()  # 关闭数据库连接

7.7.2.6 表_删除数据

import pymysql

db = pymysql.connect(host="localhost", user="root", password="root", database="mysql")  # 连接
cursor = db.cursor()  # 创建游标
sql = 'DELETE FROM employee WHERE income > 5000 AND age >35'  # SQL语句，执行删除操作
try:
    cursor.execute(sql)  # 执行SQL语句
    db.commit()  # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
db.close()  # 关闭数据库连接

7.7.3 问题及报错

报错1：使用Navicat连接数据库后发现看不到需要操作的数据库或者使用Python连接数据库的时候报错：Access denied for user ‘root’@‘%’ to database ‘netai_test’
原因：这是由于创建数据库后没有对用户授权，使用户可以访问数据库的原因。但是本地访问可以。
处理：打开Navicat，创建以root用户的连接，然后在要操作的数据库中新建查询，执行如下语句：grant all on (数据库名).* to ‘(用户)’@‘%’ identified by ‘(用户密码)’ with grant option;