Python通过传递多个参数从sqlite表中选择

Question

我正试图在Flask和SQLite中构建一个webapp，我有一个包含列的表：Date、Country和Name，我正在对这些数据做一个请求表单

country = request.form.getlist('country')
name = request.form.getlist('name')

str_from_date = request.form.get('from_date')
str_to_date = request.form.get('to_date')
from_date = dt.datetime.strptime(str_from_date, '%d/%m/%Y')
to_date = dt.datetime.strptime(str_to_date, '%d/%m/%Y')

我试图将这些参数传递到sql select语句中 i、 e

SELECT * FROM table WHERE country_col IN {country} AND name_col IN {name}
AND date(date_col) >= {from_date} AND date(date_col) <= {to_date}

但是我找不到一种方法来传递多个参数，所以现在我只有这个，因为cur.execute不接受多个参数

sql = 'SELECT * FROM table WHERE country_col IN ({co}) ORDER BY datetime(date_col) ASC'.format(co=','.join(['?'] * len(country)))
data = cur.execute(sql, country).fetchall()

我需要一种方法，可以将多个参数传递到sql select语句中，以便相应地查询数据

Answer 1

我想到的第一种方法是手动生成查询：

country = {
  "col": col1,
  "type": None, 
  "q": "IN", 
  "data": request.form.getlist('country')
}

name = {
  "col": col2, 
  "type": None, 
  "q": "IN", 
  "data": request.form.getlist('name')
}

str_from_date = request.form.get('from_date')
str_to_date = request.form.get('to_date')

from_date = {
  "col": col3, 
  "type": "date", 
  "q": ">=", 
  "data": dt.datetime.strptime(str_from_date, '%d/%m/%Y')
}

to_date = {
  "col": col4, 
  "type": "date", 
  "q": "<=", 
  "data": dt.datetime.strptime(str_to_date, '%d/%m/%Y')
}

all_data = [country, name_col, from_date, to_date]

q = "SELECT * FROM table WHERE "

for i, d in enumerate():
  _col = f"date({d['col']})" if d["type"] == "date" else d["col"]
  q += f"{'AND ' if i > 0 else ''}{d['col']} {d['q']} ({['?'] * len(d['data'])}) "

data = cur.execute(q, [d["data"] for d in all_data]).fetchall()

我不知道这是否是理想的方法，但我想它会起作用