I've got a a database filled with lots of data. Let's make it simple and say the schema looks like this:

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')
)

There are lots of rows with lots of values, but let's say I've just done this:

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

I have match lists for each of the values, and I want to return rows that match the UNION of all list values. For this question, assume no lists are empty.

Say I have these match lists...

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

I can build a text-based query correctly, using something like this..

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)

• Output should be just yyy.
• xxx is NOT chosen because col3 is a and not in the col3 match list.
• zzz is NOT chosen because col2 is an and not in the col2 match list.

How would I do this using the safer qmark style, like my 'INSERT' above?

edit: I just realized that I screwed up the notion of 'row' and 'col' here... Sorry for the confusion! I won't change it because it has perpetuated into the answer below...

CodePudding user response：

What you want can be done like this:

# 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

Or if the number of values may vary, like this:

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)