Retrieving data from a DB and send it via mail

Question

I am creating a script that takes some data from a database and then it sends it via mail. Unfortunately, I am stuck with something that seems very easy to do.

If there is only one entry from the DB it works perfectly (ex: 18/10/18 - Do math exercises) but if there are multiple entries they don't show up, I thought about using a while loop but as I have to keep all the entries in the same variable I can't get it working. I share the code below:

# Enviar correu Python 3.6 (NO ANOMENAR EMAIL.PY)
import smtplib, pymysql.cursors, secrets, re, datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from time import strftime

def dbb():
    connection = pymysql.connect(host='X',
                                 user='root',
                                 password='X',
                                 db='deuresc',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            # Llegim tots els recordatoris
            dataactual = strftime('%Y-%m-%d')
            data = datetime.datetime.strptime(dataactual, "%Y-%m-%d")
            datafinal = data + datetime.timedelta(days=1)
            sql = "SELECT * FROM `deures` WHERE `data`=%s"
            cursor.execute(sql, datafinal)
            resultat = cursor.fetchall()

            for row in resultat:
                data = str(row['data'])
                print(data)
                tasca = str(row['tasca'])
                print(tasca)
                # It prints all the values but it doesn't send them below.
            fromaddr = "X"
            PASSWORD = "X"

            welcome = ['Jefe', 'Gery', 'Boss', 'Gary']

            # Misstage del correu
            SUBJECT = "Recordatori deures " + data
            body = "Bon dia " + secrets.choice(welcome) + ',' +  '\n' + 'Tens les següents tasques programades per demà: ' + '\n' + data + ' ' + tasca
            toaddr = "X"

            try:
                msg = MIMEMultipart()
                msg['From'] = fromaddr
                msg['To'] = toaddr
                msg['Subject'] = SUBJECT

                msg.attach(MIMEText(body, 'plain'))

                # Detalles del servidor de correu (Gmail, SMTP, Port: 587, TLS)
                server = smtplib.SMTP('smtp.gmail.com', 587)
                server.starttls()
                server.login(fromaddr, PASSWORD)
                text = msg.as_string()
                server.sendmail(fromaddr, toaddr, text)
                server.quit()
                print("S'ha enviat el recordatori a '" + toaddr + "' satisfactoriament!")
            except:
                print("S'ha produït un error!")
    finally:
        connection.close()

Thanks in advance for your help,

Answer 1

You are looping over the results and assigning the values data and tasca inside the loop. When the loop is done, those two variables will only contain the last one which was printed of each respective value.

Examine:

>>> for value in range(3):
...    print(value)
...
0
1
2
>>> print(value)
2

If you want to print all values, you have to store them somewhere.

You probably want something like

datas = []  # list to remember the data values
tascas = []  # list to remember the tasca values
for row in resultat:
    data = str(row['data'])
    print(data)
    datas.append(data)  # store this data in the list
    tasca = str(row['tasca'])
    print(tasca)
    tascas.append(tasca)  # store this tasca in the list

... and then later on in the code

body = "Bon dia " + secrets.choice(welcome) + ',' +  '\n' + \
     'Tens les següents tasques programades per demà: ' + '\n' + \
     '\n'.join(datas) + ' ' + '\n'.join(tascas)

If you expect the values to be printed next to each other in the email message, the collecting loop should probably instead look something like

data_tasca = []  # list to remember data/tasca pairs
for row in resultat:
    ...
    data_tasca.append(' '.join([data, tasca]))

and then in the body assignment collect the rows from data_tasca similarly to how the collected values from datas and tascas are collected above.

As an aside, you should probably move the code which doesn't use cursor to be outside the with clause, i.e. outdent the code after cursor.fetchall().