Wednesday, April 19, 2023

How to Send MySQL Data to an Email with Python

import smtplib
import csv
import pymysql
import json
import base64
import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

# Connect to the MySQL database
db = pymysql.connect(host="localhost", user="root", password="pass1234", database="local")

# Execute an SQL query to retrieve data
cursor = db.cursor()
cursor.execute("SELECT * FROM local.questions")
data = cursor.fetchall()

# Write the data to a json file
data = [[str(col) if isinstance(col, datetime.datetime) else col for col in row] for row in data]
with open('data.json', 'w', encoding='utf-8', newline='') as file:
    json.dump(data, file, ensure_ascii=False)

# Open the JSON file
with open('data.json', 'r', encoding='utf-8') as file:
    data = json.load(file)

# Create the email message
from_addr = 'your@gmail.com'
to_addr = 'friend@gmail.com'
subject = 'JSON data'
msg = MIMEMultipart()
msg['From'] = from_addr
msg['To'] = to_addr
msg['Subject'] = subject

# Add the JSON file as an attachment
attachment = MIMEApplication(json.dumps(data, indent=2))
attachment['Content-Disposition'] = f'attachment; filename=data.json'
msg.attach(attachment)

# Connect to the SMTP server and send the message
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(from_addr, 'Your_Gmail_APP_PASSWORD')
server.sendmail(from_addr, to_addr, msg.as_string())
server.quit()

No comments:

Post a Comment