【原】最全總結(jié) | 聊聊 Python 數(shù)據(jù)處理全家桶（Mysql 篇）

AirPython 2021-10-28

展開全文

1. 前言

在爬蟲、自動化、數(shù)據(jù)分析、軟件測試、Web 等日常操作中，除 JSON、YAML、XML 外，還有一些數(shù)據(jù)經(jīng)常會用到，比如：Mysql、Sqlite、Redis、MongoDB、Memchache 等

一般情況下，我們都會使用特定的客戶端或命令行工具去操作；但是如果涉及到工程項目，將這部分?jǐn)?shù)據(jù)操作集成到代碼中使用才是王道

接下來，我將分幾篇文章，和大家一起聊聊 Python 操作這些數(shù)據(jù)的最優(yōu) 方案

本篇從使用最為廣泛的關(guān)系型數(shù)據(jù)庫 - Mysql 開始講起

2. 準(zhǔn)備

首先，我們通過 Mysql 客戶端或命令行創(chuàng)建一個數(shù)據(jù)庫 xh

然后，在這個數(shù)據(jù)庫下建一張簡單的表 people

為了便于演示，這里只創(chuàng)建了三個字段：id、name、age，其中 id 為主鍵

Python 操作 Mysql 主要包含下面 3 種方式：

Python-MySql
PyMysql
SQLAlchemy

其中，

Python-MySql 由 C 語法打造，接口精煉，性能最棒；但是由于環(huán)境依賴多，安裝復(fù)雜，已停止更新，僅支持 Python2

PyMysql 為替代 Python-Mysql 而生，純 Python 語言編寫的 Mysql 操作客戶端，安裝方便，支持 Python3

SQLAlchemy 是一個非常強(qiáng)大的 ORM 框架，不提供底層的數(shù)據(jù)庫操作，主要是通過定義模型對應(yīng)數(shù)據(jù)表結(jié)構(gòu)，在 Python Web 編程領(lǐng)域應(yīng)用廣泛

由于 Python-MySql 不支持 Python3，所以本文只談后 2 種操作方式

3. PyMysql

首先，使用 pip 安裝依賴

# 安裝依賴
pip3 install pymysql

連接數(shù)據(jù)庫，獲取數(shù)據(jù)庫連接對象及游標(biāo)對象

使用 pymysql 中的 connect() 方法，傳入數(shù)據(jù)庫的 HOST 地址、端口號、用戶名、密碼、待操作數(shù)據(jù)庫的名稱，即可以獲取數(shù)據(jù)庫的連接對象

然后，再通過數(shù)據(jù)庫連接對象，獲取執(zhí)行數(shù)據(jù)庫具體操作的游標(biāo)對象

import pymysql

# 數(shù)據(jù)庫連接
self.db = pymysql.connect(host='localhost',
                          port=3306,
                          user='root',
                          password='**',
                          database='xh')

# 獲取游標(biāo)
self.cursor = self.db.cursor()

接著，我們來實現(xiàn)增刪改查操作

1、新增

新增包含新增單條數(shù)據(jù)和多條數(shù)據(jù)

對于單條數(shù)據(jù)的插入，只需要編寫一條插入的 SQL 語句，然后作為參數(shù)執(zhí)行上面游標(biāo)對象的 execute(sql) 方法，最后使用數(shù)據(jù)庫連接對象的 commit() 方法將數(shù)據(jù)提交到數(shù)據(jù)庫中

# 插入一條數(shù)據(jù)
SQL_INSERT_A_ITEM = "INSERT INTO PEOPLE(name,age) VALUES('xag',23);"

def insert_a_item(self):
    """
    插入一條數(shù)據(jù)
    :return:
    """
    try:
        self.cursor.execute(SQL_INSERT_A_ITEM)
        self.db.commit()
    except Exception as e:
        print('插入數(shù)據(jù)失敗')
        print(e)
        self.db.rollback()

