Execute a query for multiple sets of parameters with psycopg2

I have a table that I want to query, but I want to make many specific queries and return a table of any results that have met their condition, and ignore queries that do not exist.

data = (
    (1, '2020-11-19'),
    (1, '2020-11-20'),
    (1, '2020-11-21'),
    (2, '2020-11-19'),
    (2, '2020-11-20'),
    (2, '2020-11-21')
)
        
string = """
    SELECT * FROM my_schema.my_table
    WHERE my_schema.my_table.song_id = %s
    AND my_schema.my_table.date = %s;
"""
        
execute_values(cursor, string, data)
results = cursor.fetchall()

Hopefully this illustrates what I’m trying to achieve here…

I want to perform a series of select statements which each have a pair of parameters. If that pair of parameters is in the database, then append it to the results table.

Is the only way to do this, manually in a for-loop?

Answer

Executing many queries in a loop is not a good idea. Use a common table expression to deliver many pairs of parameters to a single query and get results for all of them, like in this Postgres example.

Python code:

data = (
    (1, '2020-11-19'),
    (1, '2020-11-20'),
    (1, '2020-11-21'),
    (2, '2020-11-19'),
    (2, '2020-11-20'),
    (2, '2020-11-21')
)
        
query = """
    with data(song_id, date) as (
        values %s
    )
    select t.*
    from my_table t
    join data d 
    on t.song_id = d.song_id and t.date = d.date::date
"""
execute_values(cursor, query, data)
results = cursor.fetchall()