how to send SQL query results to email in python

Question

The code below sends an email to my outlook. But, the email is empty and the column names in the query are printed in subject line and the email body is empty and I don't see the query results.

import smtplib

import pyodbc

import pandas as pd

myconn = pyodbc.connect('Driver='{SQL Server}',host='', database='',user='',password='', Trusted connection='yes')

query = """SELECT *;"""

df = pd.read_sql(query, myconn)

remail = "sendto@gmail.com"

smtpObj = smtplib.SMTP('smtp-outlook.com', 587)

smtpObj.ehlo()

smtpObj.starttls()

smtpObj.login('from@gmail.com','PASSWORD')

smtpObj.sendmail('from@gmail.com', remail, 'Subject:Query results \n' +df.to_string())

smtpObj.quit()

Possible duplicate of [Python email body is empty](https://stackoverflow.com/questions/50214990/python-email-body-is-empty) — Henry Yik, Oct 08 '19 at 07:24

Akshay Karande · Answer 1 · 2019-10-09T06:21:42.533

2

use yagmail lib. to send email

import smtplib
import yagmail
import pandas as pd

import mysql.connector


host= '*****'
user= '*****'
passwd= '******'
db= '********'

mydb = mysql.connector.connect(
     host=host,
     user=user,
     passwd=passwd,
     database=db
     )
myconn =mydb

query = """SELECT * from sa_user;"""

df = pd.read_sql(query, myconn)

receiver = "*******@gmail.com"

print(df)
yag = yagmail.SMTP("*******@gmail.com")
yag.send(
        to=receiver,
        subject="Verify your email",
        contents=df.to_string(), 
        )

print("done")

this is my inbox screenshot

edited Oct 09 '19 at 06:21

answered Oct 08 '19 at 07:51

Akshay Karande

559
4
6

Results are getting printer in terminal. But, I'm not receiving any email still. – smritisree Oct 09 '19 at 03:49
when you execute this code yagmail ask to enter password of sender email. so are you entered that password? – Akshay Karande Oct 09 '19 at 05:59
It didn't ask me anything. also, unable to import yaml – smritisree Oct 09 '19 at 06:15
yaml not required you can run without yaml. remove this import yaml. – Akshay Karande Oct 09 '19 at 06:23
but, it's not working for me. also, i'm using outlook not a gmail account. – smritisree Oct 09 '19 at 06:41
which domain are you using ? if is it gmail then please turn on "Less secure app access" from here https://myaccount.google.com/u/3/lesssecureapps?pageId=none of your sender account – Akshay Karande Oct 09 '19 at 06:47
i'm using outlook – smritisree Oct 09 '19 at 06:55
try to do with some gmail id's. it will be work. or use https://libraries.io/pypi/yagmail-outlook – Akshay Karande Oct 09 '19 at 06:57
same as your's.. just the query and email changed to check if it works. – smritisree Oct 09 '19 at 08:38
yes it works for me i can show you the latest screenshot of my inbox. – Akshay Karande Oct 09 '19 at 08:59
screen shot of my code : https://photos.app.goo.gl/AaBSE4j6nWWyaAbi6 – Akshay Karande Oct 09 '19 at 09:13
screenshot of inbox of my email :https://photos.app.goo.gl/Z2ty3zSTDoEN9PDx7 – Akshay Karande Oct 09 '19 at 09:14

how to send SQL query results to email in python

1 Answers1