使用執(zhí)行游標(biāo)對象的 executemany() 方法，傳入插入的 SQL 語句及位置變量列表，可以實現(xiàn)一次插入多條數(shù)據(jù)

# 插入多條數(shù)據(jù)SQL，name和age是變量，對應(yīng)列表
SQL_INSERT_MANY_ITEMS = "INSERT INTO PEOPLE (name, age) VALUES(%s, %s)"

# 待插入的數(shù)據(jù)
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("插入數(shù)據(jù)異常")
        self.db.rollback()

需要注意的是，PyMysql 會將 SQL 語句中的所有字段當(dāng)做字符串進(jìn)行處理，所以這里的 age 字段在 SQL 中被當(dāng)做字符串處理

2、查詢

查詢分為三步，分別是：

通過游標(biāo)對象執(zhí)行具體的 SQL 語句
通過游標(biāo)對象，獲取到元組數(shù)據(jù)
遍歷元組數(shù)據(jù)，查看結(jié)果

比如：查看數(shù)據(jù)表中所有的記錄

# 查詢所有記錄
SQL_QUERY_ALL = "SELECT * FROM PEOPLE;"

def query(self):
    """查詢數(shù)據(jù)"""
    # 查詢所有數(shù)據(jù)
    self.cursor.execute(SQL_QUERY_ALL)

    # 元組數(shù)據(jù)
    rows = self.cursor.fetchall()

    # 打印結(jié)果
    for row in rows:
        id = row[0]
        name = row[1]
        age = row[2]
        print('id:', id, ',name:', name, 'age:', age)

如果需要按條件查詢某一條記錄，只需要修改 SQL 語句即可實現(xiàn)

# 按id查詢
SQL_QUERY_WITH_CONDITION = "SELECT * FROM PEOPLE WHERE id={};"

# 查詢id為5的記錄
self.cursor.execute(SQL_QUERY_WITH_CONDITION.format(5))

3、更新

和新增操作類似，更新操作也是通過游標(biāo)對象去執(zhí)行更新的 SQL 語句，最后利用數(shù)據(jù)庫連接對象將數(shù)據(jù)真實更新到數(shù)據(jù)庫中

# 更新(通過id去更新)
SQL_UPDATE = "UPDATE PEOPLE SET name='%s',age=%s WHERE id=%s"

