1

I have a Dataframe of 100 rows. I want to loop that through in chunks of 10 and have it run through a SQL query

Given below is how the Dataframe looks like with 100 rows:

prod_id
123
124
125
126
127

I am trying to run this in a loop and pass it into the below query in chunks of 10 rows each time

query = "select * from table WHERE prod_id = {i}"
df = pd.DataFrame(list(session.execute(query)))

How can I pass the values from the Dataframe in chunks into the SQL query

Kevin Nash
  • 1,511
  • 3
  • 18
  • 37
  • You may build chunks of you target dataframe from each query execution. To batch your query by source dataframe you may check this question: [Binding list to params in Pandas read_sql_query with other params](https://stackoverflow.com/questions/36840438/binding-list-to-params-in-pandas-read-sql-query-with-other-params) – astentx Jul 13 '21 at 08:06

1 Answers1

0

there are two main things to handle

the 1st is the actual loop, I would predefine the limit and the jump so you can easily adjust them later

the 2nd thing is to stringify the list of items to add to the query you're query logic wrong, you cannot have an eqaulity with multiple items, but you need to use the in operator

limit = 100
jump = 10
lst = ''

for i in range(0, limit,jump):
    to = min(i + jump, limit)
    lst = "'" + "','".join(prod_df.loc[i:to,'prod_id']) + "'"

    query = "select * from table WHERE prod_id IN (" + lst + ")"
    df = pd.DataFrame(list(session.execute(query)))
Guy Louzon
  • 1,175
  • 9
  • 19
  • thanks for replying. I am however getting an error `TypeError: sequence item 0: expected str instance, int found`. Any idea how that can be addressed when creating the list `lst` – Kevin Nash Jul 13 '21 at 15:45
  • sorry of the delay in the reply... what line of code was the error referred to ? the lst is a string by definition (it's constructed as one). I will change the anwer where I believe it might be an issue – Guy Louzon Jul 18 '21 at 08:45