1. 前言
在爬蟲、自動化、資料分析、軟體測試、Web 等日常操作中，除 JSON、YAML、XML 外，還有一些資料經常會用到，比如：Mysql、Sqlite、Redis、MongoDB、Memchache 等
一般情況下，我們都會使用特定的客戶端或命令列工具去操作；但是如果涉及到工程專案，將這部分資料操作整合到程式碼中使用才是王道
接下來，我將分幾篇文章，和大家一起聊聊 Python 操作這些資料的 最優 方案
本篇從使用最為廣泛的關係型資料庫 - Mysql 開始講起
2. 準備
首先，我們通過 Mysql 客戶端或命令列建立一個資料庫 xh
然後，在這個資料庫下建一張簡單的表 people
為了便於演示，這裡只建立了三個欄位：id、name、age，其中 id 為主鍵
Python 操作 Mysql 主要包含下面 3 種方式：
-
Python-MySql
-
PyMysql
-
SQLAlchemy
其中，
Python-MySql 由 C 語法打造，介面精煉，效能最棒；但是由於環境依賴多，安裝複雜，已停止更新，僅支援 Python2
PyMysql 為替代 Python-Mysql 而生，純 Python 語言編寫的 Mysql 操作客戶端，安裝方便，支援 Python3
SQLAlchemy 是一個非常強大的 ORM 框架，不提供底層的資料庫操作，主要是通過定義模型對應資料表結構，在 Python Web 程式設計領域應用廣泛
由於 Python-MySql 不支援 Python3，所以本文只談後 2 種操作方式
3. PyMysql
首先，使用 pip 安裝依賴
# 安裝依賴
pip3 install pymysql
連線資料庫，獲取資料庫連線物件及遊標物件
使用 pymysql 中的 connect() 方法，傳入資料庫的 HOST 地址、埠號、使用者名稱、密碼、待運算元據庫的名稱，即可以獲取 資料庫的連線物件
然後，再通過資料庫連線物件，獲取執行資料庫具體操作的 遊標物件
import pymysql
# 資料庫連線
self.db = pymysql.connect(host='localhost',
port=3306,
user='root',
password='**',
database='xh')
# 獲取遊標
self.cursor = self.db.cursor()
接著，我們來實現增刪改查操作
1、新增
新增包含新增單條資料和多條資料
對於單條資料的插入，只需要編寫一條插入的 SQL 語句，然後作為引數執行上面遊標物件的 execute(sql) 方法，最後使用資料庫連線物件的 commit() 方法將資料提交到資料庫中
# 插入一條資料
SQL_INSERT_A_ITEM = "INSERT INTO PEOPLE(name,age) VALUES('xag',23);"
def insert_a_item(self):
"""
插入一條資料
:return:
"""
try:
self.cursor.execute(SQL_INSERT_A_ITEM)
self.db.commit()
except Exception as e:
print('插入資料失敗')
print(e)
self.db.rollback()
使用執行遊標物件的 executemany() 方法，傳入插入的 SQL 語句及 位置變數列表，可以實現一次插入多條資料
# 插入多條資料SQL，name和age是變數，對應列表
SQL_INSERT_MANY_ITEMS = "INSERT INTO PEOPLE (name, age) VALUES(%s, %s)"
# 待插入的資料
self.datas = [("張三", 23), ("李四", 24), ("王五", 25)]
def insert_items(self):
"""
插入多條記錄
:return:
"""
try:
self.cursor.executemany(SQL_INSERT_MANY_ITEMS, self.datas)
self.db.commit()
except Exception as e:
print("插入資料異常")
self.db.rollback()
需要注意的是，PyMysql 會將 SQL 語句中的所有欄位當做字串進行處理，所以這裡的 age 欄位在 SQL 中被當做字串處理
2、查詢
查詢分為三步，分別是：
-
通過遊標物件執行具體的 SQL 語句
-
通過遊標物件，獲取到元組資料
-
遍歷元組資料，檢視結果
比如：檢視資料表中所有的記錄
# 查詢所有記錄
SQL_QUERY_ALL = "SELECT * FROM PEOPLE;"
def query(self):
"""查詢資料"""
# 查詢所有資料
self.cursor.execute(SQL_QUERY_ALL)
# 元組資料
rows = self.cursor.fetchall()
# 列印結果
for row in rows:
id = row[0]
name = row[1]
age = row[2]
print('id:', id, ',name:', name, 'age:', age)
如果需要按條件查詢某一條記錄，只需要修改 SQL 語句即可實現
# 按id查詢
SQL_QUERY_WITH_CONDITION = "SELECT * FROM PEOPLE WHERE id={};"
# 查詢id為5的記錄
self.cursor.execute(SQL_QUERY_WITH_CONDITION.format(5))
3、更新
和 新增操作 類似，更新操作也是通過遊標物件去執行更新的 SQL 語句，最後利用資料庫連線物件將資料真實更新到資料庫中
# 更新(通過id去更新)
SQL_UPDATE = "UPDATE PEOPLE SET name='%s',age=%s WHERE id=%s"
def update(self):
"""
更新資料
:return:
"""
sql_update = SQL_UPDATE % ("王五五", 30, 5)
print(sql_update)
try:
self.cursor.execute(sql_update)
self.db.commit()
except Exception as e:
self.db.rollback()
print('更新資料異常')
print(e)
4、刪除
刪除操作同查詢、新增操作類似，只需要變更 SQL 語句即可
# 刪除（通過id去刪除資料）
SQL_DELETE = "DELETE FROM PEOPLE WHERE id=%d"
def delete(self):
"""
刪除記錄
:return:
"""
try:
# 刪除的完整sql
sql_del = SQL_DELETE % (5)
self.cursor.execute(sql_del)
self.db.commit()
except Exception as e:
# 發生錯誤時回滾
self.db.rollback()
print(e)
最後，我們需要將遊標物件和資料庫連線物件資源釋放掉
def teardown(self):
# 釋放資源
self.cursor.close()
self.db.close()
4. SQLAlchemy
首先，使用 SQLAlchemy 操作 Mysql 資料庫同樣先需要安裝依賴庫
# 安裝依賴包
pip3 install sqlalchemy
通過 SQLAlchemy 的內建方法 declarative_base() 建立一個基礎類 Base
然後，自定義一個 Base 類的子類，內部定義靜態變數，和上面資料表 people 中的欄位一一對應
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
# 基礎類
Base = declarative_base()
# 自定義的表
class People(Base):
# 表名
__tablename__ = 'people'
# 定義欄位
id = Column(Integer, primary_key=True)
name = Column(String(255))
age = Column(Integer)
def __repr__(self):
"""
便於列印結果
:return:
"""
return "<People(id:{},name:{},age:{})".format(self.id, self.name, self.age)
接著，通過資料庫名、使用者名稱、密碼及 Host 組裝一個資料庫連線地址，作為引數傳入到 SQLAlchemy 的 create_engine() 方法中，以建立一個資料庫引擎例項物件
# 建立資料庫的引擎例項物件
# 資料庫名稱：xh
engine = create_engine("mysql+pymysql://root:資料庫密碼@localhost:3306/xh",
encoding="utf-8",
echo=True)
最後，通過資料庫引擎在資料庫中建立表結構，並例項化一個 會話物件
需要注意的是，create_all() 方法中的 checkfirst 引數如果傳入 True，則會判斷資料表是否存在，如果表存在，則不會重新建立
# 建立表結構
# checkfirst：判斷表是否存在，如果存在，就不重複建立
Base.metadata.create_all(engine, checkfirst=True)
# 例項化會話
self.session = sessionmaker(bind=engine)()
這樣所有的準備工作已經完成，接下來可以進行增刪改查操作了
1、新增
新增操作同樣包含插入一條記錄和多條記錄，分別對應會話物件的 add()、add_all() 方法
對於一條記錄的新增操作，只需要例項化一個 People 物件，執行上面的會話物件的 add(instance) 和 commit() 兩個方法，即可以將資料插入到資料表中
def add_item(self):
"""
新增
:return:
"""
# 例項化一個物件
people = People(name='xag', age=23)
self.session.add(people)
# 提交資料才會生效
self.session.comit()
如果需要一次插入多條資料，只需要呼叫 add_all(列表資料) 即可
def add_items(self):
"""
新增多條記錄
:return:
"""
datas = [
People(name='張三', age=20),
People(name='李四', age=21),
People(name='王五', age=22),
]
self.session.add_all(datas)
self.session.commit()
2、查詢
查詢資料表的操作對應會話物件的 query(可變引數)
方法中的引數指定要查詢的欄位值，還可以通過 all()、first() 級聯方法限制要查詢的資料
def query(self):
"""
查詢
:return:
"""
# 查詢所有記錄
# result = self.session.query(People).all()
# 查詢name/age兩個欄位
result = self.session.query(People.name, People.age).all()
print(result)
當然，也可以利用 filter_by(條件)，按條件進行過濾
# 條件查詢
resp = self.session.query(People).filter_by(name='xag').first()
print(resp)
3、更新
更新操作一般做法是：
-
query 查詢出待更新的物件
-
直接更新物件中的資料
-
使用會話物件提交修改，完成更新操作
def update1(self, id):
"""
更新資料1
:return:
"""
# 獲取資料
temp_people = self.session.query(People).filter_by(id=id).first()
# 更新資料
temp_people.name = "星安果"
temp_people.age = 18
# 提交修改
self.session.commit()
需要指出的是，這裡可以使用 update() 方法進行簡寫
def update2(self, id):
"""
更新資料2
:param id:
:return:
"""
# 使用update()方法直接更新欄位值
self.session.query(People).filter(People.id == id).update({People.name: "xag", People.age: 1})
self.session.commit()
4、刪除
刪除操作對應 delete() 方法，同樣是先查詢，後刪除，最後提交會話完成刪除操作
以按照 id 刪除某一條記錄為例：
def del_by_id(self, id):
"""
通過id刪除一條記錄
:param id:
:return:
"""
del_count = self.session.query(People).filter(People.id == id).delete()
print('刪除數目:', del_count)
self.session.commit()
5.最後
本篇文章通過一張表的增刪改查，詳細講解了 Python 操作 Mysql 的兩種使用方式
在實際專案中，如果僅僅是簡單的爬蟲或者自動化，建議使用 PyMysql；否則建議直接上 SQLAlchemy，它更強大方便
我已經將文中全部原始碼上傳到後臺，關注公眾號「 AirPython 」後回覆「 dball 」即可獲得全部原始碼
如果你覺得文章還不錯，請大家 點贊、分享、留言下，因為這將是我持續輸出更多優質文章的最強動力！
推薦閱讀