def update(self):
    """
    更新數(shù)據(jù)
    :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('更新數(shù)據(jù)異常')
        print(e)

4、刪除

刪除操作同查詢、新增操作類似，只需要變更 SQL 語句即可

# 刪除（通過id去刪除數(shù)據(jù)）
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:
        # 發(fā)生錯誤時回滾
        self.db.rollback()
        print(e)

最后，我們需要將游標(biāo)對象和數(shù)據(jù)庫連接對象資源釋放掉

def teardown(self):
    # 釋放資源
    self.cursor.close()
    self.db.close()

4. SQLAlchemy

首先，使用 SQLAlchemy 操作 Mysql 數(shù)據(jù)庫同樣先需要安裝依賴庫

# 安裝依賴包
pip3 install sqlalchemy

通過 SQLAlchemy 的內(nèi)置方法 declarative_base() 創(chuàng)建一個基礎(chǔ)類 Base

然后，自定義一個 Base 類的子類，內(nèi)部定義靜態(tài)變量，和上面數(shù)據(jù)表 people 中的字段一一對應(yīng)

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

# 基礎(chǔ)類
Base = declarative_base()


# 自定義的表
class People(Base):
    # 表名
    __tablename__ = 'people'

    # 定義字段
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    age = Column(Integer)

    def __repr__(self):
        """
        便于打印結(jié)果
        :return:
        """
        return "<People(id:{},name:{},age:{})".format(self.id, self.name, self.age)

接著，通過數(shù)據(jù)庫名、用戶名、密碼及 Host 組裝一個數(shù)據(jù)庫連接地址，作為參數(shù)傳入到 SQLAlchemy 的 create_engine() 方法中，以創(chuàng)建一個數(shù)據(jù)庫引擎實例對象

# 創(chuàng)建數(shù)據(jù)庫的引擎實例對象
# 數(shù)據(jù)庫名稱：xh
engine = create_engine("mysql+pymysql://root:數(shù)據(jù)庫密碼@localhost:3306/xh",
                               encoding="utf-8",
                               echo=True)

最后，通過數(shù)據(jù)庫引擎在數(shù)據(jù)庫中創(chuàng)建表結(jié)構(gòu)，并實例化一個會話對象

需要注意的是，create_all() 方法中的 checkfirst 參數(shù)如果傳入 True，則會判斷數(shù)據(jù)表是否存在，如果表存在，則不會重新創(chuàng)建

# 創(chuàng)建表結(jié)構(gòu)
# checkfirst：判斷表是否存在，如果存在，就不重復(fù)創(chuàng)建
Base.metadata.create_all(engine, checkfirst=True)

# 實例化會話
self.session = sessionmaker(bind=engine)()

這樣所有的準(zhǔn)備工作已經(jīng)完成，接下來可以進(jìn)行增刪改查操作了

1、新增

新增操作同樣包含插入一條記錄和多條記錄，分別對應(yīng)會話對象的 add()、add_all() 方法

對于一條記錄的新增操作，只需要實例化一個 People 對象，執(zhí)行上面的會話對象的 add(instance) 和 commit() 兩個方法，即可以將數(shù)據(jù)插入到數(shù)據(jù)表中

def add_item(self):
    """
    新增
    :return:
    """
    # 實例化一個對象
    people = People(name='xag', age=23)
    self.session.add(people)

    # 提交數(shù)據(jù)才會生效
    self.session.comit()

如果需要一次插入多條數(shù)據(jù)，只需要調(diào)用 add_all(列表數(shù)據(jù)) 即可

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、查詢

查詢數(shù)據(jù)表的操作對應(yīng)會話對象的 query(可變參數(shù))

方法中的參數(shù)指定要查詢的字段值，還可以通過 all()、first() 級聯(lián)方法限制要查詢的數(shù)據(jù)

def query(self):
    """
    查詢
    :return:
    """
    # 查詢所有記錄
    # result = self.session.query(People).all()

    # 查詢name/age兩個字段
    result = self.session.query(People.name, People.age).all()
    print(result)

當(dāng)然，也可以利用 filter_by(條件)，按條件進(jìn)行過濾

# 條件查詢
resp = self.session.query(People).filter_by(name='xag').first()
print(resp)

3、更新

更新操作一般做法是：

query 查詢出待更新的對象
直接更新對象中的數(shù)據(jù)
使用會話對象提交修改，完成更新操作

def update1(self, id):
    """
    更新數(shù)據(jù)1
    :return:
    """
    # 獲取數(shù)據(jù)
    temp_people = self.session.query(People).filter_by(id=id).first()

    # 更新數(shù)據(jù)
    temp_people.name = "星安果"
    temp_people.age = 18
    # 提交修改
    self.session.commit()

需要指出的是，這里可以使用 update() 方法進(jìn)行簡寫

def update2(self, id):
    """
    更新數(shù)據(jù)2
    :param id:
    :return:
    """
    # 使用update()方法直接更新字段值
    self.session.query(People).filter(People.id == id).update({People.name: "xag", People.age: 1})
    self.session.commit()

4、刪除

刪除操作對應(yīng) delete() 方法，同樣是先查詢，后刪除，最后提交會話完成刪除操作

以按照 id 刪除某一條記錄為例：

def del_by_id(self, id):
    """
    通過id刪除一條記錄
    :param id:
    :return:
    """
    del_count = self.session.query(People).filter(People.id == id).delete()
    print('刪除數(shù)目:', del_count)
    self.session.commit()