python介面自動化（三十八）-python操作mysql資料庫（詳解）

巨集哥發表於2019-05-22
PythonMySql資料庫

簡介

　　現在的招聘要求對QA人員的要求越來越高，測試的一些基礎知識就不必說了，來說測試知識以外的，會不會一門或者多門開發與語言，能不能讀懂程式碼，會不會Linux，會不會搭建測試系統，會不會常用的資料庫，會不會SQL等等，因此我們這篇文章來講解如何用

python語言操作mysql資料庫。

 本科階段曾學過使用java對MySQL資料庫進行操作，基本思路是先連線資料庫，然後執行SQL語句對資料庫進行操作，最後列印結果並斷開連線。使用Python運算元據庫的流程和以上過程基本一致，在對其進行介紹之前，先介紹一些基本的概念。

基本概念

python運算元據庫的流程

以流程圖的方式展示python操作MySQL資料庫的流程：

 

　　對上圖的解讀：首先檢查是否依次建立Connection物件（資料庫連線物件）用於開啟資料庫連線，建立Cursor物件（遊標物件）用於執行查詢和獲取結果；然後執行SQL語句對資料庫進行增刪改查等操作並提交事務，此過程如果出現異常則使用回滾技術使資料庫恢

復到執行SQL語句之前的狀態；最後，依次銷燬Cursor物件和Connection物件，以避免多計算機記憶體過多的佔用和浪費。

　　下面依次對Connection物件、Cursor物件和事務等概念進行介紹。

Connection物件

Connection物件即為資料庫連線物件，在python中可以使用pymysql.connect()方法建立Connection物件，該方法的常用引數如下：

host：連線的資料庫伺服器主機名，預設為本地主機（localhost）；字串型別（String） 。

user：使用者名稱，預設為當前使用者；字串型別（String） 。

passwd：密碼，無預設值；字串類 （String）。

db：資料庫名稱，無預設值；字串型別（String） 。

port：指定資料庫伺服器的連線埠，預設為3306；整型（int）。

charset：連線字符集；字串型別（String）

Connection物件常用的方法如下：

cursor()：使用當前連線建立並返回遊標 。

commit()：提交當前事務 。

rollback()：回滾當前事務 。

close()：關閉當前連線

Cursor物件

Cursor物件即為遊標物件，用於執行查詢和獲取結果，在python中可以使用conn.cursor()建立，conn為Connection物件。Cursor物件常用的方法和屬性如下：

execute()：執行資料庫查詢或命令，將結果從資料庫獲取到客戶端 fetchone()：獲取結果集的下一行 fetchmany()：獲取結果集的下幾行 fetchall()：獲取結果集中剩下的所有行 close()：關閉當前遊標物件 rowcount：最近一次的execute返回資料的行數或受影響的行數

事務

1、事務機制可以確保資料一致性。

事務是資料庫理論中一個比較重要的概念，指訪問和更新資料庫的一個程式執行單元，具有ACID特性：

  • 原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
  • 一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
  • 隔離性（isolation）。一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。
  • 永續性（durability）。持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

在開發時，我們以以下三種方式使用事務：

正常結束事務：conn.commit() 異常結束事務：conn.rollback() 關閉自動commit：設定 conn.autocommit(False)

2 、使用python實現對MySQL資料庫的增刪改查等操作

在python中操作MySQL資料庫時，要使用的模組是：

Python2中：mysqldb（pip2 install mysqldb） Python3中：pymysql（pip3 install pymysql）

本篇部落格所使用的環境為：

python 3.7 win 10 pycharm 2018.3.5 pymysql 0.9.2 mysql 5.6 

下面將以具體程式碼的形式依次介紹python中如何實現對MySQL資料庫的增刪改查等操作。

python運算元據庫

Python 標準資料庫介面為 Python DB-API，Python DB-API為開發人員提供了資料庫應用程式設計介面。

