CODEWITHSUNDEEP
sqlpandas
scott martin
scott martin

Reputation: 1293

Pandas - Looping through multiple items in a Dataframe to a SQL query

I have a Dataframe that has two columns, one that mentions the product_name and other that has a key related to that product as shown below:

product,key
prod_a, key_1
prod_b, key_2
prod_c, key_3

I am trying to query a table such that I apply both the product and its corresponding key in where clause as shown below:

query:

cursor.execute("""select prod_name, quantity from table where prod_name = {product} and prod_key = {key}""")

product and key values are taken from the above Dataframe

I would like to loop through each product and key from the above DataFrame into the SQL query ad get the output

Upvotes: 1

Views: 730

Answers (2)

zealous
zealous

Reputation: 7503

itertuples() is faster way to do it.

for index, row in df.itertuples():
    cursor.execute("""select prod_name, quantity from table where prod_name = '{row["product"]}' and prod_key = '{row["key"]}'""")

.itertuples() yields a namedtuple for each row, with the row’s index value as the first element of the tuple.

.iterrows() yields pairs (tuples) of (index, Series) for each row in the DataFrame.

You can read more about it here.

Upvotes: 1

scott martin
scott martin

Reputation: 1293

Figured using the below:

for index, row in df.iterrows():
    print(index, row["product"],row["key"])

    cursor.execute(f"""select prod_name, quantity from table where prod_name = '{row["product"]}' and prod_key = '{row["key"]}'""")

Upvotes: 1

Related Questions