【python 动态拼接where查询】where条件动态变化

应用场景:有些查询sql接口 地方where条件动态变化。后端如何根据where条件变化。下面是一个简单的实现。


# -*- coding:utf-8 -*-
import cx_Oracle
from DBUtils.PooledDB import PooledDB
from webapi_function import *
from config import *
dsn = cx_Oracle.makedsn(ip, port, sid)
pool = PooledDB(cx_Oracle, mincached=20, blocking=True, user=user, password=password, dsn=dsn)
import pandas as pd



dict_info=dict()


# dict_info['id']=''
dict_info['word']='微信'
# dict_info['type']='恶意推广'
# dict_info['platform']='kkty'
# dict_info['gmtCreateStart']='2019-01-01 10:01:14'
# dict_info['gmtCreateEnd']='2019-11-09 15:20:10'


for key in list(dict_info.keys()):
    if not dict_info.get(key):
        del dict_info[key]



dict_info_len=len(dict_info)
print(dict_info_len)


if dict_info_len==0:
    sql_str="select * from ZK_SENSITIVE_WORDS"

    conn = pool.connection()
    cursor = conn.cursor()
    query = sql_str
    cursor.execute(query)
    ph = cursor.fetchall()

    data = pd.DataFrame(ph)
    print(data)
    cursor.close()
    conn.close()

elif dict_info_len==1:
    res = ''
    for item in dict_info:
        res = res + str(item) + '=' + """'%s'"""%(dict_info[item])
        sql_str="select * from ZK_SENSITIVE_WORDS where "+res

    print(sql_str)

    conn = pool.connection()
    cursor = conn.cursor()
    query = sql_str
    cursor.execute(query)
    ph = cursor.fetchall()

    data = pd.DataFrame(ph)


    # 修改列名
    data.columns = ['id', 'word', 'type', 'list_type', 'gmt_create', 'gmt_modified', 'source_type', 'enabled',
                    'platform']

    print(data)

    # 数据框转换成json list
    return_result_dict=dict()
    return_result_dict['errmsg']='ok'
    return_result_dict['errcode']='200'
    result=list()

    print(len(return_result_dict))
    for i in range(0,len(data)):
        print(data.iloc[i,0])



    cursor.close()
    conn.close()


elif dict_info_len>=2:
    res = ''

    k1 = ('gmtCreateStart' in dict_info.keys())
    k2 = ('gmtCreateEnd' in dict_info.keys())

    if (k1 is True) & (k2 is True):
        res= res+"to_char(GMT_CREATE,'yyyy-mm-dd hh24:mi:ss')"+'>='+"""'%s'"""%(dict_info.get('gmtCreateStart'))+'\t'+'and'+'\t'+"to_char(GMT_CREATE,'yyyy-mm-dd hh24:mi:ss')"+'<='+"""'%s'"""%(dict_info.get('gmtCreateEnd'))+\
             '\t'+'and'+'\t'

        del dict_info['gmtCreateStart']
        del dict_info['gmtCreateEnd']

        for item in dict_info:
            res = res + str(item) + '='+ """'%s'"""%(dict_info[item])+'\t'+'and'+'\t'
        res=res[:-5]
        print(res)
        sql_str = "select * from ZK_SENSITIVE_WORDS where " + res

        print(sql_str)

    else:
        for item in dict_info:
            res = res + str(item) + '='+ """'%s'"""%(dict_info[item])+'\t'+'and'+'\t'
        res=res[:-5]
        print(res)

        sql_str = "select * from ZK_SENSITIVE_WORDS where " + res
        print(sql_str)



    conn = pool.connection()
    cursor = conn.cursor()
    query = sql_str
    cursor.execute(query)
    ph = cursor.fetchall()



    data = pd.DataFrame(ph)
    print(data)
    cursor.close()
    conn.close()





已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页