Loop through a spreadsheet-CodePudding

I made a Python program using tkinter and pandas to select rows and send them by email.

The program let the user decides on which excel file wants to operate;

then asks on which sheet of that file you want to operate;
then it asks how many rows you want to select (using .tail function);
then the program is supposed to iterate through rows and read from a cell (within selected rows) the email address;
then it sends the correct row to the correct address.

I'm stuck at the iteration process.

Here's the code:

import pandas as pd
import smtplib

def invio_mail(my_tailed__df): #the function imports the sliced (.tail) dataframe
    gmail_user = '###'
    gmail_password = '###'
    sent_from = gmail_user
    server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
    server.ehlo()
    server.login(gmail_user, gmail_password)

    list = my_tailed_df

    customers = list['CUSTOMER']
    wrs = list['WR']
    phones = list['PHONE']
    streets = list['STREET']
    cities = list["CITY"]
    mobiles = list['MOBILE']
    mobiles2 = list['MOBILE2']
    mails = list['INST']

    for i in range(len(mails)):
        customer = customers[i]
        wr = wrs[i]
        phone = phones[i]
        street = streets[i]
        city = cities[i]
        mobile = mobiles[i]
        mobile2 = mobiles2[i]
"""
        for i in range(len(mails)):
            if mails[i] == "T138":
                mail = "[email protected]"
            elif mails[i] == "T139":
                mail = "[email protected]"
"""
        subject = 'My subject'
        body = f"There you go" \n Name: {customer} \n WR: {wr} \n Phone: {phone} \n Street: {street} \n City: {city} \n Mobile: {mobile} \n Mobile2: {mobile2}"


        email_text = """\
        From: %s
        To: %s
        Subject: %s
    
        %s
        """ % (sent_from, ", ".join(mail), subject, body)
        try:
            server.sendmail(sent_from, [mail], email_text)
            server.close()
            print('Your email was sent!')
        except:
            print("Some error")

The program raises a KeyError: 0 after it enters the for loop, on the first line inside the loop: customer = customers[i]

I know that the commented part (the nested for loop) will raise the same error.

I'm banging my head on the wall, I think i've read and tried everything.

Where's my error?

CodePudding user response：

Things start to go wrong here: list = my_tailed_df. In Python list() is a Built-in Type.

However, with list = my_tailed_df, you are overwriting the type. You can check this:

# before the line:
print(type(list))
<class 'type'>

list = my_tailed_df

# after the line:
print(type(list))
<class 'pandas.core.frame.DataFrame'> # assuming that your df is an actual df!

This is bad practice and adds no functional gain at the expense of confusion. E.g. customers = list['CUSTOMER'] is doing the exact same thing as would customers = my_tailed_df['CUSTOMER'], namely creating a pd.Series with the index from my_tailed_df. So, first thing to do, is to get rid of list = my_tailed_df and to change all those list[...] snippets into my_tailed_df[...].

Next, let's look at your error. for i in range(len(mails)): generates i = 0, 1, ..., len(mails)-1. Hence, what you are trying to do, is access the pd.Series at the index 0, 1 etc. If you get the error KeyError: 0, this must simply mean that the index of your original df does not contain this key in the index (e.g. it's a list of IDs or something).

If you don't need the original index (as seems to be the case), you could remedy the situation by resetting the index:

my_tailed_df.reset_index(drop=True, inplace=True)

print(my_tailed_df.index)
# will get you: RangeIndex(start=0, stop=x, step=1)
# where x = len(my_tailed_df)-1 (== len(mails)-1)

Implement the reset before the line customers = my_tailed_df['CUSTOMER'] (so, instead of list = my_tailed_df), and you should be good to go.

Alternatively, you could keep the original index and change for i in range(len(mails)): into for i in mails.index:.

Finally, you could also do for idx, element in enumerate(mails.index): if you want to keep track both of the position of the index element (idx) and its value (element).