python – 数据库查询条件有多个值时如何处理 where id in (, , ,)

这个问题困扰了好久, 没有在网上找到全一点的处理方法. 现在解决了, 记录一下处理的办法:

#ask customer to order food
print("\n which kind of pizza would you like to order? Please input the index number of them."
      "\n (note: Please separate them with commas, like 1,2,3. When you have finished typing, press ' Enter '):")
orderFood = list(map(int, input().split(',')))

#设置有几个%s的方法
s1 = "select id,name,note,price from foodlist where id in (%s)" % ','.join(['%s'] * len(orderFood))
cur.execute(s1,(orderFood))
复制代码

得到的结果如下图:
可以导入pandas, 用DataFrame绘表:

#ask customer to order food
print("\n which kind of pizza would you like to order? Please input the index number of them."
      "\n (note: Please separate them with commas, like 1,2,3. When you have finished typing, press ' Enter '):")
orderFood = list(map(int, input().split(',')))


#show order information
print("\n Food items you have ordered are as follows: \n")

s1 = "select id,name,note,price from foodlist where id in (%s)" % ','.join(['%s'] * len(orderFood))
cur.execute(s1,(orderFood))

orderInfo = pd.DataFrame(list(cur.fetchall()),columns=["index number","name","note","price"])
print(orderInfo)

# calculate total cost
s2 = "select price from foodlist where id in (%s)"%",".join(['%s']*len(orderFood))
cur.execute(s2,orderFood)

cost = []
total = 0
for i in cur.fetchall():
    cost += i
for j in cost:
    total +=j

print("\n The total cost of your order is:",total)
复制代码

可能我算订单总额的方法不是太好, 但是我试了其他方法都报错了, 如果大家有更好的方式, 希望可以告诉我, 谢谢~~~~