sqlite 正确使用Qmark(或命名)样式进行SQL查询

u4dcyp6a  于 2023-02-05  发布在  SQLite
关注(0)|答案(1)|浏览(137)

我有一个数据库,里面有很多数据,让我们把它简单化,假设这个模式看起来像这样:

CREATE TABLE foo (
  col1   CHAR(25)    PRIMARY KEY,
  col2   CHAR(2)     NOT NULL,
  col3   CHAR(1)     NOT NULL
  CONSTRAINT c_col2 (col2 = 'an' OR col2 = 'bx' OR col2 = 'zz')
  CONSTRAINT c_col3 (col3 = 'a' OR col3 = 'b' OR col3 = 'n')
)

有很多行包含很多值,但假设我刚刚执行了以下操作:

cur.executemany('INSERT INTO foo VALUES(?, ?, ?)', [('xxx', 'bx', 'a'),
                                                    ('yyy', 'bx', 'b'),
                                                    ('zzz', 'an', 'b')])

我有每个值的匹配列表,我想返回与所有列表值的UNION匹配的行。对于这个问题,假设没有列表是空的。
假设我有这些匹配列表...

row2 = ['bx', 'zz']   # Consider all rows that have 'bx' OR 'zz' in row2
row3 = ['b']          # Consider all rows that have 'b' in row3

我可以正确地构建一个基于文本的查询,使用类似这样的东西。

s_row2 = 'row2 IN (' + ', '.join('"{}"'.format(x) for x in row2) + ')'
s_row3 = 'row3 IN (' + ', '.join('"{}"'.format(x) for x in row3) + ')'

query = 'SELECT col1 FROM foo WHERE ' + ' AND '.join([s_row2, s_row3])
for row in cur.execute(query):
   print(row)
  • 输出应为yyy
  • 未选择xxx,因为col3a且不在第3列匹配列表中。
  • 未选择zzz,因为col2an且不在第2列匹配列表中。

我如何使用更安全的 qmark 样式来实现这一点,就像上面的'INSERT'一样?

dluptydi

dluptydi1#

你想要的可以这样做:

# Combine the values into a single list:
vals = row2 + row3
# Create query string with placeholders:
query = """SELECT col1 FROM foo WHERE col2 IN (?, ?) AND col3 IN (?)"""
cur.execute(query, vals)
for row in cur:
    print row

或者,如果值的数量可能不同,如下所示:

rows = [row2, row3] 
# Flatten the list of rows to get scalar values.                                                          
vals = [x for y in rows for x in y] 
# Generate placeholders for each row.                                          
placeholders = (', '.join(['?'] * len(row)) for row in rows)                  
# Create query string with placeholders for placeholders.
query = """SELECT col1 FROM foo WHERE col2 IN ({}) AND col3 IN ({})"""
# Replace placeholders with placeholders.
query = query.format(*placeholders)

for row in cur.execute(query, vals):
    print(row)

相关问题