python - SQL 中是否有可选的预准备语句用于不同特定级别的查询

Question

假设我有一个查询返回数据库中单个产品的每月总销售额:

product = input("Enter product column to search")
milkOptions = input("Enter milkOptions column to search")
size = input("Enter size to search")

import sqlite3

conn=sqlite3.connect("system.db")
cur=conn.cursor()
sql ="""
select sum(quantity*price), strftime("%m-%Y", orderDate) 
as month 
from customerOrders
WHERE product = ? and milkOptions = ? and size = ?
group by orderDate"""

有没有一种方法可以查询各种级别的特异性，而无需创建许多类似的查询。例如，如果我为产品输入浓缩咖啡，省略牛奶选项条目，并输入小尺寸，我可以退回所有小浓缩咖啡销售吗？反之亦然，可以省略并输入其他选项。

这是我的数据库:

+---------+-----------+--------+-------------+------------+----------+-------+------------+
| orderid |  product  |  size  | milkOptions | orderDate  | quantity | price | customerid |
+---------+-----------+--------+-------------+------------+----------+-------+------------+
|       1 | Espresso  | Small  | Soya        | 2019-10-29 |        1 | 1.0   |        1   |
|       2 | Cappucino | Small  | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
|       3 | Cappucino | Small  | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
|       4 | Cappucino | Medium | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
+---------+-----------+--------+-------------+------------+----------+-------+------------+

Answer 1

为每列传递一个附加标志参数 0 或 1，例如:

WHERE 
  (product = ? OR 0 = ?)  
  AND 
  (milkOptions = ? OR 0 = ?) 
  AND 
  (size = ? OR 0 = ?)

• 当您为 product 的标志参数传递 0 时(并且为 product 列anything 不为空) >) 条件将计算为 TRUE，这相当于不为 product 列设置条件。
  
• 当您为 product 的标志参数传递 1 并为 product 列传递 'someproduct' 时那么条件相当于 product = 'someproduct'。