114 lines
3.4 KiB
Python
114 lines
3.4 KiB
Python
import mysql.connector
|
|
import csv
|
|
import smtplib
|
|
from email.mime.text import MIMEText
|
|
from email.mime.multipart import MIMEMultipart
|
|
from email.mime.base import MIMEBase
|
|
from email import encoders
|
|
import os
|
|
|
|
|
|
def connect_db():
|
|
try:
|
|
conn = mysql.connector.connect(
|
|
host='192.168.145.196',
|
|
user='python_sqlcsv_nosecure',
|
|
password='HBMqyUVM',
|
|
database='smsgw_control',
|
|
auth_plugin='mysql_native_password',
|
|
ssl_disabled=True
|
|
)
|
|
return conn
|
|
except mysql.connector.Error as err:
|
|
print(f"Error connecting to DB: {err}")
|
|
return None
|
|
|
|
|
|
def run_query(conn, query):
|
|
cursor = conn.cursor()
|
|
cursor.execute(query)
|
|
rows = cursor.fetchall()
|
|
columns = [desc[0] for desc in cursor.description]
|
|
return rows, columns
|
|
|
|
|
|
def write_csv(filename, rows, columns):
|
|
with open(filename, mode='w', newline='') as file:
|
|
writer = csv.writer(file)
|
|
writer.writerow(columns)
|
|
writer.writerows(rows)
|
|
|
|
|
|
def send_email(sender_email, receiver_email, subject, body, filename, smtp_server, smtp_port, smtp_user, smtp_password):
|
|
message = MIMEMultipart()
|
|
message['From'] = sender_email
|
|
message['To'] = receiver_email
|
|
message['Subject'] = subject
|
|
|
|
message.attach(MIMEText(body, 'plain'))
|
|
|
|
with open(filename, "rb") as attachment:
|
|
part = MIMEBase('application', 'octet-stream')
|
|
part.set_payload(attachment.read())
|
|
encoders.encode_base64(part)
|
|
part.add_header('Content-Disposition', f"attachment; filename= {os.path.basename(filename)}")
|
|
message.attach(part)
|
|
|
|
try:
|
|
server = smtplib.SMTP(smtp_server, smtp_port)
|
|
server.starttls()
|
|
server.login(smtp_user, smtp_password)
|
|
text = message.as_string()
|
|
server.sendmail(sender_email, receiver_email, text)
|
|
server.quit()
|
|
print('Email sent')
|
|
except Exception as e:
|
|
print(f'Error sending email')
|
|
|
|
if __name__ == "__main__":
|
|
# MySQL Connection and Query Info
|
|
conn = connect_db()
|
|
if conn:
|
|
query = """SELECT
|
|
mcc, mnc, route, price, currency
|
|
FROM
|
|
smsgw_control.routingprices
|
|
WHERE
|
|
clientid = '13271' AND route = 'M1'
|
|
ORDER BY 1 ASC;"""
|
|
|
|
rows, columns = run_query(conn, query)
|
|
conn.close()
|
|
|
|
# Write to CSV
|
|
csv_filename = "SixFiveNetworksM1.csv"
|
|
write_csv(csv_filename, rows, columns)
|
|
|
|
# Email Information
|
|
sender_email = "donotreply@mexedia.mt"
|
|
receiver_email = "mark@42tele.com"
|
|
subject = "MEXEDIA PRICE CHANGES for SixFiveNetworks - M1 - SixFiveNe_DIR"
|
|
body = """
|
|
Dear Valued Customer,
|
|
|
|
We would like to inform you of the following price changes on your account.
|
|
|
|
Please note that the changes are effective immediately, any messages sent from now on will be charged based on the new price.
|
|
|
|
If you require any further assistance or clarification, please do not hesitate to contact your Account Manager or our Support Team on support.sms@mexedia.com and we'll gladly assist you further.
|
|
|
|
Best Regards,
|
|
Mexedia Team"""
|
|
|
|
smtp_server = "smtp.eu.mailgun.org"
|
|
smtp_port = 587
|
|
smtp_user = 'stps@monitoring.fortytwo.mt'
|
|
smtp_password = 'f18c48a7076c868bef91eb8f0ca59d33-9776af14-d2648a16'
|
|
|
|
# Send email with CSV attachment
|
|
send_email(
|
|
sender_email, receiver_email, subject, body, csv_filename,
|
|
smtp_server, smtp_port, smtp_user, smtp_password
|
|
)
|
|
|