Refreshing dataframe in Python inside a While loop

Question

I have a data frame that fetches data from SQL and sends out an email based on the condition of a column. But these columns are not present in SQL, I need to create them after creating the data frame. The issue here is I have a while loop where it checks for the condition of the column every 10 sec. I noticed that the while loop works perfectly with all the conditions but the data frame is not refreshed from SQL since it is outside the while loop. If I put the data frame inside the while loop, the last_email_sent is initialized as None is affected and gives the wrong output. Below is my pseudo code where I have the logic described.

#initialisation and fetching of the table from SQL
df = pd.read_sql('SELECT * FROM stations', cnxn)
df['Timeflag'] = now - df['last_reported']
df['last_email_sent'] = None

while True:
   for index in df.index:
       if(df['Timeflag'] == 1 and df.loc[df.index[index], "Last_email_sent"] is None:
            print('pass')
            minutes = divmod((now - df.loc[index, "Last_email_sent"]).total_seconds(), 60)[0]

       elif df.loc[index, 'Time flag'] == 1 and minutes < min:
            print('fail')
            minutes = divmod((now - df.loc[index, "Last_email_sent"]).total_seconds(), 60)[0]
               else:
        print('false')
time.sleep(10)

The issue is that I cannot do something like below as inside the for loop the last_email_sent cannot be None and has to retain the last updated value that was prevalent after 1st iteration of while loop.

while True:
    #initialisation and fetching of the table from SQL
    df = pd.read_sql('SELECT * FROM stations', cnxn)
    df['Timeflag'] = now - df['last_reported']
    df['last_email_sent'] = None

Is there any other method to call the data frame inside for loop and thereby calculating other columns simultaneously?

score 1 · Accepted Answer · answered Oct 14 '19 at 10:51

1

If I undrestood your question the right way, you can do the following, but note that it is not ready to use code, I just want to demonstrate logic

First_Start = True  # first time we define db colum to None
Last_Email_Sent = None  # when we sent the last email

while True:

    # read data from db heare and do what you need
    df = pd.read_sql('SELECT * FROM stations', cnxn)
    df['Timeflag'] = now - df['last_reported']
    if First_Start:
        df['last_email_sent'] = None
        First_Start = False  # never again will be True
    else:
        df['last_email_sent'] = Last_Email_Sent

    while True:
       for index in df.index:  # cheack all you want in df
           if(df['Timeflag'] == 1 and df.loc[df.index[index], "Last_email_sent"] is None:
                print('pass')
                minutes = divmod((now - df.loc[index, "Last_email_sent"]).total_seconds(), 60)[0]

           elif df.loc[index, 'Time flag'] == 1 and minutes < min:
                print('fail')
                minutes = divmod((now - df.loc[index, "Last_email_sent"]).total_seconds(), 60)[0]
            else:
                print('false')

        Last_Email_Sent  = ??? # define new value here!
        break # all work is done and you go out of the while loop
    time.sleep(10)

    # now you can apply to db again to get a new df

Hope the answer was useful for you.

answered Oct 14 '19 at 10:51

Artiom Kozyrev

3,526
2
13
31

Thanks for the help. In the end can I assign Last_Email_Sent = df['last_email_sent'] as that is what I want. Is this the right way to assign? @Artiom Kozyrev – Krishnamurthy Oct 15 '19 at 09:32
@KrishnamurthyKA why not? Last_Email_Sent can be any value you want, you can surely compare df column with another df column. If my answer helped please upvote it and if issue is solved mark anser as solution. – Artiom Kozyrev Oct 15 '19 at 09:38
How can you assign a column to a variable? The First_start should be false at the end of the while loop, right? Because it will not trigger the else part the 2nd time. I am testing my code still. @Artiom Kozyrev – Krishnamurthy Oct 15 '19 at 09:50
@KrishnamurthyKA why can't you? column of df is also a variable you can assign it. First start is True in 1st run, in all other cases in is False and it will surely trigger else. The part is correct if First_Start: df['last_email_sent'] = None First_Start = False # never again will be True else: df['last_email_sent'] = Last_Email_Sent – Artiom Kozyrev Oct 15 '19 at 09:57
@KrishnamurthyKA hello how is retest? – Artiom Kozyrev Nov 15 '19 at 09:11

Refreshing dataframe in Python inside a While loop

1 Answers1