peewee 執行原生 sql

peewee 很適合用來處理一些簡單的 crud 任務

如果是複雜的查詢語句，我更加喜歡用 raw sql 來處理

用 join 查詢來舉例子：

models.py

from peewee import *
import settings

host = settings.DATABASE.host
port = settings.DATABASE.port
username = settings.DATABASE.username
password = settings.DATABASE.password
database_name = settings.DATABASE.database

db = MySQLDatabase(
    database=database_name,
    host=host,
    port=port,
    user=username,
    password=password
)


class User(Model):
    id = AutoField()
    name = CharField(max_length=255, null=False)
    created_at = DateTimeField(
        null=False,
        constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')],
        help_text='使用資料庫時間'
    )
    updated_at = DateTimeField(
        null=False,
        constraints=[
            SQL('DEFAULT CURRENT_TIMESTAMP'),
            SQL('ON UPDATE CURRENT_TIMESTAMP'),
        ]
    )

    class Meta:
        database = db
        table_name = 'account'


class Order(Model):
    """ 訂單資訊表 """
    id = AutoField(primary_key=True)
    user_id = IntegerField(null=False, unique=False, index=True)
    product_id = IntegerField(null=False, unique=False)
    activity_id = IntegerField(null=False, unique=False)
    is_paid = BooleanField(null=False, unique=False, default=False)
    created_at = DateTimeField(
        null=False,
        constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')]
    )
    updated_at = DateTimeField(
        null=False,
        constraints=[
            SQL('DEFAULT CURRENT_TIMESTAMP'),
            SQL('ON UPDATE CURRENT_TIMESTAMP'),
        ]
    )

    class Meta:
        database = db
        table_name = 'order'
        # http://docs.peewee-orm.com/en/latest/peewee/models.html?highlight=table%20generation#multi-column-indexes
        indexes = (
            # create a unique on,限購一次
            (('user_id', 'product_id', 'activity_id'), True),
        )

查詢一些資料

order 表

insert into `order` (user_id,product_id,activity_id,is_paid) values (1,2,3,0)

insert into `order` (user_id,product_id,activity_id,is_paid) values (2,3,4,0)

account 表

insert into `account` (name) values ('jike')
insert into `account` (name) values ('ponponon')

看看插入後的結果

MySQL root@192.168.31.245:seckill> select * from account;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | updated_at          |
+----+----------+---------------------+---------------------+
| 1  | jike     | 2022-04-06 13:26:28 | 2022-04-06 13:31:34 |
| 2  | ponponon | 2022-04-06 13:26:35 | 2022-04-06 13:26:35 |
+----+----------+---------------------+---------------------+
2 rows in set
Time: 0.005s

各兩條

MySQL root@192.168.31.245:seckill> select * from `order`
+----+---------+------------+-------------+---------+---------------------+---------------------+
| id | user_id | product_id | activity_id | is_paid | created_at          | updated_at          |
+----+---------+------------+-------------+---------+---------------------+---------------------+
| 1  | 1       | 2          | 3           | 0       | 2022-04-06 13:25:19 | 2022-04-06 13:25:19 |
| 2  | 2       | 3          | 4           | 0       | 2022-04-06 13:25:28 | 2022-04-06 13:25:28 |
+----+---------+------------+-------------+---------+---------------------+---------------------+
2 rows in set
Time: 0.007s

使用單元測試的指令碼來驗證

import unittest
from loguru import logger
from models import db
from pymysql.cursors import Cursor


class TestProject(unittest.TestCase):
    def peewee_exec_raw_sql(self):
        """
        python -m unittest tests.TestProject.peewee_exec_raw_sql
        """
        # cursor = db.cursor()

        with db.cursor() as cursor:
            cursor: Cursor
            sql = """
            SELECT 
                `account`.`id`,
                `account`.`name`,
                `order`.`product_id`
            FROM 
                `account`
                INNER JOIN `order` ON (`account`.`id` = `order`.`user_id`)
            """
            cursor.execute(sql)
            rows: tuple[tuple] = cursor.fetchall()

            for row in rows:
                logger.debug(row)

執行結果

─➤  python -m unittest tests.TestProject.peewee_exec_raw_sql
2022-04-06 13:37:20.893 | DEBUG    | tests:peewee_exec_raw_sql:36 - (1, 'jike', 2)
2022-04-06 13:37:20.894 | DEBUG    | tests:peewee_exec_raw_sql:36 - (2, 'ponponon', 3)
.
----------------------------------------------------------------------
Ran 1 test in 0.005s

參考文章：
Python程式設計：peewee執行多條原生sql語句
This document describes changes to be aware of when switching from 2.x to 3.x.