在sqlite python中根据用户选择更改查询

Question

I have a sqlite database named StudentDB which has 3 columns Roll number, Name, Marks. Now I want to fetch only the columns that user selects in the IDE. User can select one column or two or all the three. How can I alter the query accordingly using Python?

我试过：

import sqlite3

sel={"Roll Number":12}

query = 'select * from StudentDB Where({seq})'.format(seq=','.join(['?']*len(sel))),[i for k,i in sel.items()]
con = sqlite3.connect(database)
cur = con.cursor()
cur.execute(query)

all_data = cur.fetchall()
all_data

我得到：

operation parameter must be str

Answer 1

在代码中，query现在是一个元组，而不是str，这就是错误的原因

我假设您希望执行如下查询-

select * from StudentDB Where "Roll number"=?

然后，您可以像这样更改sql查询（假设您想要和而不是或）——

query = "select * from StudentDB Where {seq}".format(seq=" and ".join('"{}"=?'.format(k) for k in sel.keys()))

并执行如下查询-

cur.execute(query, tuple(sel.values()))

请确保在代码中定义了提供的database并包含数据库名称，并且studentDB确实是表名而不是数据库名

Answer 2

您应该控制查询的文本。如果要构建参数化查询，where子句的格式应始终为WHERE colname=value [AND colname2=...]或（更好的）WHERE colname=? [AND ...]

所以你想要：

query = 'select * from StudentDB Where ' + ' AND '.join('"{}"=?'.format(col)
                                                        for col in sel.keys())
...
cur.execute(query, tuple(sel.values()))