import pyodbc
import pandas as pd
import smtplib, ssl

from send_email import alert_msg_for_txn_amt


#Initialize server connections
server = 'uat-sql-db-002.database.windows.net'
database = 'UAT-DWH'
username = '********'
password = '********'
driver= '{ODBC Driver 17 for SQL Server}'

#Initialize error settings
ta_min = 100
ta_max = 900000

#Email Settings
user_email = '********@gmail.com'
user_pw = '********'
receiver_email = '********@ntu.edu.com.sg'

ta_range = list(range(ta_min,ta_max+1))

#Connect to server
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

#SQL query data from table
sql = "select * from ew_daily_prepaid_txn"

#Import data as a dataframe
df = pd.read_sql(sql,cnxn)

#export dataframe as a CSV
#df.to_csv('df.csv', sep=',')

#View total number of transactions
print ('Total number of transactions for the day:', df['id'].count())

#check if monetary amount in each transaction is in range
if df['txn_amount'].tolist() in ta_range:
    pass

else:
    #Print error to command line
    print ('nALERT: There are transactions outside of acceptable rangen')
    df2 = pd.DataFrame()
    df2 = df[(df['txn_amount'].isin(ta_range) == False) & df['txn_amount'] != 0]
    txn_oor_num = df2['id'].count()
    print ('Total number of transactions that are out of range: ', txn_oor_num)

    #Send alert email
    content = alert_msg_for_txn_amt(database, txn_oor_num)
    mail = smtplib.SMTP("smtp.gmail.com", 587)
    mail.starttls(context = ssl.create_default_context())
    mail.login(user_email, user_pw)
    mail.sendmail(user_email, receiver_email, content)
    mail.close()


#Close the connection
cnxn.close()

Traceback (most recent call last):
  File "sqltest.py", line 55, in <module>
    mail = smtplib.SMTP("smtp.gmail.com", 587)
  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
ine 251, in __init__
    (code, msg) = self.connect(host, port)
  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
ine 336, in connect
    self.sock = self._get_socket(host, port, self.timeout)
  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
ine 307, in _get_socket
    self.source_address)
  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsocket.py", li
ne 727, in create_connection
    raise err
  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsocket.py", li
ne 716, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [WinError 10061] No connection could be made because the
 target machine actively refused it