1

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

scott martin
  • 1,253
  • 1
  • 14
  • 36
  • y dont u pass the products and keys to ```cursor.execute``` as a list and use ```where prod_name IN [list_items]``` ? – sammywemmy Apr 16 '20 at 06:35

2 Answers2

1

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.

zealous
  • 7,336
  • 4
  • 16
  • 36
1

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"]}'""")
scott martin
  • 1,253
  • 1
  • 14
  • 36