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)
            cursor.connection.commit() # 这里必须要提交，不然所有的查询都会处于一个事务中
            rows: tuple[tuple] = cursor.fetchall()

            for row in rows:
                logger.debug(row)

因为 peewee 关闭了自动提交，所以查询后也要自己手动 commit 哦
使用 peewee 的 MODEL.select() 这种方式，是会自动 commit 的（前提是不开事务）

执行结果

─➤  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.

---

但是需要注意，peewee 后在创建 db conn 的时候，帮你做这些事情：

• SET sql_mode='PIPES_AS_CONCAT'
• SET AUTOCOMMIT = 0

注意第二个 SET AUTOCOMMIT = 0 ！