python peewee模块执行原生sql语句的方法

由于公司要求使用peewee模块，不能使用pymysql，而我又不是特别喜欢ORM的方式，很喜欢原生sql，所以，我决定重新封装peewee

 

代码如下：

from peewee import MySQLDatabase


class base_peewee(object):
    def __init__(self, host=None, port=3306, user=None, password=None, db_name=None):
        self.db_host = host
        self.db_port = int(port)
        self.user = user
        self.password = str(password)
        self.db = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        # self.conn = MySQLDatabase(
        #     host=self.db_host,
        #     port=self.db_port,
        #     user=self.user,
        #     passwd=self.password,
        #     database=self.db,
        #     charset="utf8"
        # )
        self.conn = MySQLDatabase(
            host="127.0.0.1",
            port=3306,
            user="root",
            passwd="123123qwe",
            database='test',
            charset="utf8"
        )
        self.cursor = self.conn.cursor()


class ReDefinedPeeWee(base_peewee):

    def __init__(self):
        super(ReDefinedPeeWee, self).__init__()
        self.connect()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def insert_sql(self, sql, value=None, commit=None):
        self.cursor.execute(sql, value)
        if commit:
            self.commit()

    def update_sql(self, sql, value=None, commit=None):
        self.cursor.execute(sql, value)
        if commit:
            self.commit()

    def delete_sql(self, sql, value=None, commit=None):
        self.cursor.execute(sql, value)
        if commit:
            self.commit()

    def selectone_sql(self, sql, columns=None):
        """

        :param sql:
        :param columns: ['id', 'name'...] 要求sql的返回数据相同
        :return:
        """
        self.cursor.execute(sql)
        self.conn.commit()
        if not columns:
            return self.cursor.fetchone()
        else:
            data = self.cursor.fetchone()
            if data and len(data) == len(columns):
                return dict(zip(columns, data))
            else:
                return data

    def selectall_sql(self, sql, columns=None):
        self.cursor.execute(sql)
        self.conn.commit()
        if not columns:
            return self.cursor.fetchall()
        else:
            data = self.cursor.fetchall()

            if len(data) > 0 and len(data[0]) == len(columns):
                return [dict(zip(columns, i)) for i in data]
            else:
                return data

    def select_sql(self, sql, value=None, columns=None):
        self.cursor.execute(sql, value)
        self.conn.commit()
        return self.cursor.fetchall()

    def close(self):
        self.cursor.close()
        self.conn.close()
        self.conn = None
        self.cursor = None


def main():
    ret = ReDefinedPeeWee()
    res = ret.selectone_sql("select * from test", )
    print(res)
    res1 = ret.selectone_sql("select * from test", ["id", 'name', "num"])
    print(res1)
    ret.close()


if __name__ == '__main__':
    main()

 

 

如果用peewee写原生的方式就是这么玩

from peewee import MySQLDatabase, Model
from marshmallow import Schema, fields
from peewee import PrimaryKeyField, IntegerField, CharField, FloatField, DoubleField

# MYSQL 配置

# db = MySQLDatabase('test',
#                    user='root',
#                    password='123123qwe',
#                    host='127.0.0.1',
#                    port=3306)


db = MySQLDatabase('',
                   user='root',
                   password='',
                   host='',
                   port=37214
                   )


class BaseModel(Model):
    class Meta:
        database = db


class TestFactor(BaseModel):
    id = PrimaryKeyField()
    type = IntegerField(verbose_name="类型")
    name = CharField(verbose_name="姓名")
    num = FloatField(verbose_name="浮点")

    class Meta:
        database = db
        # order_by = ('id',)
        db_table = 'test1'


class TestFactor_(Schema):
    id = fields.Integer()
    type = fields.Integer()
    name = fields.String()
    num = fields.Float()
    name_level = fields.Method('get_name_level')

    def get_name_level(self, item):
        if item.type == 1:
            status = '正常'
        elif item.type == 2:
            status = "低危"
        elif item.type == 3:
            status = "高危"
        else:
            status = "正常"
        return status


