pymysql是Python操作mysql的最主要方法，本文詳細介紹pymysql的建庫、建表和增刪改查操作。 ...

1 引言

mysql應該說是如今使用最為普遍的資料庫了，沒有之一，而Python作為最為流行的語言之一，自然少不了與mysql打交道，pymysql就是使用最多的工具庫了。

2 創建庫、表

我們先從創建資料庫、數據表說起，有了庫表，後面的增刪改查才有根據。

2.1 創建資料庫

pymysql的所有對資料庫的操作都必須先與資料庫服務建立連接，然後創建游標為基礎執行具體的sql語句。創建資料庫方法如下：

# -*- coding: utf-8 -*-
import pymysql
 
conn = pymysql.connect( # 創建資料庫連接
    host='10.10.11.131', # 要連接的資料庫所在主機ip
    user='chb', # 資料庫登錄用戶名
    password='123456!', # 登錄用戶密碼
    charset='utf8' # 編碼，註意不能寫成utf-8
)
cursor = con.cursor()
cursor .execute("create database test_db character set utf8;")
# 執行完之後別忘了關閉游標和資料庫連接
cursor.close()
conn.close()

上面代碼執行完後，就創建了一個名為test_db的資料庫：

2.2 創建數據表

# -*- coding: utf-8 -*-
import pymysql
 
conn = pymysql.connect( # 創建資料庫連接
    host='10.10.11.131', # 要連接的資料庫所在主機ip
    user='chb', # 資料庫登錄用戶名
    password='123456!', # 登錄用戶密碼
    database='test_db', # 連接的資料庫名，也可以後續通過cursor.execture('user test_db')指定
    charset='utf8' # 編碼，註意不能寫成utf-8
)
 
cursor = conn.cursor() # 創建一個游標
 
# 需要執行的創建表的sql語句
sql = """
create table book(
bookid int auto_increment primary key ,
bookname VARCHAR(255) not null ,
authors VARCHAR(255) not null ,
year_publication YEAR not null
);
"""
 
cursor.execute(sql) # 使用游標執行sql
 
# 執行完之後別忘了關閉游標和資料庫連接
cursor.close()
conn.close()

在這個創建數據表的例子中，在創建資料庫服務連接時，我們通過 database='test_db'這行代碼指定參數連接了目標資料庫，如果不想在創建資料庫服務連接時指定資料庫，可以在後續通過cursor.execture('user test_db')這種方法指定資料庫。

從上面創建資料庫和數據表的例子可以看出，pymysql執行具體操作時都是先創建資料庫服務連接，然後通過連接創建游標，以游標來執行具體的sql語句來完成具體的對資料庫操作。不單單可以創建資料庫、數據表，還可以創建索引、視圖等等，方法也是一樣的，這裡不再介紹。

3 增刪改查

3.1 插入

插入操作可以通過游標的execute和executemany兩個方法來完成。註意：只要是對數據表有修改的操作（插入、更新、刪除）在使用execute方法後，都需要再次調用commit方法對資料庫的修改才會最終生效。

execute方法一次插入一條記錄，executemany一次插入多條記錄：

（1）execute：一次插入一條記錄

cursor.execute('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);' % ('Python從入門到放棄', '喬布斯', 2019))
conn.commit()

上面這種寫法是先用字元串利用%s占位生成一條完整的sql語句，然後去執行。其實，pymysql的游標也自帶這一功能，而且看起來更加方便，所以，我們還可以這樣執行execute方法：

cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('Python從入門到放棄', '喬布斯', 2019))
conn.commit()

這種方法的好處是sql語句中的%s不需要加引號（如果加了引號，引號也會被當做數據插入到數據表中），游標執行這一語句時，會根據數據類型來判斷是否要加上引號。

（2）executemany：一次插入多條記錄

data = [
    ('21天完全入門Java', '扎克伯格', 2018),
    ('Linux學習手冊', '李納斯', 2017),
    ('MySQL從刪庫到跑路', '比爾蓋茨', 2018),
]
cursor.executemany('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);', data)
conn.commit()

3.2 更新

cursor.execute('update book set authors=%s where bookname=%s;', ["馬雲", "Python從入門到放棄"])
conn.commit()

3.3 查詢

查詢是最最要但也是最複雜的一個操作了。我們分為fetch操作、游標兩部分來說。

（1）fetch操作

插入、更新、刪除操作必須再執行commit操作之後才會生效，而查詢操作也只有在執行fetch操作之後才會生效。fetch操作包括3個方法，分別是fetchone()、fetchall()、fetchmany()。

fetchall()：查詢指定的所有記錄

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchall()
print(books)

輸出結果為：

