python-sqlalchemy-原生sql

柴米油盐那点事儿

已于 2022-08-09 17:56:24 修改

阅读量596

点赞数 1

文章标签： python sql 开发语言

于 2022-08-04 18:28:24 首次发布

本文链接：https://blog.youkuaiyun.com/qq_36774734/article/details/126164605

版权

增删改查条件分页-仅接口测试

控制器代码

@config_blue.route('/config/save/', methods=['POST'])
def save_config():
    # authorname=request.form.get('authorname')
    if request.method == 'POST':
        post_data = request.get_json()
        # return jsonify({"code": 201, "msg": len(post_data.get('apn'))})
        if len(post_data.get('apn')) == 0:
            sql = "UPDATE `pythonConfig` SET `plmn`='',`tac`='',`sd`='',`sst`='',`apn`='',`ue_ip_end`='',`ue_ip_start`='' WHERE `id`='1'"  
            return Response.success() if pythonConfigDas.execute(sql) else Response.error()
        elif len(post_data.get('apn')) > 0: 
            plmn = repr(post_data.get('plmn')).strip()
            tac = repr(post_data.get('tac')).strip()
            sd = repr(post_data.get('sd')).strip()
            sst = repr(post_data.get('sst')).strip()
            apn =   '"'+repr(post_data.get('apn')).strip()+'"'
            ue_ip_end = repr(post_data.get('ue_ip_end')).strip()
            ue_ip_start = repr(post_data.get('ue_ip_start')).strip()
            sql = "UPDATE `pythonConfig` SET `plmn`=%s,`tac`=%s,`sd`=%s,`sst`=%s,`apn`=%s,`ue_ip_end`=%s,`ue_ip_start`=%s WHERE `id`='1'" %(plmn,tac,sd,sst,apn,ue_ip_end,ue_ip_start)
            # return jsonify({"code": 201, "msg": sql})

            sql = sql.replace('\\', " ")
            return Response.success() if pythonConfigDas.execute(sql) else Response.error()
        else:
            return jsonify({"code": 201, "msg": "操作失败"})
 
##
@config_blue.route('/config/rightList/', methods=['POST'])
def rightList_config():
    if request.method == 'POST':
        post_data = request.get_json()
        tac = repr(post_data.get('tac')).strip()
        page = post_data.get('page')    ##当前页码
        pageSize = post_data.get('pageSize')  ##每页条数
        sql = "select plmn,tac,sd,sst,apn,ue_ip_end,ue_ip_start FROM pythonConfig  where   `tac` =%s  limit %d,%d " %(tac,(page-1)*pageSize,pageSize)
        data_config = pythonConfigDas.select(sql)  ##数据
        
        ##符合条件的总个数
        count_sql = " select count(*) as count FROM pythonConfig  where `tac` =%s" %(tac)
        total = pythonConfigDas.selectCount(count_sql)[0]['count'] ##数据总条数
        pagetotal = 0 ##页码总数
        if total % pageSize == 0:
            pagetotal = total/pageSize
        else:
            pagetotal = total/pageSize+1
        ##返回数据
        return jsonify({
                "code": 200,
                "msg": "",
                "data":{
                    "page":page, ##当前页码
                    "total":total,##数据总数
                    "pagetotal":pagetotal,##页码总数
                    "list":data_config
                }   
        })

表模型


from core_network import db
from core_network.tools.warps import das_warps


class pythonConfigDas:

    ##增删改
    @staticmethod
    @das_warps
    def execute(sql):
        db.session.execute(sql)
        db.session.commit()
        return True
   
    ##查询多条数据
    @staticmethod
    def select(sql):
        data = db.session.execute(sql).fetchall()
        ##结果集必须是字典的形式，不要直接返回！！！
        result = [dict(zip(result.keys(), result)) for result in data]
        return result
    ##查询个数
    @staticmethod
    def selectCount(sql):
        count = db.session.execute(sql)
        result = [dict(zip(result.keys(), result)) for result in count]
        return result

无数据

有数据