# 健康管理监测值存储类
class HealthHouseKeeperMonitoringValue(BaseModel):
    id = PrimaryKeyField()
    user_id = IntegerField(verbose_name="用户ID")
    type_id = IntegerField(verbose_name="类型")
    monitoring_value = CharField(verbose_name="监测值")
    report_filepath = CharField(verbose_name="文档路径")
    create_time = IntegerField(verbose_name="创建时间")
    update_time = IntegerField(verbose_name="更新时间")
    status = IntegerField()

    class Meta:
        order_by = ('id',)
        db_table = 'wechat_health_housekeeper_monitoringvalue'


class HealthHouseKeeperMonitoringValueSerializer(Schema):
    id = fields.Integer()
    user_id = fields.Integer()
    type_id = fields.Integer()
    type_level = fields.Method("get_type_level")
    monitoring_value = fields.String()
    report_filepath = fields.String()
    create_time = fields.Integer()
    update_time = fields.Integer()
    status = fields.Integer()

    def get_type_level(self, item):
        if item.type_id == 1:
            status = '血压'
        elif item.type_id == 2:
            status = "心率"
        elif item.type_id == 3:
            status = "低密度脂蛋白胆固醇"
        elif item.type_id == 4:
            status = "空腹血糖"
        elif item.type_id == 5:
            status = "甘油三酯"
        elif item.type_id == 6:
            status = "糖化血红蛋白"
        elif item.type_id == 7:
            status = "总胆固醇"
        elif item.type_id == 8:
            status = "BMI"
        return status


def test():
    # get方法-单条数据
    # detail = TestFactor.get() # 只查一条，没有则报错
    # data = TestFactor_(many=False) # 展示数据
    # print(data.dump(detail))

    # get_or_none方法-单挑数据
    # detail = TestFactor.get_or_none() # 没有不报错，只查询一条
    # data = TestFactor_(many=False) # 展示数据
    # print(data.dump(detail))

    # 多条数据
    # detail = TestFactor.select()
    # many=False表示只有一条， exclude表示不展示某些列, only表示只展示某些列
    # data = TestFactor_(many=False, exclude=[], only=())
    # data = TestFactor_(many=False, exclude=[])
    # print([data.dump(i) for i in detail])

    detail = HealthHouseKeeperMonitoringValue.select().where(
        HealthHouseKeeperMonitoringValue.user_id == 180,
        HealthHouseKeeperMonitoringValue.status == 1,
        HealthHouseKeeperMonitoringValue.type_id == 1,
    ).order_by(HealthHouseKeeperMonitoringValue.create_time.desc())
    print(detail)
    data_serial = HealthHouseKeeperMonitoringValueSerializer()
    print(len(detail))
    print(data_serial.dump(detail[0]))
    # user_detail = RiskFactorSerializers(many=False, exclude=['create_time', 'id', 'chronic_id', 'user_id'])


if __name__ == '__main__':
    test()

 

用ORM的Peewee模块查询返回字典模式，哈哈哈，无意发现的，啦啦啦，如果你用到了请点赞+关注，谢谢

basemodel

class CaseAdjusterResult(BaseModel):
    """
    理算结果（后台）
    """
    id = PrimaryKeyField()
    case_id = IntegerField()
    assurer_id = IntegerField()
    case_no = CharField()
    adjuster_id = IntegerField()
    fee_adjuster_id = IntegerField()
    self_charge_1 = FloatField()
    self_charge_2 = FloatField()
    self_charge = FloatField()
    year_first_hospital_self_charge_1 = FloatField()
    year_first_hospital_self_charge_2 = FloatField()
    year_first_hospital_self_charge = FloatField()
    year_no_first_hospital_self_charge_1 = FloatField()
    year_no_first_hospital_self_charge_2 = FloatField()
    year_no_first_hospital_self_charge = FloatField()
    year_hospital_self_charge_1 = FloatField()
    year_hospital_self_charge_2 = FloatField()
    year_hospital_self_charge = FloatField()
    limit_charge = FloatField()
    injury = FloatField()
    special_disease_self_charge_1 = FloatField()
    special_disease_self_charge_2 = FloatField()
    special_disease_self_charge = FloatField()
    serious_illness_self_charge_1 = FloatField()
    serious_illness_self_charge_2 = FloatField()
    serious_illness_self_charge = FloatField()
    work_injury_self_charge_1 = FloatField()
    work_injury_self_charge_2 = FloatField()
    work_injury_self_charge = FloatField()
    special_limit_charge = FloatField()
    status = IntegerField()
    create_time = IntegerField()
    update_time = IntegerField()

    class Meta:
        order_by = ('id',)
        db_table = 'case_adjuster_result'

