SQL空参数值

Question

我有一个查询，它返回我的数据库中单个产品的月销售额之和。查询工作正常

不是为“=？”设置一个值，而是在“=？”中存储一个空值，而不影响查询。例如，如果我输入'Espresso'作为产品，省略milkOptions，输入'Small'作为大小，我是否可以返回所有小Espresso的查询结果。我想要这个特性是因为我想要不同程度的特异性

import sqlite3
product = input("Enter product")
milkOptions = input("Enter milkoptions")
size = input("Enter size")

conn=sqlite3.connect("system.db")
cur=conn.cursor()
test = cur.execute("""
select count(product), strftime("%m-%Y", orderDate) as month 
from customerOrders
where product = ? and milkOptions = ? and size = ? 
group by product, month""",(product, milkOptions, size)).fetchall()

+---------+---------------+--------+-------------+------------+
| orderid |    product    |  size  | milkOptions | orderDate  |
+---------+---------------+--------+-------------+------------+
|       1 | Espresso      | Small  | Soya        | 2019-10-29 |
|       2 | Cappucino     | Small  | SemiSkimmed | 2019-10-29 |
|       3 | Cappucino     | Small  | SemiSkimmed | 2019-10-29 |
|       4 | Cappucino     | Medium | SemiSkimmed | 2019-10-29 |
|       5 | Hot Chocolate | Medium | Coconut     | 2019-10-29 |
|       6 | Hot Chocolate | Medium | Coconut     | 2019-10-29 |
+---------+---------------+--------+-------------+------------+

Answer 1

如果列没有notnull约束，可以将其值指定为NULL

if milkOptions == "":
    milkOptions = "NULL"

执行此语句后插入数据库。这是一个相当不雅观的解决办法，但它的工作