使用 Python 操作 MySQL 數(shù)據(jù)庫

1. Python 數(shù)據(jù)庫訪問接口

1.1 簡介

Python 所有的數(shù)據(jù)庫接口程序都在一定程度上遵守 Python DB-API 規(guī)范。Python DB-API 是一個規(guī)范，它定義了一系列必須的對象和數(shù)據(jù)庫存取方式，以便為各種各樣的底層數(shù)據(jù)庫系統(tǒng)和多種多樣的數(shù)據(jù)庫接口程序提供一致的訪問接口。

在沒有 Python DB-API 之前，各數(shù)據(jù)庫之間的應(yīng)用接口非?；靵y，實現(xiàn)各不相同。如果項目需要更換數(shù)據(jù)庫時，則需要做大量的修改，非常不便。Python DB-API 的出現(xiàn)就是為了解決這樣的問題。

由于 Python DB-API 為不同的數(shù)據(jù)庫提供了一致的訪問接口， 在不同的數(shù)據(jù)庫之間移植代碼成為一件輕松的事情。

1.2 connect() 方法

connect 方法生成一個 connect 對象, 通過這個對象來訪問數(shù)據(jù)庫。connect 的參數(shù)如下：

用戶以命名參數(shù)的方式打開數(shù)據(jù)庫，例如：

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

1.3 connect 對象

使用 connect() 方法與數(shù)據(jù)庫連接成功后，connect() 方法返回一個 connect() 對象。
與數(shù)據(jù)庫進(jìn)行通信時, 向 connect 對象發(fā)送 SQL 查詢命令, 并 connect 對象接收 SQL 查詢結(jié)果。

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

1.4 cursor 對象

cursor 對象用于執(zhí)行 SQL 命令和得到 SQL 查詢結(jié)果。cursor 對象提供如下方法：

2. 安裝 pymysql

pymysql 是 python 訪問 mysql 數(shù)據(jù)庫的模塊。首先檢查是否已經(jīng)安裝了 pymsql 模塊，在 python 交互模式下 import pymysql，如下所示：

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

如果出現(xiàn)錯誤：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. 創(chuàng)建數(shù)據(jù)庫

3.1 編寫數(shù)據(jù)庫腳本 db.sql

SET character_set_database=utf8;
SET character_set_server=utf8;

• 設(shè)置數(shù)據(jù)庫編碼為 utf8

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

• 如果已經(jīng)存在名稱為 school 的數(shù)據(jù)庫，則刪除
• 創(chuàng)建名稱為 school 的數(shù)據(jù)庫
• 使用名稱為 school 的數(shù)據(jù)庫

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

• 在數(shù)據(jù)庫 school 中創(chuàng)建表 students，表 students 包含 3 個字段：
  • sno，學(xué)號
  • name，姓名
  • age，年齡

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

• 向表 students 中插入兩條數(shù)據(jù)，用于測試

3.2 執(zhí)行數(shù)據(jù)庫腳本 db.sql

啟動 mysql 命令行，輸入 source db.sql，執(zhí)行 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. 連接數(shù)據(jù)庫

def open_conn():
    global conn
    global cursor

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

• 在第 1 行，定義函數(shù) open_conn 打開 mysql 數(shù)據(jù)庫
• 在第 2 行，聲明全局變量 conn
• 在第 3 行，聲明全局變量 cursor
• 在第 5 行，調(diào)用 connect 方法創(chuàng)建 connect 對象
• 在第 12 行，調(diào)用 cursor 方法創(chuàng)建 cursor 對象

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

• 在第 1 行，定義函數(shù) close_conn 關(guān)閉 mysql 數(shù)據(jù)庫
• 在第 3 行，關(guān)閉數(shù)據(jù)庫之前調(diào)用 commit() 方法，提交對數(shù)據(jù)庫的修改

5. 查詢數(shù)據(jù)

5.1 使用 fetchone 查詢數(shù)據(jù)

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 行，定義函數(shù) query_students_fetchone，使用 fetchone 方法查詢數(shù)據(jù)
• 在第 3 行，cursor.execute(sql) 返回查詢記錄的條數(shù)
• 在第 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 查詢數(shù)據(jù)

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 行，定義函數(shù) query_students_fetchall，使用 fetchall 方法查詢數(shù)據(jù)
• 在第 3 行，cursor.execute(sql) 返回查詢記錄的條數(shù)
• 在第 6 行，使用 fetchall() 獲取所有的查詢記錄

編寫測試程序如下：

open_conn()
query_students_fetchall()
close_conn()        

程序輸出如下：

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

6. 增加數(shù)據(jù)

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 行，定義函數(shù) add_student(sno, name, age)，向表 students 中插入一條數(shù)據(jù)
• 在第 3 行，cursor.execute(sql) 返回插入記錄的條數(shù)

編寫測試程序如下：

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. 修改數(shù)據(jù)

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 行，定義函數(shù) update_student(sno, name, age)，根據(jù)學(xué)生的 sno 修改 name 和 age
• 在第 3 行，cursor.execute(sql) 返回修改記錄的條數(shù)

編寫測試程序如下：

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. 刪除數(shù)據(jù)

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

• 在第 1 行，定義函數(shù) delete_student(sno)，刪除表 students 中學(xué)號為 sno 的數(shù)據(jù)
• 在第 3 行，cursor.execute(sql) 返回刪除記錄的條數(shù)

編寫測試程序如下：

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)