使用 Python 操作 MySQL 数据库

1. Python 数据库访问接口

1.1 简介

Python 所有的数据库接口程序都在一定程度上遵守 Python DB-API 规范。Python DB-API 是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。

在没有 Python DB-API 之前，各数据库之间的应用接口非常混乱，实现各不相同。如果项目需要更换数据库时，则需要做大量的修改，非常不便。Python DB-API 的出现就是为了解决这样的问题。

由于 Python DB-API 为不同的数据库提供了一致的访问接口，在不同的数据库之间移植代码成为一件轻松的事情。

1.2 connect() 方法

connect 方法生成一个 connect 对象, 通过这个对象来访问数据库。connect 的参数如下：

参数	功能
user	访问数据库的用户
password	访问数据库的密码
host	Mysql 数据库服务所在的主机
port	Mysql 数据库服务的端口号，默认值为 3306
db	数据库名
charset	字符编码

用户以命名参数的方式打开数据库，例如：

pymysql.connect(
    host = '192.168.168.168',
    port = 3306,
    user = 'root',
    password = 'mysql',
    db = 'school',
    charset = 'utf8'
)

1.3 connect 对象

使用 connect() 方法与数据库连接成功后，connect() 方法返回一个 connect() 对象。
与数据库进行通信时, 向 connect 对象发送 SQL 查询命令, 并 connect 对象接收 SQL 查询结果。

connect 对象提供了如下常用方法：

方法	功能
close()	关闭数据库连接
commit()	提交当前事务
rollback()	取消当前事务
cursor()	创建一个游标对象用于执行 SQL 查询命令

1.4 cursor 对象

cursor 对象用于执行 SQL 命令和得到 SQL 查询结果。cursor 对象提供如下方法：

方法	功能
close()	关闭游标对象
execute()	执行一个数据库查询或命令
fetchone()	返回结果集的下一行
fetchall()	返回结果集中所有行

2. 安装 pymysql

pymysql 是 python 访问 mysql 数据库的模块。首先检查是否已经安装了 pymsql 模块，在 python 交互模式下 import pymysql，如下所示：

>>> import pymysql
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'pymysql'

如果出现错误：ModuleNotFoundError，则表示还没有安装 pymysql，使用 pip3 install mysql 安装 pymysql，如下所示：

C:\Users\Administrator>pip3 install pymysql
Collecting pymysql
...
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3

3. 创建数据库

3.1 编写数据库脚本 db.sql

SET character_set_database=utf8;
SET character_set_server=utf8;

设置数据库编码为 utf8

DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;

如果已经存在名称为 school 的数据库，则删除
创建名称为 school 的数据库
使用名称为 school 的数据库

CREATE TABLE students(
    sno VARCHAR(32),
    name VARCHAR(32),
    age INT
);

在数据库 school 中创建表 students，表 students 包含 3 个字段：
- sno，学号
- name，姓名
- age，年龄

INSERT INTO students(sno, name, age) VALUES ('1', '张三', '20');
INSERT INTO students(sno, name, age) VALUES ('2', '李四', '21');

向表 students 中插入两条数据，用于测试

3.2 执行数据库脚本 db.sql

启动 mysql 命令行，输入 source db.sql，执行 db.sql 中的 SQL 命令，如下所示：

$ sudo mysql
mysql> source db.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)

4. 连接数据库

def open_conn():
    global conn
    global cursor

    conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        password = '',
        db = 'school',
        charset='utf8'
    )
    cursor = conn.cursor()

在第 1 行，定义函数 open_conn 打开 mysql 数据库
在第 2 行，声明全局变量 conn
在第 3 行，声明全局变量 cursor
在第 5 行，调用 connect 方法创建 connect 对象
在第 12 行，调用 cursor 方法创建 cursor 对象

def close_conn():
    cursor.close()
    conn.commit()
    conn.close()

在第 1 行，定义函数 close_conn 关闭 mysql 数据库
在第 3 行，关闭数据库之前调用 commit() 方法，提交对数据库的修改

5. 查询数据

5.1 使用 fetchone 查询数据

def query_students_fetchone():
    sql = 'SELECT * FROM students'
    rows = cursor.execute(sql)
    print('There are %d students' % rows)

    for i in range(rows):
        student = cursor.fetchone()
        print(student)

在第 1 行，定义函数 query_students_fetchone，使用 fetchone 方法查询数据
在第 3 行，cursor.execute(sql) 返回查询记录的条数
在第 7 行，使用 fetchone() 获取一条查询记录

编写测试程序如下：

open_conn()
query_students_fetchone()
close_conn()

程序输出如下：

There are 2 students
('1', '张三', 20)
('2', '李四', 21)

查询记录是一个元组
- 第 0 项是 sno
- 第 1 项是 name
- 第 2 项是 age

5.2 使用 fetchall 查询数据

def query_students_fetchall():
    sql = 'SELECT * FROM students'
    rows = cursor.execute(sql)
    print('There are %d students' % rows)

    students = cursor.fetchall() 
    for student in students:
        print(student)

在第 1 行，定义函数 query_students_fetchall，使用 fetchall 方法查询数据
在第 3 行，cursor.execute(sql) 返回查询记录的条数
在第 6 行，使用 fetchall() 获取所有的查询记录

编写测试程序如下：

open_conn()
query_students_fetchall()
close_conn()

程序输出如下：

There are 2 students
('1', '张三', 20)
('2', '李四', 21)

6. 增加数据

def add_student(sno, name, age):
    sql = 'INSERT INTO students(sno, name, age) VALUES("%s", "%s", %d)' % (sno, name, age)
    rows = cursor.execute(sql)
    print('Insert %d students' % rows)

在第 1 行，定义函数 add_student(sno, name, age)，向表 students 中插入一条数据
在第 3 行，cursor.execute(sql) 返回插入记录的条数

编写测试程序如下：

open_conn()
add_student('3', '王五', 18)
add_student('4', '刘六', 19)
query_students_fetchone()
close_conn()

程序输出如下：

Insert 1 students
Insert 1 students
There are 4 students
('1', '张三', 20)
('2', '李四', 21)
('3', '王五', 18)
('4', '刘六', 19)

7. 修改数据

def update_student(sno, name, age):
    sql = 'UPDATE students SET name="%s", age=%d WHERE sno="%s"' % (name, age, sno)
    rows = cursor.execute(sql)
    print('UPDATE %d students' % rows)

在第 1 行，定义函数 update_student(sno, name, age)，根据学生的 sno 修改 name 和 age
在第 3 行，cursor.execute(sql) 返回修改记录的条数

编写测试程序如下：

open_conn()
update_student('3', 'WangWu', 28)
update_student('4', 'LiuLiu', 29)
query_students_fetchone()
close_conn()

程序输出如下：

UPDATE 0 students
UPDATE 0 students
There are 4 students
('1', '张三', 20)
('2', '李四', 21)
('3', 'WangWu', 28)
('4', 'LiuLiu', 29)

8. 删除数据

def delete_student(sno):
    sql = 'DELETE FROM students WHERE sno="%s"' % (sno)
    rows = cursor.execute(sql)
    print('DELETE %d students' % rows)

在第 1 行，定义函数 delete_student(sno)，删除表 students 中学号为 sno 的数据
在第 3 行，cursor.execute(sql) 返回删除记录的条数

编写测试程序如下：

open_conn()
delete_student('3')
delete_student('4')
query_students_fetchone()
close_conn()

程序输出如下：

DELETE 1 students
DELETE 1 students
There are 2 students
('1', '张三', 20)
('2', '李四', 21)