How to apply map of parameters to read_sql from loop in python

Question

I am trying to use a map to pass in values into a read_sql statement. Here's what I've tried:

inventory = {
    'fruit': ['apple', 'orange'],
    'veggies': ['onion', 'cucumber'],
    }

for type, items in inventory.items():
    with pyodbc.connect('DSN=DB_CONN') as conn:
        df_t_minus_1 = pd.read_sql("SELECT * FROM temp_table where type1 = ? and item = ? and item = ?", conn, params=[type, description])

Basically, I am trying to get a query to select fruit as type1 then item as apple and orange (in the first iteration as an example).

However, I keep getting an error saying it expects 3 parameters but I am passing 2. I am assuming this is because it only consumes 1 item from the list. I'd like to figure out how to pass the list to the second two ? in my sql statement. Thank you for your help!

score 1 · Accepted Answer · answered Mar 03 '21 at 19:25

why don't just format before call read_sql and allow for multiple items in an in_select query:

inventory = {
    "fruit": ["apple", "orange"],
    "veggies": ["onion", "cucumber"],
}

sql_str = (
    "SELECT * FROM temp_table where type1 = '{category}' "
    "and item in ({items})"
)

for category, items in inventory.items():
    in_select = "', '".join([item for item in items])
    in_select = f"'{in_select}'"
    sql = sql_str.format(category=category, items=in_select)

    with pyodbc.connect("DSN=DB_CONN") as conn:
        df_t_minus_1 = pd.read_sql(sql, conn)

score 0 · Answer 2 · answered Mar 03 '21 at 19:11

Well your SQL string has three question marks but you only pass in the type and the single list.

So what you need to do is access the individual items of the list and pass those in as parameters,

params = [type, description[0], description[1]]

but note that this assumes that there's two items (or more) in the list, and of course if your list had more than two items, then the extra items would just be ignored.

Also the SQL statement seems weird. It would select a record only if the item is BOTH apple AND orange AT THE SAME TIME. That's obviously impossible.

How to apply map of parameters to read_sql from loop in python

2 Answers2