pymysql指南

奧辰發表於2019-07-24
MySql

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一次插入多條記錄：

  • 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不需要加引號（如果加了引號，引號也會被當做資料插入到資料表中），遊標執行這一語句時，會根據資料型別來判斷是否要加上引號。

  • 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的記錄已經被刪除：

 

相關文章