Python SQLite3: Select from table by multiple keys and list of key values, using parametrization if possible

Question

If I query SqLite table using single key, I can use the following code for parametrization:

contact_phones_list = ['+123456789', '+987654321']
q = "select * from {} WHERE user_phone in ({})".format(
                      my_table_name, 
                      ', '.join('?' for _ in contact_phones_list)
                      )

res = self.cursor.execute(q, contact_phones_list).fetchall()

Now I want to query for key pairs for which I have values:

keys = ['user_phone', 'contact_phone']
values = [('+1234567', '+1000000'), ('+987654321', '+1200000')]
q = "select contact_phone, is_main, aliases from {} WHERE ({}) in ({})".format( 
                 my_table_name, 
                 ', '.join(keys), 
                 ', '.join('(?, ?)' for _ in values)
                 )

res = self.cursor.execute(q, values).fetchall()

I'm getting error "row value misused". I tried many combinations with sublist instead of tuple, single "?", etc.

How can I create parametrization in this case?

EDIT: adding "VALUES" keyword and flattening list works:

keys = ['user_phone', 'contact_phone']
values = [('+1234567', '+1000000'), ('+987654321', '+1200000')]
values_q = []
for v in values:
    values_q += [v[0], v[1]]

q = "select * from my_table_name WHERE ({}) IN (VALUES {})".format(
    ', '.join(keys), 
    ', '.join('(?, ?)' for _ in values)
    )
res = cursor.execute(q, values_q).fetchall()

Is this a workaround or only acceptable solution?

Shawn · Accepted Answer · 2018-08-12 20:15:59Z

1

From the documentation:

For a row-value IN operator, the left-hand side (hereafter "LHS") can be either a parenthesized list of values or a subquery with multiple columns. But the right-hand side (hereafter "RHS") must be a subquery expression.

You're building up something that looks like (?,?) IN ((?,?), (?,?)), which doesn't meet that requirement. The syntax (?,?) IN (VALUES (?,?), (?,?)) works, though.

Also, I think you might have to flatten out that list of tuples you pass to the prepared statement, but somebody more knowledgeable about python would have to say for sure.

answered Aug 12, 2018 at 20:15

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Slowpoke Over a year ago

Actually, the following workaround seem to work: select * from my_table where (user_phone, contact_phone) in (VALUES (?,?), (?,?)). To fill up the placeholders I create list where I just concatenate values like values_q = ['+1234567', '+1000000', '+987654321', '+1200000']

Slowpoke Over a year ago

I just don't know which parts of SQL statement are being filled by library

Collectives™ on Stack Overflow

Python SQLite3: Select from table by multiple keys and list of key values, using parametrization if possible

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related