python Mysql 多条件查询
做项目时,遇到一场景,前端至少传入一个参数,最多传入四个参数,根据单参数或者组合参数,从数据库筛选数据。
作为一个小白,思考之,从数学的角度,\(C_4^1 + C_4^2+C_4^3+C_4^4=15\),靠,写SQL要写15种?
放弃,试图寻找简便方法,寻之半天,未果。
默默接受了寻找捷径所损耗的时间还不如直接用原始的方法,老老实实,一个一个一个一个(还有11个一个)的写。
可写的时候内心始终在挣扎,写到一半时,r忍不住,找大神问了问。
大神曰:“傻不傻逼?为啥要传参!!!传参传死人。构造形式拼接啊,前端接入的参数,用字典接收,然后,将参数字典转换为字符串,在拼接到SQL语句后,构成SQL语句。”
醍醐灌顶,有没有?
下面记录原过程以及新代码如下,如有相似情境,可借鉴。
原代码结构
(代码过多,只看结构,PS:还没写完的条件。。)
经大神指点后的代码:
def user_list_post(username=None, phone=None, dispost_status=None, agentname=None):
dic = {"`userName`": username,
"`phoneNumber`": phone,
"`dispost`": dispost_status,
"`agentName`": agentname}
print(dic)
sr = ""
if dic["`dispost`"] == "已缴纳":
dic["`dispost`"] = "200"
elif dic["`dispost`"] == "未缴纳":
dic["`dispost`"] = "0"
for k, v in dic.items():
if v:
sr += k + " = " + "'" + v + "'"+ " and "
print(sr.rstrip(" and "), 2222)
sql_fro = """select * from
(select user_list.`userId`, user_list.`userName`, order_list.`agentName`,
user_list.`headUrl`, user_list.`phoneNumber`, user_list.`balance`,
user_list.`dispost`, user_list.`registerTime`
from order_list join user_list on order_list.`userId` = user_list.`userId`)
as tmp
where """
sql = sql_fro + sr.rstrip(" and ")
print(sql, 33333)
sql_datas = mysql_func(sql)
# print(sql_datas)
user_datas = list()
for sql_data in sql_datas:
user_dic = dict()
userId = sql_data[0]
userName = sql_data[1]
agentName = sql_data[2]
headUrl = sql_data[3]
phoneNumber = sql_data[4]
balance = sql_data[5]
dispost = sql_data[6]
registerTime = sql_data[7]
user_dic["userId"] = userId
user_dic["userName"] = userName
user_dic["agentName"] = agentName
user_dic["headUrl"] = headUrl
user_dic["phoneNumber"] = phoneNumber
user_dic["balance"] = balance
if int(dispost) > 0:
user_dic["dispost_status"] = "已缴纳"
else:
user_dic["dispost_status"] = "未缴纳"
user_dic["registerTime"] = registerTime
user_datas.append(user_dic)
return user_datas
构造形式2
def consumer_order_post(start_time=None, end_time=None, order_number=None,
user_name=None, phone_number=None, agent_name=None):
"""
查询条件
:param start_time: 开始条件
:param end_time: 结束条件
:param order_number: 订单编号
:param user_name: 用户昵称
:param phone_number: 手机号
:param agent_name: 代理商
:return:
"""
dic = {"A.`startTime`": start_time,
"A.`endTime`": end_time,
"A.`orderNumber`": order_number,
"A.`userName`": user_name,
"A.`phoneNumber`": phone_number,
"A.`agentName`": agent_name
}
sr = ""
char = {"1": ">=",
"2": "<=",
"3": "="}
for key, value in dic.items():
if value:
if key == "A.`startTime`":
tmp = char.get("1")
elif key == "A.`endTime`":
tmp = char.get("2")
else:
tmp = char.get("3")
sr += key + tmp + "'" + value + "'" + " and "
print(sr.rstrip(" and "))