亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

朱廣蔚 · 更新于 2020-06-12

使用 Python 操作 MySQL 數據庫

1. Python 數據庫訪問接口

1.1 簡介

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

在沒有 Python DB-API 之前，各數據庫之間的應用接口非?；靵y，實現各不相同。如果項目需要更換數據庫時，則需要做大量的修改，非常不便。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)