Python 資料庫介面支援非常多的資料庫，你可以選擇適合你專案的資料庫：

  • GadFly
  • mSQL
  • MySQL（小公司、小廠首選）
  • PostgreSQL
  • Microsoft SQL Server 2008（中型廠、中等公司首選）
  • Informix
  • Interbase
  • Oracle（大廠、大企業首選）
  • Sybase

你可以訪問Python資料庫介面及API檢視詳細的支援資料庫列表。

不同的資料庫你需要下載不同的DB API模組，例如你需要訪問Oracle資料庫和Mysql資料，你需要下載Oracle和MySQL資料庫模組。

DB-API 是一個規範. 它定義了一系列必須的物件和資料庫存取方式, 以便為各種各樣的底層資料庫系統和多種多樣的資料庫介面程式提供一致的訪問介面 。

Python的DB-API，為大多數的資料庫實現了介面，使用它連線各資料庫後，就可以用相同的方式操作各資料庫。

Python DB-API使用流程：

  • 引入 API 模組。
  • 獲取與資料庫的連線。
  • 執行SQL語句和儲存過程。
  • 關閉資料庫連線。

什麼是MySQLdb?

PyMySQL 是在 Python3.x 版本中用於連線 MySQL 伺服器的一個庫，Python2中則使用mysqldb。

PyMySQL 遵循 Python 資料庫 API v2.0 規範，幷包含了 pure-Python MySQL 客戶端庫。

如何安裝MySQLdb?

在使用 PyMySQL 之前，我們需要確保 PyMySQL 已安裝。

PyMySQL 下載地址：https://github.com/PyMySQL/PyMySQL。

如果還未安裝，我們可以使用以下命令安裝最新版的 PyMySQL：

1 pip install PyMySQL

 

如果你的系統不支援 pip 命令，可以使用以下方式安裝：

1、使用 git 命令下載安裝包安裝(你也可以手動下載)：

1 git clone https://github.com/PyMySQL/PyMySQL
2 cd PyMySQL/
3 python3 setup.py install

2、如果需要制定版本號，可以使用 curl 命令來安裝：

1 # X.X 為 PyMySQL 的版本號
2 url -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
3 cd PyMySQL*
4 python3 setup.py install
5 # 現在你可以刪除 PyMySQL* 目錄

注意：請確保您有root許可權來安裝上述模組。

安裝的過程中可能會出現"ImportError: No module named setuptools"的錯誤提示，意思是你沒有安裝setuptools，你可以訪問https://pypi.python.org/pypi/setuptools 找到各個系統的安裝方法。

Linux 系統安裝例項：

1 $ wget https://bootstrap.pypa.io/ez_setup.py
2 $ python3 ez_setup.py

資料庫連線

連線資料庫前，請先確認以下事項：

  • 您已經建立了資料庫 TESTDB（測試資料庫）
  • 在TESTDB資料庫中您已經建立了表 EMPLOYEE（測試用的測試表）
  • EMPLOYEE表欄位為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
  • 連線資料庫TESTDB（測試資料庫）使用的使用者名稱為 "testuser" ，密碼為 "test123",你可以可以自己設定或者直接使用root使用者名稱及其密碼，Mysql資料庫使用者授權請使用Grant命令。
  • 在你的機子上已經安裝了 Python MySQLdb 模組。
  • 如果你對sql語句不熟悉，可以自己先簡單的學習一下

1、檢視有沒有建立資料庫TESTDB（測試資料庫），開啟MySQL的client介面輸入密碼 後，輸入命令：

1 show databases;

2、看到沒有那個資料庫，那我們就開始建立一個TESTDB資料庫，輸入命令：

1 create database TESTDB;

3、再次輸入第一步的命令檢視，看到已經成功建立

 

4、檢視TESTDB資料庫裡，有沒有EMPLOYEE（測試用的測試表），輸入命令：

1 use testdb;
2 show tables;

5、檢視到在TESTDB資料庫中沒有EMPLOYEE表，那麼沒有我們就建立一張EMPLOYEE表即可。

建立一個例項

