python 操作 mysql 只看這篇就夠了

大话性能發表於2024-03-17
PythonMySql

初始化準備

安裝 pymysql 包

sudo pip3 install PyMysql

然後在 mysql 裡建立資料庫名稱為 my_test, 使用者名稱/密碼也是 my_test , 並建立 Product 表如下:

DROP TABLE IF EXISTS `Product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,    /* 商品名稱 */
  `remark` varchar(1000) NULL,
  `isBuy` int(1) DEFAULT 1,      /* 1: 在售 2:賣出 */
  `version` int(11) NOT null default 1000, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

測試

pymysql 的程式碼還是很簡單的, 以下程式碼分別為連線 mysql 獲得 connection, 從 connection 獲得 cursor 進行操作, 都是固定套路:

#! /usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql


host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'


# ---- 用pymysql 運算元據庫
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


def check_it():

    conn = get_connection()

    # 使用 cursor() 方法建立一個 dict 格式的遊標物件 cursor
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    # 使用 execute()  方法執行 SQL 查詢
    cursor.execute("select count(id) as total from Product")

    # 使用 fetchone() 方法獲取單條資料.
    data = cursor.fetchone()

    print("-- 當前數量: %d " % data['total'])

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


if __name__ == '__main__':
    check_it()

pymysql 實戰應用

使用 with 最佳化操作程式碼

從以上程式碼可以看到, 如果每次都要開啟連線, 關閉連線 .... 程式碼難看且容易出錯. 最好的辦法是用 python with 的方式來增加一個上下文管理器. 修改如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


import pymysql
from timeit import default_timer


host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'


# ---- 用pymysql 運算元據庫
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


# ---- 使用 with 的方式來最佳化程式碼
class UsingMysql(object):

    def __init__(self, commit=True, log_time=True, log_label='總用時'):
        """

        :param commit: 是否在最後提交事務(設定為False的時候方便單元測試)
        :param log_time:  是否列印程式執行總時間
        :param log_label:  自定義log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要記錄時間
        if self._log_time is True:
            self._start = default_timer()

        # 在進入的時候自動獲取連線和cursor
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事務
        if self._commit:
            self._conn.commit()
        # 在退出的時候自動關閉連線和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    @property
    def cursor(self):
        return self._cursor

def check_it():

    with UsingMysql(log_time=True) as um:
        um.cursor.execute("select count(id) as total from Product")
        data = um.cursor.fetchone()
        print("-- 當前數量: %d " % data['total'])

if __name__ == '__main__':
    check_it()

程式執行結果如下:

-- 當前數量: 0 
-- 用時: 0.002345 秒

用這種方式改寫程式碼之後, 業務方法更精簡. 並且加了引數方便進行單元測試和監控程式碼的執行時間, 不亦美哉.

封裝公用程式碼

現在新增一個 pymysql_comm.py 類, 將連線程式碼和寫好的 UsingMysql 放進去, 程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
from timeit import default_timer


host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'


# ---- 用pymysql 運算元據庫
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


# ---- 使用 with 的方式來最佳化程式碼
class UsingMysql(object):

    def __init__(self, commit=True, log_time=True, log_label='總用時'):
        """

        :param commit: 是否在最後提交事務(設定為False的時候方便單元測試)
        :param log_time:  是否列印程式執行總時間
        :param log_label:  自定義log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要記錄時間
        if self._log_time is True:
            self._start = default_timer()

        # 在進入的時候自動獲取連線和cursor
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事務
        if self._commit:
            self._conn.commit()
        # 在退出的時候自動關閉連線和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    @property
    def cursor(self):
        return self._cursor

新增一個 test.py 檔案, 引入這個模組進行測試使用. 程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def check_it():

    with UsingMysql(log_time=True) as um:
        um.cursor.execute("select count(id) as total from Product")
        data = um.cursor.fetchone()
        print("-- 當前數量: %d " % data['total'])

if __name__ == '__main__':
    check_it()

後續的學習和開發都可以使用這個封裝類, 用類似 test.py 的方式來寫自己的業務程式碼, 更方便精簡了.

增刪改查 api

下面記錄了最常用的增刪改查分頁等方法

新增單條記錄

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def select_one(cursor):
    cursor.execute("select * from Product")
    data = cursor.fetchone()
    print("-- 單條記錄: {0} ".format(data))


# 新增單條記錄
def create_one():

    with UsingMysql(log_time=True) as um:
        sql = "insert into Product(name, remark) values(%s, %s)"
        params = ('男士雙肩揹包1', '這個是非常好的揹包')
        um.cursor.execute(sql, params)

        # 檢視結果
        select_one(um.cursor)

if __name__ == '__main__':
    create_one()

在上面程式碼裡先增加了一條記錄, 然後接著檢視這條記錄, 結果類似這樣:

-- 單條記錄: {'id': 1003, 'name': '男士雙肩揹包1', 'isBuy': 1, 'remark': '這個是非常好的揹包'} 
-- 用時: 0.002600 秒

順便吐嘈下, 用 1 秒/0.0026 可計算得出併發數是 384.6 , 這表示無最佳化狀態下每秒插入記錄 384 條左右, 效能比較低.

新增多條記錄

一口氣插入 1000 條記錄, 同時加入查詢方法, 如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def get_count(cursor):
    cursor.execute("select count(id) as total from Product")

    # 使用 fetchone() 方法獲取單條資料.
    data = cursor.fetchone()

    print("-- 當前數量: %d " % data['total'])


def delete_all(cursor):
    cursor.execute("delete from Product")


# 插入 1000 條記錄
def create_many():

    with UsingMysql(log_time=True) as um:

        # 清空之前的測試記錄
        delete_all(um.cursor)

        for i in range(0, 1000):

            sql = "insert into Product(name, remark) values(%s, %s)"
            params = ('男士雙肩揹包%d' % i, '這個是非常好的揹包%d' %i)
            um.cursor.execute(sql, params)

        # 檢視結果
        get_count(um.cursor)

if __name__ == '__main__':
    create_many()

在我的機器用時如下:

-- 當前數量: 1000 
-- 用時: 0.097566 秒

勉強能接受. 現在用你的 mysql 客戶端檢視資料庫, 應該能看到 1000 條資料。

刪除某條記錄

為了方便測試, 順便把查的方法也提前寫出來了，程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def delete_one(cursor, name):
    sql = 'delete from Product where name = %s'
    params = name
    cursor.execute(sql, params)
    print('--- 已刪除名字為%s的商品. ' % name)


def select_one(cursor):
    sql = 'select * from Product'
    cursor.execute(sql)
    data = cursor.fetchone()
    print('--- 已找到名字為%s的商品. ' % data['name'])
    return data['name']


def select_one_by_name(cursor, name):
    sql = 'select * from Product where name = %s'
    params = name
    cursor.execute(sql, params)
    data = cursor.fetchone()
    if data:
        print('--- 已找到名字為%s的商品. ' % data['name'])
    else:
        print('--- 名字為%s的商品已經沒有了' % name)


# 刪除單條記錄
def check_delete_one():

    with UsingMysql(log_time=True) as um:

        # 查詢一條記錄
        name = select_one(um.cursor)

        # 刪除之
        delete_one(um.cursor, name)

        # 檢視還在不在?
        select_one_by_name(um.cursor, name)

if __name__ == '__main__':
    check_delete_one()

操作結果類似這樣:

--- 已找到名字為男士雙肩揹包0的商品. 
--- 已刪除名字為男士雙肩揹包0的商品. 
--- 名字為男士雙肩揹包0的商品已經沒有了
-- 用時: 0.015917 秒

修改記錄

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def update_by_pk(cursor, name, pk):
    sql = "update Product set name = '%s' where id = %d" % (name, pk)

    cursor.execute(sql)


def select_one(cursor):
    sql = 'select * from Product'
    cursor.execute(sql)
    return cursor.fetchone()


def select_one_by_name(cursor, name):
    sql = 'select * from Product where name = %s'
    params = name
    cursor.execute(sql, params)
    data = cursor.fetchone()
    if data:
        print('--- 已找到名字為%s的商品. ' % data['name'])
    else:
        print('--- 名字為%s的商品已經沒有了' % name)


# 修改記錄
def check_update():

    with UsingMysql(log_time=True) as um:

        # 查詢一條記錄
        data = select_one(um.cursor)
        pk = data['id']
        print('--- 商品{0}: '.format(data))

        # 修改名字
        new_name = '單肩包'
        update_by_pk(um.cursor, new_name, pk)

        # 檢視
        select_one_by_name(um.cursor, new_name)

if __name__ == '__main__':
    check_update()

這裡記錄了根據 id 修改記錄的方法, 其他修改方式主要看 sql 知識, 就不再贅述.

查詢

查詢主要涉及 pymysql 的 fetchone(返回單條資料), fetchall(返回所有資料) . fetchone 上面已經寫過了, 現在來看看 fetchall 方法:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def fetch_list_by_filter(cursor, pk):
    sql = 'select * from Product where id > %d' % pk
    cursor.execute(sql)
    data_list = cursor.fetchall()
    print('-- 總數: %d' % len(data_list))
    return data_list


# 查詢
def fetch_list():

    with UsingMysql(log_time=True) as um:

        # 查詢id 大於800的記錄
        data_list = fetch_list_by_filter(um.cursor, 800)

        # 查詢id 大於 10000 的記錄
        data_list = fetch_list_by_filter(um.cursor, 10000)

if __name__ == '__main__':
    fetch_list()

結果應該類似這樣:

-- 總數: 999
-- 總數: 0
-- 用時: 0.012355 秒

分頁查詢

分頁查詢主要是用了 mysql 的 limit 特性, 和 pymysql 沒太大關係, 程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_comm import UsingMysql


def fetch_page_data(cursor, pk, page_size, skip):
    sql = 'select * from Product where id > %d limit %d,%d' % (pk, skip, page_size)
    cursor.execute(sql)
    data_list = cursor.fetchall()
    print('-- 總數: %d' % len(data_list))
    print('-- 資料: {0}'.format(data_list))
    return data_list


# 查詢
def check_page():

    with UsingMysql(log_time=True) as um:

        page_size = 10
        pk = 500

        for page_no in range(1, 6):

            print('====== 第%d頁資料' % page_no)
            skip = (page_no - 1) * page_size

            fetch_page_data(um.cursor, pk, page_size, skip)


if __name__ == '__main__':
    check_page()

更多內容可以學習《測試工程師 Python 工具開發實戰》書籍、《大話效能測試 JMeter 實戰》書籍

中級篇: 使用連線池和封裝方法

經過一系列示例, 現在你應該會用 pymysql 做最基本的增刪改查分頁了. 現在來看點高階點的功能: 更好的封裝程式碼和使用資料庫連線池.

封裝程式碼

我們發覺呼叫 pymysql 的程式碼都差不多, 其實可以挪到公用方法裡去, 新增一個 pymysql_lib_1.py 檔案, 實現 UsingMysql 如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


import pymysql
from timeit import default_timer


host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'


# ---- 用pymysql 運算元據庫
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


# ---- 使用 with 的方式來最佳化程式碼
class UsingMysql(object):

    def __init__(self, commit=True, log_time=True, log_label='總用時'):
        """

        :param commit: 是否在最後提交事務(設定為False的時候方便單元測試)
        :param log_time:  是否列印程式執行總時間
        :param log_label:  自定義log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要記錄時間
        if self._log_time is True:
            self._start = default_timer()

        # 在進入的時候自動獲取連線和cursor
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事務
        if self._commit:
            self._conn.commit()
        # 在退出的時候自動關閉連線和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    # ========= 一系列封裝的業務方法

    # 返回 count
    def get_count(self, sql, params=None, count_key='count(id)'):
        self.cursor.execute(sql, params)
        data = self.cursor.fetchone()
        if not data:
            return 0
        return data[count_key]

    def fetch_one(self, sql, params=None):
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, sql, params=None):
        self.cursor.execute(sql, params)
        return self.cursor.fetchall()

    def fetch_by_pk(self, sql, pk):
        self.cursor.execute(sql, (pk,))
        return self.cursor.fetchall()

    def update_by_pk(self, sql, params=None):
        self.cursor.execute(sql, params)

    @property
    def cursor(self):
        return self._cursor

然後新增一個 test2.py 檔案進行測試, 如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from pymysql_lib_1 import UsingMysql


def check_it():

    with UsingMysql(log_time=True) as um:
        sql = "select count(id) as total from Product"
        print("-- 當前數量: %d " % um.get_count(sql, None, 'total'))

if __name__ == '__main__':
    check_it()

可以看到業務程式碼精簡了不少, 拼 sql 和引數就好了, 其他呼叫方法都封裝到了上下文管理器.

使用連線池

在上面的使用過程中, 每個請求都會開啟一個資料庫連線. 如果連線數太多, 資料庫很快就會報錯. 如何調整資料庫的連線數增加併發效能算是個比較有技術含量的話題, 我打算放到高階篇裡再介紹. 現在這裡要讓你知道的是: 資料庫這麼返回連線是不行的, 必須要使用連線池.

連線池程式碼當然不用自己動手, python 的世界那麼大~ 先安裝 DBUtils, 如下:

pip3 install DBUtils

然後新增 pymysql_lib.py , 增加程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


import pymysql
from timeit import default_timer
from DBUtils.PooledDB import PooledDB


class DMysqlConfig:
    """

        :param mincached:連線池中空閒連線的初始數量
        :param maxcached:連線池中空閒連線的最大數量
        :param maxshared:共享連線的最大數量
        :param maxconnections:建立連線池的最大數量
        :param blocking:超過最大連線數量時候的表現，為True等待連線數量下降，為false直接報錯處理
        :param maxusage:單個連線的最大重複使用次數
        :param setsession:optional list of SQL commands that may serve to prepare
            the session, e.g. ["set datestyle to ...", "set time zone ..."]
        :param reset:how connections should be reset when returned to the pool
            (False or None to rollback transcations started with begin(),
            True to always issue a rollback for safety's sake)
        :param host:資料庫ip地址
        :param port:資料庫埠
        :param db:庫名
        :param user:使用者名稱
        :param passwd:密碼
        :param charset:字元編碼
    """

    def __init__(self, host, db, user, password, port=3306):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

        self.charset = 'UTF8'  # 不能是 utf-8
        self.minCached = 10
        self.maxCached = 20
        self.maxShared = 10
        self.maxConnection = 100

        self.blocking = True
        self.maxUsage = 100
        self.setSession = None
        self.reset = True


# ---- 用連線池來返回資料庫連線
class DMysqlPoolConn:

    __pool = None

    def __init__(self, config):

        if not self.__pool:
            self.__class__.__pool = PooledDB(creator=pymysql,
                                             maxconnections=config.maxConnection,
                                             mincached=config.minCached,
                                             maxcached=config.maxCached,
                                             maxshared=config.maxShared,
                                             blocking=config.blocking,
                                             maxusage=config.maxUsage,
                                             setsession=config.setSession,
                                             charset=config.charset,
                                             host=config.host,
                                             port=config.port,
                                             database=config.db,
                                             user=config.user,
                                             password=config.password,
                                             )

    def get_conn(self):
        return self.__pool.connection()


# ========== 在程式的開始初始化一個連線池
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'

db_config = DMysqlConfig(host, db, user, password, port)


g_pool_connection = DMysqlPoolConn(db_config)


# ---- 使用 with 的方式來最佳化程式碼
class UsingMysql(object):

    def __init__(self, commit=True, log_time=True, log_label='總用時'):
        """

        :param commit: 是否在最後提交事務(設定為False的時候方便單元測試)
        :param log_time:  是否列印程式執行總時間
        :param log_label:  自定義log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要記錄時間
        if self._log_time is True:
            self._start = default_timer()

        # 從連線池獲取資料庫連線
        conn = g_pool_connection.get_conn()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事務
        if self._commit:
            self._conn.commit()
        # 在退出的時候自動關閉連線和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    # ========= 一系列封裝的業務方法

    # 返回 count
    def get_count(self, sql, params=None, count_key='count(id)'):
        self.cursor.execute(sql, params)
        data = self.cursor.fetchone()
        if not data:
            return 0
        return data[count_key]

    def fetch_one(self, sql, params=None):
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, sql, params=None):
        self.cursor.execute(sql, params)
        return self.cursor.fetchall()

    def fetch_by_pk(self, sql, pk):
        self.cursor.execute(sql, (pk,))
        return self.cursor.fetchall()

    def update_by_pk(self, sql, params=None):
        self.cursor.execute(sql, params)

    @property
    def cursor(self):
        return self._cursor

新增加的一大坨程式碼看起來很多, 其實只是增加了兩個配置類. 同時在這裡:

# ========== 在程式的開始初始化一個連線池
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'

db_config = DMysqlConfig(host, db, user, password, port)


g_pool_connection = DMysqlPoolConn(db_config)

例項化了連線池. 後續的上下文管理器改從連線池獲取連線, 其他程式碼都不變.

把這個 pymysql_lib 存好, 以後有機會慢慢增加/修改裡面的各種 fetch/update ... 方法, 這個檔案會變成你的傳家寶, 你會用它和 mysql 打交道很多很多年...

最後的嚴肅問題: raw sql ? 使用或放棄?

從 UsingMysql 可以看出程式碼最佳化到這個層面已經到頭了. 可是那些什麼 insert 語句, update 語句還是要拼一大堆 sql 欄位, 怎麼辦? 這裡有兩個辦法: 一個是思考一些程式碼生成技術, 根據各種引數自動組裝 sql, 這樣下去這程式碼就會變成自己獨有的 orm 了 (年輕時我就這麼幹) . 另一個選擇 (也就是我現在的選擇), 不用 pymysql, 而是使用 sqlalchemy .... :-D :-D :-D

我現在工作中很少用 Mysql , 通常用到的時候都是接手別人的程式碼. 所以我一般這麼做: 簡單無效能瓶頸的業務程式碼, 我用 sqlalchemy 不用動腦子. 有效能瓶頸的地方, 我用 pymysql 原生 sql 進行操作. 因為 pymysql 網上很少成型的好文章, 所以我才寫了這麼一大坨進行總結.

sqlchemy 入門

新增一個 sqlal_comm.py 類, 程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Text
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from timeit import default_timer


host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'


g_mysql_url = 'mysql+pymysql://%s:%s@%s:%d/%s' % (user, password, host, port, db)


engine = create_engine(g_mysql_url)

Base = declarative_base()


class Product(Base):
    __tablename__ = 'Product'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(40))
    remark = Column(String(1000), nullable=True)
    isBuy = Column(Integer, default=1)

Base.metadata.create_all(engine)  #建立表

Session = sessionmaker(bind=engine)

# =============== 以上為初始化資料庫和表

# ---- 使用 with 的方式來最佳化程式碼
class UsingAlchemy(object):

    def __init__(self, commit=True, log_time=True, log_label='總用時'):
        """

        :param commit: 是否在最後提交事務(設定為False的時候方便單元測試)
        :param log_time:  是否列印程式執行總時間
        :param log_label:  自定義log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label
        self._session = Session()

    def __enter__(self):

        # 如果需要記錄時間
        if self._log_time is True:
            self._start = default_timer()

        return self

    def __exit__(self, *exc_info):
        # 提交事務
        if self._commit:
            self._session.commit()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    @property
    def session(self):
        return self._session

這個檔案分為兩大部分: 上部分是 sqlalchemy 的固定套路: 拼接連線字串, 進行連線初始化, 然後初始化資料庫的表. 下部分是繼續之前的上下文管理套路, 讓程式碼編寫更輕鬆點.

新增一個 test4.py 進行測試, 如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from sqlal_comm import Session, Product, UsingAlchemy


# 測試獲取一條記錄
def check_it():

    session = Session()

    result = session.query(Product).first()
    if result is None:
        session.commit()
        return None

    session.commit()
    session.close()
    print('-- 得到記錄: {0}'.format(result))


# 測試獲取一條記錄
def check_it_2():

    with UsingAlchemy() as ua:

        result = ua.session.query(Product).first()
        print('-- 得到記錄: {0}'.format(result))

if __name__ == '__main__':
    check_it()
    check_it_2()

這個檔案用兩種方式來進行呼叫, 顯然用了上下文管理的方式會更輕鬆點.

sqlalchemy 條件查詢和分頁

有一篇部落格極好, 把增刪改查總結得明明白白. 所以我也偷懶了, 在最後直接放出他的文章. 我這裡來補充兩個他沒有寫的: 條件查詢和分頁查詢.

條件查詢

主要的業務場景就是: 使用者傳入多個引數, 要根據引數的不同構造不同的查詢條件. 新增一個 python 檔案, 如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from sqlal_comm import Session, Product, UsingAlchemy

is_available = 1


# 重新插入資料
def re_insert_data():

    with UsingAlchemy() as ua:

        # 刪除所有資料
        ua.session.query(Product).filter(Product.id > 0).delete()

        data_list = []
        for i in range(0, 10):

            data = Product()
            data.name = '雙肩包%d' % i
            data.remark = '雙肩包%d' % i
            data.isBuy = is_available
            data_list.append(data)

        # 批次增加資料
        ua.session.add_all(data_list)


# 測試條件查詢
def check_search(keyword):

    re_insert_data()

    with UsingAlchemy() as ua:

        # 多條件的列表組合
        query_filter = []
        if keyword:
            like_value = '%{}%'.format(keyword)

            # 查詢 name 和 remark 欄位裡 包含查詢關鍵詞的記錄
            query_filter.append(Product.name.like(like_value) | Product.remark.like(like_value))

        # 增加另一個查詢條件作為測試
        query_filter.append(Product.isBuy == is_available)

        # 查詢結果
        data_list = ua.session.query(Product).filter(*query_filter).all()
        print('-- 記錄條數: {}'.format(len(data_list)))
        print('-- 該記錄是: %s' % data_list[0].name)

if __name__ == '__main__':
    check_search(3)

這個檔案的演示分兩步:

  1. 刪除原來資料庫的資料, 插入 10 條新的測試資料. 這樣能確保肯定有一條帶關鍵字 3 的資料.
  2. 演示瞭如何組合查詢條件進行查詢, 其中有一個帶 or 的查詢條件. 從這個例子入手, 所有查詢對你都不是難題了.

程式執行結果應該類似這樣:

-- 總用時: 0.009106 秒
-- 記錄條數: 1
-- 該記錄是: 雙肩包3
-- 總用時: 0.001323 秒

分頁查詢

增加一個新的 python 檔案, 程式碼如下:

#! /usr/bin/python
# -*- coding: UTF-8 -*-


from sqlal_comm import Session, Product, UsingAlchemy

is_available = 1


# 重新插入資料
def re_insert_data():

    with UsingAlchemy() as ua:

        # 刪除所有資料
        ua.session.query(Product).filter(Product.id > 0).delete()

        data_list = []
        for i in range(0, 10):

            data = Product()
            data.name = '雙肩包%d' % i
            data.remark = '雙肩包%d' % i
            data.isBuy = is_available
            data_list.append(data)

        # 批次增加資料
        ua.session.add_all(data_list)


# 測試分頁查詢
def check_search(page_no, page_size):

    re_insert_data()

    with UsingAlchemy() as ua:

        # 多條件的列表組合
        query_filter = list()

        # 增加另一個查詢條件作為測試
        query_filter.append(Product.isBuy == is_available)

        offset = (page_no - 1) * page_size

        # 查詢結果
        data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()
        print('=== 記錄條數: {}'.format(len(data_list)))
        for data in data_list:
            print('-- 記錄: ' + data.name)

if __name__ == '__main__':

    page_size = 5
    for page_no in range(1, 3):
        check_search(page_no, page_size)

可以看到分頁查詢就是在獲取列表之前呼叫 limit 和 offset 方法, 也就是這句:

data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()

所以 , 也是很簡單的.

更多內容可以學習《測試工程師 Python 工具開發實戰》書籍、《大話效能測試 JMeter 實戰》書籍

相關文章