What is the most effective way for iterate over dataframe and do sql query and then save as dataframe each row in pandas

Clegane 0
Viewed 34

i have a dataframe like this:

import pandas as pd
import sqlalchemy
con = sqlalchemy.create_engine('....')

df=pd.DataFrame({'user_id':[1,2,3],'start_date':pd.Series(['2022-05-01 00:00:00','2022-05-10 00:00:00','2022-05-20 00:00:00'],dtype='datetime64[ns]'),
                 'end_date':pd.Series(['2022-06-01 00:00:00','2022-06-10 00:00:00','2022-06-20 00:00:00'],dtype='datetime64[ns]')})

'''
user_id    start_date            end_date
1          2022-05-01 00:00:00   2022-06-01 00:00:00
2          2022-05-10 00:00:00   2022-06-10 00:00:00
3          2022-05-20 00:00:00   2022-06-20 00:00:00
'''

I want to get the sales data for each user from the database in the date ranges specified in the df. Below is a code that I am currently using and it is working correctly.

df_stats=pd.DataFrame()
for k,j in df.iterrows():
    sql='''
  select '{}' as user_id,sum(item_price) as sales,count(return) as return from sales 
  where created_at between '{}' and '{}' and user_id={}'''.format(j['user_id'],j['start_date'],j['end_date'],j['user_id'])
    sql_to_df = pd.read_sql(sql, con)
    df_stats = df_stats.append(sql_to_df)

final=df.merge(df_stats,on='user_id')

'''
final:

user_id    start_date            end_date              sales   return
1          2022-05-01 00:00:00   2022-06-01 00:00:00   1500    5
2          2022-05-10 00:00:00   2022-06-10 00:00:00   2900    9
3          2022-05-20 00:00:00   2022-06-20 00:00:00   1450    1
'''

But in the articles I read it is mentioned that using iterrows() is very slow. Is there a way to make this process more efficient ?

note: Similar to my question but i couldn't find a satisfactory answer in this previously asked question.

pandas loops
1 Answers

You can use .to_records to transform the rows to a list of tuples. Then iterate the list and unpack the tuple and pass the args to "your_sql_function"

import pandas as pd


data = {
    "user_id": [1, 2, 3],
    "start_date": pd.Series(["2022-05-01 00:00:00", "2022-05-10 00:00:00", "2022-05-20 00:00:00"], dtype="datetime64[ns]"),
    "end_date": pd.Series(["2022-06-01 00:00:00", "2022-06-10 00:00:00", "2022-06-20 00:00:00"], dtype="datetime64[ns]")
}

df = pd.DataFrame(data)

for user, start, end in df.to_records(index=False):
    your_sql_function(user, start, end)
snake_charmer_7750
Related