執行結果：（從結果中我們可以看見成功建立了一個Connection和Cursor物件。）

下面將以具體程式碼的形式依次介紹python中如何實現對MySQL資料庫的增刪改查等操作。

例項：

1、連線資料庫

執行結果：

參考程式碼：

 1 # coding=utf-8
 2 # 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行
 3 
 4 # 2.註釋：包括記錄建立時間，建立人，專案名稱。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-巨集哥
 8 Project:學習和使用python操作MySQL資料庫
 9 '''
10 # 3.匯入模組
11 import pymysql
12 
13 import pymysql
14 
15 # 開啟資料庫連線
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法建立一個遊標物件
19 cursor = conn.cursor()
20 
21 # 使用execute()方法執行SQL查詢
22 cursor.execute('SELECT VERSION()')
23 
24 # 使用fetchone()方法獲取單條資料
25 data = cursor.fetchone()
26 
27 # 列印
28 print('database version: %s' % data)
29 
30 # 關閉資料庫連線
31 conn.close()

2、建立資料庫表

如果資料庫連線存在我們可以使用execute()方法來為資料庫建立表，如下所示建立表EMPLOYEE：

執行程式碼後檢視有沒有表建立，輸入命令：

1 use TESTDB；
2 show tables;

執行結果：

參考程式碼：

 1 # coding=utf-8
 2 # 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行
 3 
 4 # 2.註釋：包括記錄建立時間，建立人，專案名稱。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-巨集哥
 8 Project:學習和使用python操作MySQL資料庫
 9 '''
10 # 3.匯入模組
11 import pymysql
12 
13 import pymysql
14 
15 # 開啟資料庫連線
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法建立一個遊標物件cursor
19 cursor = conn.cursor()  # 遊標物件用於執行查詢和獲取結果
20 
21 # 使用execute()方法執行SQL，如果表存在則將其刪除
22 cursor.execute('DROP TABLE IF EXISTS EMPLOYEE')
23 
24 # 使用預處理語句建立表
25 sql = """CREATE TABLE `employee` (
26   `first_name` varchar(255) DEFAULT NULL COMMENT '姓',
27   `last_name` varchar(255) DEFAULT NULL COMMENT '名',
28   `age` int(11) DEFAULT NULL COMMENT '年齡',
29   `sex` varchar(255) DEFAULT NULL COMMENT '性別',
30   `income` varchar(255) DEFAULT NULL COMMENT '收入'
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
32 """
33 
34 # 執行SQL語句
35 cursor.execute(sql)
36 
37 # 關閉資料庫連線
38 conn.close()

3、資料庫插入操作

以下例項使用執行 SQL INSERT 語句向表 EMPLOYEE 插入記錄：

1、首先查詢有沒有記錄輸入查詢語句：

1 select * from employee;

2、程式碼實現

執行結果：

再次檢視有沒有記錄，輸入第一步的sql語句：（可以看到插入一條記錄）

參考程式碼：

# coding=utf-8
# 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行

# 2.註釋：包括記錄建立時間，建立人，專案名稱。
'''
Created on 2019-5-22
@author: 北京-巨集哥
Project:學習和使用python操作MySQL資料庫
'''
# 3.匯入模組
import pymysql

import pymysql

# 開啟資料庫連線
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')

# 使用cursor()方法獲取操作遊標
cursor = conn.cursor()

# SQL語句：向資料表中插入資料
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

# 異常處理
try:
    # 執行SQL語句
    cursor.execute(sql)
    # 提交事務到資料庫執行
    conn.commit()  # 事務是訪問和更新資料庫的一個程式執行單元
except:
    # 如果發生錯誤則執行回滾操作
    conn.rollback()

# 關閉資料庫連線
conn.close()

另一種寫法：

# coding=utf-8
# 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行

# 2.註釋：包括記錄建立時間，建立人，專案名稱。
'''
Created on 2019-5-22
@author: 北京-巨集哥
Project:學習和使用python操作MySQL資料庫
'''
# 3.匯入模組
import pymysql