查询代码，返回的结果是字典的形式

 

case_no = "wechat_20200817271"
# 一条数据这么玩
# case_adjuster_result = CaseAdjusterResult.get(CaseAdjusterResult.case_no == case_no)
# data = case_adjuster_result.__dict__["__data__"]　　---》就是这句，哈哈哈哈，可以直接拿到字典类型的数据
# print(data)

# 多条数据这么玩
case_adjuster_result = CaseAdjusterResult.select()　　　　--> 这个地方也可以select().get()看源码就是这么玩的
for son_sql_obj in case_adjuster_result:
    data = son_sql_obj.__dict__.get("__data__", {})
    print(data)

　　
　　如果你用的，请点赞 + 评论 + 关注，一件三联，哈哈哈哈

　　呸，好不要脸，没办法，程序员的快乐也就这么点了：）

 

 

peewee 怎么模拟SQL里面的select count(1) as count from table group by的写法

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-

from hyh.model.model import XueShuPaper
from peewee import fn

data = XueShuPaper.select(XueShuPaper.technology_crawler_keyword, fn.COUNT(XueShuPaper.id).alias("count")).group_by(XueShuPaper.technology_crawler_keyword)
aaa = []
print(data)
for i in data:
    new_son_data = {
        "crawler_keyword": i.__dict__.get("__data__").get("technology_crawler_keyword"),
        "count": i.__dict__.get("count")
    }
    print(new_son_data)

 

 

问题，由于出现了tornado + peewee阻塞的情况

这里注意个问题，如果大批量访问，长时间访问，就会出现访问没有数据，阻塞的情况，因为一般大家tornado都是用同步的形式

那解决方案呢

先说为什么

peewee 的连接池，使用时需要显式的关闭连接。下面先说下为什么，最后会给出推荐的使用方法，避免进坑。

为什么要显式的关闭连接

Connections will not be closed exactly when they exceed their stale_timeout. Instead, stale connections are only closed when a new connection is requested.

这里引用官方文档的提示。大致说：“超时连接不会自动关闭，只会在有新的请求时是才会关闭”。这里的request是指‘web 框架处理的请求’，peewee 源码片段：

 

根据 pool 库中的 _connect 方法的代码可知：每次在建立数据库连接时，会检查连接实例是否超时。但是需要注意一点：使用中的数据库连接实例（_in_use dict中的数据库连接实例) 是不会在创建数据库连接时，检查是否超时的。

因为这段代码中，每次创建连接实例，都是在 _connections(pool) 取实例，如果有的话就判断是否超时；如果没有的话就新建。

然而，使用中的数据库连接并不在 _connections 中，所以每次创建数据库连接实例时，并没有检测使用中的数据库连接实例是否超时。

只有调用连接池实例的 _close 方法。执行这个方法后，才会把使用后的连接实例放回到 _connections (pool)。

 

 如果不显式的关闭连接，会出现的问题

如果不调用_close方法的话，使用后 的数据库连接就一直不会关闭(两个含义：回到pool中和关闭数据库连接)，这样会造成两个问题：

1. 每次都是新建数据库连接，因为 pool 中没有数据库连接实例。会导致稍微有一点并发量就会返回Exceeded maximum connections.错误
2. MySQL也是有 timeout 的，如果一个连接长时间没有请求的话，MySQL Server 就会关闭这个连接，但是，peewee的已建立（后面会解释为什么特指已建立的）的连接实例，并不知道 MySQL Server 已经关闭了，再去通过这个连接请求数据的话，就会返回 Error 2006: “MySQL server has gone away”错误，根据官方文档

 

 

解决方案

  在return的地方，进行一个判断
  if not database.is_closed():
        database.close()

 

已经进行了代码改进，但是还没有进行大批量测试

参考连接：https://www.cnblogs.com/xueweihan/p/6698456.html   ，这里说一声感谢