EqZbnke3

1import pyodbc
2import pandas as pd
3import smtplib, ssl
4
5from send_email import alert_msg_for_txn_amt
6
7
8#Initialize server connections
9server = 'uat-sql-db-002.database.windows.net'
10database = 'UAT-DWH'
11username = '********'
12password = '********'
13driver= '{ODBC Driver 17 for SQL Server}' 
14
15#Initialize error settings
16ta_min = 100
17ta_max = 900000
18
19#Email Settings
20user_email = '********@gmail.com'
21user_pw = '********'
22receiver_email = '********@ntu.edu.com.sg'
23
24ta_range = list(range(ta_min,ta_max+1))
25
26#Connect to server
27cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
28
29#SQL query data from table
30sql = "select * from ew_daily_prepaid_txn"
31
32#Import data as a dataframe
33df = pd.read_sql(sql,cnxn)
34
35#export dataframe as a CSV
36#df.to_csv('df.csv', sep=',')
37
38#View total number of transactions
39print ('Total number of transactions for the day:', df['id'].count())
40
41#check if monetary amount in each transaction is in range 
42if df['txn_amount'].tolist() in ta_range:
43    pass
44
45else:
46    #Print error to command line
47    print ('nALERT: There are transactions outside of acceptable rangen')
48    df2 = pd.DataFrame()
49    df2 = df[(df['txn_amount'].isin(ta_range) == False) & df['txn_amount'] != 0]
50    txn_oor_num = df2['id'].count()
51    print ('Total number of transactions that are out of range: ', txn_oor_num)
52
53    #Send alert email
54    content = alert_msg_for_txn_amt(database, txn_oor_num)
55    mail = smtplib.SMTP("smtp.gmail.com", 587)
56    mail.starttls(context = ssl.create_default_context())
57    mail.login(user_email, user_pw)
58    mail.sendmail(user_email, receiver_email, content)
59    mail.close()
60
61
62#Close the connection   
63cnxn.close()
64	
65Traceback (most recent call last):
66  File "sqltest.py", line 55, in <module>
67    mail = smtplib.SMTP("smtp.gmail.com", 587)
68  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
69ine 251, in __init__
70    (code, msg) = self.connect(host, port)
71  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
72ine 336, in connect
73    self.sock = self._get_socket(host, port, self.timeout)
74  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsmtplib.py", l
75ine 307, in _get_socket
76    self.source_address)
77  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsocket.py", li
78ne 727, in create_connection
79    raise err
80  File "C:UsersruvenAppDataLocalProgramsPythonPython37libsocket.py", li
81ne 716, in create_connection
82    sock.connect(sa)
83ConnectionRefusedError: [WinError 10061] No connection could be made because the
84 target machine actively refused it