((1, 'Python從入門到放棄', '馬雲', 2019), (2, 'Python從入門到放棄', '馬雲', 2019), (3, '21天完全入門Java', '扎克伯格', 2018))

輸出結果是以元組的形式來保存，且每一條記錄也是一個元素，這是由游標cursor決定的，在下文中還會說到。

fetchmany(size)：查詢指定數量的記錄

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchmany(2)
print(books)

輸出結果為：

((1, 'Python從入門到放棄', '馬雲', 2019), (2, 'Python從入門到放棄', '馬雲', 2019))

可以看出，fetchmany(size)是取出符合查詢條件的最前面的指定數量記錄。這裡的size指的就是想要取出的記錄條數。

fetchone()：取出第一條記錄

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchone()
print(books)

輸出結果：

(1, 'Python從入門到放棄', '馬雲', 2019)

fetchone()相當於是fetchmany(1)，取出第一條符合查詢條件的記錄。

（2）游標

我們之前使用游標都是採用預設的Cursor類型，除此以外，pymysql還提供了DictCursor、SSCursor、SSDictCursor這幾類游標。

DictCursor

之前使用的Cursor返回的數據是以元組的方式保存，而DictCursor是以字典的形式保存。創建這種類型的游標方法也很簡單，在conn.sursor()方法中傳入DictCursor這個類即可：

cursor = conn.cursor(pymysql.cursors.DictCursor) # 創建一個字典游標
cursor.execute('select * from book where bookid < %s;', [3])
books = cursor.fetchall()
print(books)
cursor.execute('select * from book where bookid < %s;', [3])
book_one = cursor.fetchone()
print(book_one)

輸出結果如下：

[{'bookid': 1, 'bookname': 'Python從入門到放棄', 'authors': '馬雲', 'year_publication': 2019}, {'bookid': 2, 'bookname': 'Python從入門到放棄', 'authors': '馬雲', 'year_publication': 2019}] {'bookid': 1, 'bookname': 'Python從入門到放棄', 'authors': '馬雲', 'year_publication': 2019}

SSCursor和SSDictCursor

SSCursor和SSDictCursor被稱為流式游標，這類游標不會像上面使用的Cursor和DictCursor那樣，一次性返回所有的數據，流式游標會陸陸續續一條一條得返回查詢數據，所以這類游標適用於記憶體低、網路帶寬小、數據量大的應用場景中。

流式游標的使用方法類似於迭代器，再迴圈中每取一條，生成一條：

cursor = conn.cursor(pymysql.cursors.SSCursor) # 創建一個流式游標
cursor.execute('select * from book;')
book = cursor.fetchone()
while book:
print(book)
book = cursor.fetchone()

輸出結果如下：

(1, 'Python從入門到放棄', '馬雲', 2019) (2, 'Python從入門到放棄', '馬雲', 2019) (3, '21天完全入門Java', '扎克伯格', 2018) (4, 'Linux學習手冊', '李納斯', 2017) (5, 'MySQL從刪庫到跑路', '比爾蓋茨', 2018) 註意：流式游標雖然也有fetchall()方法，調用後的結果與普通游標一樣返回所有數據，但是最好別去調用，這樣會失去流式游標的優勢。使用流式游標時，如果數據量很大，導致游標一直處在迴圈遍歷狀態，這時，資料庫連接（conn）是被占用的，不能再被用於執行其他sql，如果要執行其他sql那就必須再創建一個資料庫連接，游標占用資料庫連接的時長是有限制的，如果流式游標一直在遍歷，60秒後資料庫連接會斷開，不過可以在創建資料庫連接時傳入參數init_command=("SET NET_WRITE_TIMEOUT=XX")來設置這個超時時間。

SSCursor與SSDictCursor的區別就類似於Cursor和DictCursor的區別，這裡就不在介紹了。

3.4 刪除

使用pymysql執行插入、更新、刪除操作都是相似的，最後都需要commit提交：

cursor.execute('delete from book where bookid=%s;', [1])
conn.commit()

這時候，bookid為1的記錄已經被刪除，如下圖所示：

也可以使用executemany()一次性刪除多條：

cursor.executemany('delete from book where bookid=%s;', [[2], [4]])
conn.commit()

如下圖所示，bookid為2和4的記錄已經被刪除：

4 總結

pymysql還提供了事務機制，但我還有疑問沒搞清楚，不敢往博客上面寫：執行多個修改資料庫操作（多個execute方法）時，其中有一個方法有異常，這是commit，所有的所有都不會成功，那麼為什麼還要rollback呢？如果哪位前輩知道答案，請一定告知，感激不盡！

參考：

https://www.cnblogs.com/xfxing/p/9322199.html

https://www.runoob.com/python3/python3-mysql.html

https://www.jianshu.com/p/1ba64df4fd15

pymysql指南