import pymysql

# 開啟資料庫連線
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')

# 使用cursor()方法獲取操作遊標
cursor = conn.cursor()

# SQL語句：向資料表中插入資料
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
# 異常處理
try:
    # 執行SQL語句
    cursor.execute(sql)
    # 提交事務到資料庫執行
    conn.commit()  # 事務是訪問和更新資料庫的一個程式執行單元
except:
    # 如果發生錯誤則執行回滾操作
    conn.rollback()

# 關閉資料庫連線
conn.close()

例項：

以下程式碼使用變數向SQL語句中傳遞引數:

1 ..................................
2 user_id = "root"
3 password = "root"
4 
5 con.execute('insert into Login values( %s,  %s)' % \
6              (user_id, password))
7 ..................................

3、資料庫查詢操作

Python查詢Mysql使用 fetchone() 方法獲取單條資料, 使用fetchall() 方法獲取多條資料。

  • fetchone(): 該方法獲取下一個查詢結果集。結果集是一個物件
  • fetchall():接收全部的返回結果行.
  • rowcount: 這是一個只讀屬性，並返回執行execute()方法後影響的行數。

例項：

查詢EMPLOYEE表中salary（工資）欄位大於1000的所有資料：

執行結果：

參考程式碼：

 1 # coding=utf-8
 2 # 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行
 3 
 4 # 2.註釋：包括記錄建立時間，建立人，專案名稱。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-巨集哥
 8 Project:學習和使用python操作MySQL資料庫
 9 '''
10 # 3.匯入模組
11 import pymysql
12 
13 import pymysql
14 
15 # 開啟資料庫連線
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法獲取操作遊標
19 cursor = conn.cursor()
20 
21 # SQL語句：查詢
22 sql = "SELECT * FROM employee WHERE income > 1000 "
23 
24 # 異常處理
25 try:
26     # 執行SQL語句
27     cursor.execute(sql)
28     # 獲取所有的記錄列表
29     results = cursor.fetchall()
30     # 遍歷列表
31     for row in results:
32         # 列印列表元素
33         print(row)
34         # 姓
35         first_name = row[0]
36         # 名
37         last_name = row[1]
38         # 年齡
39         age = row[2]
40         # 性別
41         sex = row[3]
42         # 收入
43         income = row[4]
44         # 列印列表元素
45         print(first_name, last_name, age, sex, income)
46 except:
47     print('Uable to fetch data!')
48 
49 # 關閉資料庫連線
50 conn.close()

4、資料庫更新操作

更新操作用於更新資料表的的資料，以下例項將 EMPLOYEE 表中的 SEX 欄位為 'M' 的 AGE 欄位遞增 5：

1、以前age是20，

2、程式碼實現：

3、執行結果：

4、查詢age變更到25了沒有

5、參考程式碼：

# coding=utf-8
# 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行

# 2.註釋：包括記錄建立時間，建立人，專案名稱。
'''
Created on 2019-5-22
@author: 北京-巨集哥
Project:學習和使用python操作MySQL資料庫
'''
# 3.匯入模組
import pymysql

import pymysql

# 開啟資料庫連線
conn = pymysql.connect('localhost', 'root', 'root', 'testdb')

# 使用cursor()方法獲取操作遊標
cursor = conn.cursor()

# SQL語句，執行更新操作
sql = 'UPDATE employee SET age = age + 5 WHERE sex = "M"'

# 異常處理
try:
    # 執行SQL語句
    cursor.execute(sql)
    # 提交到資料庫執行
    conn.commit()
except:
    # 發生錯誤時回滾
    conn.rollback()

# 關閉資料庫連線
conn.close()

5、刪除操作

刪除操作用於刪除資料表中的資料，以下例項演示了刪除資料表 EMPLOYEE 中 AGE 大於 20 的所有資料：

1、大於20的只有一條，刪除了就沒有記錄了，輸入

1  select * from employee;

2、程式碼實現

 

3、執行結果

4、檢視資料庫表，重複第一步

 

5、參考程式碼

 1 # coding=utf-8
 2 # 1.先設定編碼，utf-8可支援中英文，如上，一般放在第一行
 3 
 4 # 2.註釋：包括記錄建立時間，建立人，專案名稱。
 5 '''
 6 Created on 2019-5-22
 7 @author: 北京-巨集哥
 8 Project:學習和使用python操作MySQL資料庫
 9 '''
10 # 3.匯入模組
11 import pymysql
12 
13 import pymysql
14 
15 # 開啟資料庫連線
16 conn = pymysql.connect('localhost', 'root', 'root', 'testdb')
17 
18 # 使用cursor()方法獲取操作遊標
19 cursor = conn.cursor()
20 
21 # SQL語句，執行刪除操作
22 sql = 'DELETE FROM employee WHERE age >20'
23 
24 # 異常處理
25 try:
26     # 執行SQL語句
27     cursor.execute(sql)
28     # 提交到資料庫執行
29     conn.commit()
30 except:
31     # 發生錯誤時回滾
32     conn.rollback()
33 
34 # 關閉資料庫連線
35 conn.close()

執行事務

事務機制可以確保資料一致性。

事務應該具有4個屬性：原子性、一致性、隔離性、永續性。這四個屬性通常稱為ACID特性。

  • 原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
  • 一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
  • 隔離性（isolation）。一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。
  • 永續性（durability）。持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

Python DB API 2.0 的事務提供了兩個方法 commit 或 rollback。

例項：

 1 # SQL刪除記錄語句
 2 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
 3 try:
 4    # 執行SQL語句
 5    cursor.execute(sql)
 6    # 向資料庫提交
 7    db.commit()
 8 except:
 9    # 發生錯誤時回滾
10    db.rollback()

對於支援事務的資料庫， 在Python資料庫程式設計中，當遊標建立之時，就自動開始了一個隱形的資料庫事務。

commit()方法遊標的所有更新操作，rollback（）方法回滾當前遊標的所有操作。每一個方法都開始了一個新的事務。

錯誤處理

DB API中定義了一些資料庫操作的錯誤及異常，下表列出了這些錯誤和異常:

異常描述
Warning 當有嚴重警告時觸發，例如插入資料是被截斷等等。必須是 StandardError 的子類。
Error 警告以外所有其他錯誤類。必須是 StandardError 的子類。
InterfaceError 當有資料庫介面模組本身的錯誤（而不是資料庫的錯誤）發生時觸發。 必須是Error的子類。
DatabaseError 和資料庫有關的錯誤發生時觸發。 必須是Error的子類。
DataError 當有資料處理時的錯誤發生時觸發，例如：除零錯誤，資料超範圍等等。 必須是DatabaseError的子類。
OperationalError 指非使用者控制的，而是運算元據庫時發生的錯誤。例如：連線意外斷開、 資料庫名未找到、事務處理失敗、記憶體分配錯誤等等運算元據庫是發生的錯誤。 必須是DatabaseError的子類。
IntegrityError 完整性相關的錯誤，例如外來鍵檢查失敗等。必須是DatabaseError子類。
InternalError 資料庫的內部錯誤，例如遊標（cursor）失效了、事務同步失敗等等。 必須是DatabaseError子類。
ProgrammingError 程式錯誤，例如資料表（table）沒找到或已存在、SQL語句語法錯誤、 引數數量錯誤等等。必須是DatabaseError的子類。
NotSupportedError 不支援錯誤，指使用了資料庫不支援的函式或API等。例如在連線物件上 使用.rollback()函式，然而資料庫並不支援事務或者事務已關閉。 必須是DatabaseError的子類。

小結

 　　哈哈，終於搞完了、整完了，累死寶寶了，已到半夜，明天有時間分享！！！

　　小夥伴python操作MySQL資料庫就是這麼簡單！！！最重要的自己要會、要有紮實的SQL基礎。

相關文章