peewee将log转可执行SQL

问题描述

调用 BaseQuery.sql() 可以获得一个二元组，包含 SQL 和参数，将这个二元组转成可执行 SQL

peewee 版本

pip install peewee==3.14.4

示例

import datetime
from peewee import *

db = SqliteDatabase('test')


class Product(Model):
    id = PrimaryKeyField()
    name = CharField()
    type = CharField()
    create_at = DateTimeField(null=True)

    class Meta:
        database = db


db.connect()
db.create_tables([Product])
data = [
    Product(name='apple', type='fruit', create_at=datetime.datetime(2021, 1, 1)),
    Product(name='banana', type='fruit', create_at=datetime.datetime(2021, 1, 2)),
    Product(name='cat', type='animal', create_at=None),
    Product(name='dog', type='animal', create_at=datetime.datetime.now()),
]
Product.bulk_create(data)

lastest = Product.select(
    Product.id, fn.MAX(Product.create_at).alias('create_at')
).group_by(Product.type).alias('lastest')

query = Product.select().join(
    lastest, on=((Product.id == lastest.c.id) & (Product.create_at == lastest.c.create_at))
).where(
    (Product.create_at > datetime.datetime(2021, 1, 1) &
     Product.create_at.is_null(False) &
     Product.create_at != datetime.datetime.min)
)
print(query.sql())
# ('SELECT "t1"."id", "t1"."name", "t1"."type", "t1"."create_at" FROM "product" AS "t1" INNER JOIN (SELECT "t1"."id", MAX("t1"."create_at") AS "create_at" FROM "product" AS "t1" GROUP BY "t1"."type") AS "lastest" ON (("t1"."id" = "lastest"."id") AND ("t1"."create_at" = "lastest"."create_at")) WHERE (((? AND ("t1"."create_at" IS NOT ?)) AND "t1"."create_at") != ?)', [datetime.datetime(2021, 1, 1, 0, 0), None, datetime.datetime(1, 1, 1, 0, 0)])

解决方案

替换元组参数

代码

import re
import datetime  # eval()时含有datetime类型，不能去掉


def log2sql(log):
    """日志转SQL"""
    datetime_pattern = "(\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2}:\d{1,2})"  # 时间字符串正则表达式
    datetime_repl = lambda x: '"{}"'.format(x.group())
    result = []
    for s in log.strip().split("\n"):
        sql, parameters = eval(s)
        for parameter in parameters:
            sql = sql.replace("?", str(parameter), 1)
        sql = sql.replace("None", "NULL")
        sql = re.sub(pattern=datetime_pattern, repl=datetime_repl, string=sql)  # 给时间字符串加上双引号
        result.append(sql)
    return result


if __name__ == '__main__':
    log = """
('SELECT "t1"."id", MAX("t1"."create_at") AS "create_at" FROM "product" AS "t1" GROUP BY "t1"."type"', [])
('SELECT "t1"."id", "t1"."name", "t1"."type", "t1"."create_at" FROM "product" AS "t1" INNER JOIN (SELECT "t1"."id", MAX("t1"."create_at") AS "create_at" FROM "product" AS "t1" GROUP BY "t1"."type") AS "lastest" ON (("t1"."id" = "lastest"."id") AND ("t1"."create_at" = "lastest"."create_at")) WHERE (((? AND ("t1"."create_at" IS NOT ?)) AND "t1"."create_at") != ?)', [datetime.datetime(2021, 1, 1, 0, 0), None, datetime.datetime(1, 1, 1, 0, 0)])
"""
    result = log2sql(log)
    for sql in result:
        print(sql)
    print('共{}条'.format(len(result)))
# SELECT "t1"."id", MAX("t1"."create_at") AS "create_at" FROM "product" AS "t1" GROUP BY "t1"."type"
# SELECT "t1"."id", "t1"."name", "t1"."type", "t1"."create_at" FROM "product" AS "t1" INNER JOIN (SELECT "t1"."id", MAX("t1"."create_at") AS "create_at" FROM "product" AS "t1" GROUP BY "t1"."type") AS "lastest" ON (("t1"."id" = "lastest"."id") AND ("t1"."create_at" = "lastest"."create_at")) WHERE ((("2021-01-01 00:00:00" AND ("t1"."create_at" IS NOT NULL)) AND "t1"."create_at") != "0001-01-01 00:00:00")
# 共2条

字符串解析

代码

参考文献

1. Python ORM框架peewee
2. peewee API Documentation
3. Python正则表达式提取datetime日期+时间