peewee executes native sql

peewee good for some simple crud tasks

If it is a complex query statement, I prefer to use raw sql to handle it

Example using join query:

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),
        )

Let's insert some test data first, and then test later

order table

 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 table

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

Look at the result after inserting

 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

two each

 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

Use unit test scripts to verify

 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)

Because peewee turns off auto-commit, you have to commit manually after querying. Using peewee's MODEL.select() method will automatically commit (provided you don't open a transaction)

Results of the

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

Reference article:
Python programming: peewee executes multiple native sql statements
This document describes changes to be aware of when switching from 2.x to 3.x.

---

But it should be noted that when peewee creates db conn, it helps you to do these things:

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

Notice the second SET AUTOCOMMIT = 0 !