Pass a List of Parameters as an Input to a SQL Query

Question

I have a SQL query that I want want to execute for a list of input parameters.

SELECT Field1, Field2
FROM Table
WHERE Field3 = ?
AND Field4 = ?

I have ~10,000 pairs of values that I want to run this query for. At the moment I'm iterating over the list, and appending each result to a data frame. I feel like there is probably a more Pythonic way to do this. I just don't know what it is.

Is there a cleaner way to do this?

• Phillip: So you have List(x,y) with n records and for all n records you want to run a query, something like: If (field3 = x1 and field4 =y1) or (field3 = x2 and field y2)... etc? How is this list stored? Is it just a list in Excel, or is it something you can put into a table as mentioned in one of the answers?
• Batman: It's currently a list of tuples, which are are the output of another Python function.
• Phillip: Can you create a table or temp table for the output? If not, I have a few work-arounds that might might work for you. EDIT: I should also ask how you are running your queries. In something like PL/SQL Developer, or something else?

Answer 1

I think you need to create a temp table that stores the 10,000 pairs of values. Then you can use an Inner Join on that temp table.

Example:

Select f1,f2 
From 
 table t 
 Inner Join temptable m 
 On m.c1 = t.f3 and m.c2(column 2) = t.f4

• Batman: That had also occurred to me, but still felt a little bit like brute force and ignorance.
• Batman: It looks like this is going to be the best solution for my problem, because (with a left join) it's also the easiest way to handle the case where there is not a record in the table that matches my input parameters.