Dear Readers,
In this article, we will see the following Comparison between Python and Shell Script for sending email for the set of SQL commands.
Python and Shell Script for sending email for the set of SQL commands:
Shell Script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
#!/bin/bash #Script to perform testing ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2' export ORACLE_SID='prod' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexperts@gmail.com' export LOG='/tmp/test_mig.log' #Function step1 function step1 { echo " " >> $LOG echo "Started Step 1" >> $LOG echo " " >> $LOG echo "Test email " >> $LOG sqlplus vinod/vinod <<START >>$LOG prompt updating records in EMP table select * from emp where deptno=20; prompt update emp set sal=sal+500 WHERE deptno=20; update emp set sal=sal+500 WHERE deptno=20; prompt select * from emp where deptno=20; select * from emp; START echo " " >> $LOG echo "----- END OF Step1 ------" >> $LOG } #Function to trigger email function mailog { cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST} } #Script execution is here > $LOG if [ "$#" -eq 0 ] then step1 mailog else $1 mailog fi |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
updating records in EMP table SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 4800 20 7566 JONES MANAGER 7839 02-APR-81 6975 20 7788 SCOTT ANALYST 7566 19-APR-87 7000 20 7876 ADAMS CLERK 7788 23-MAY-87 5100 20 7902 FORD ANALYST 7566 03-DEC-81 7000 20 SQL> update emp set sal=sal+500 WHERE deptno=20 SQL> 5 rows updated. SQL> select * from emp where deptno=20 SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 5300 20 7566 JONES MANAGER 7839 02-APR-81 7475 20 7788 SCOTT ANALYST 7566 19-APR-87 7500 20 7876 ADAMS CLERK 7788 23-MAY-87 5600 20 7902 FORD ANALYST 7566 03-DEC-81 7500 20 |
Python Script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
import cx_Oracle import sys import os import time os.remove('/tmp/output.log') db = cx_Oracle.connect("vinod/vinod@orcl") cursor = db.cursor() log = open('/tmp/output.log','a') def select_query(): cursor.execute('select * from emp where deptno=20') for row in cursor: log.write(str(row)) log.write("\n") def step1(): line=("---->>>> updating records in EMP table\n") log.write("\n") log.write(line) select_query() line=("---->>>> update emp set sal=sal+500 WHERE deptno=20;\n") log.write("\n") log.write(line) cursor.execute('update emp set sal=sal+500 WHERE deptno=20') db.commit() select_query() def maillog(): email="To: test1@abc.com,test2@abc.com\n" Subject="Subject: This is Test Mail :) \n" A=open("/tmp/output.log","r") f=A.read() SENDMAIL = "/usr/sbin/sendmail" # sendmail location p = os.popen("%s -t -i" % SENDMAIL, "w") p.write(email) p.write(Subject) p.write("Content-Type: text/html\n") p.write("\n") # blank line separating headers from body p.write(f) sts = p.close() if sts != 0: print "Sendmail exit status", sts step1() log.close() maillog() exit() |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
---->>>> updating records in EMP table (7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 1800.0, None, 20) (7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 3975.0, None, 20) (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1987, 4, 19, 0, 0), 4000.0, None, 20) (7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1987, 5, 23, 0, 0), 2100.0, None, 20) (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 4000.0, None, 20) ---->>>> update emp set sal=sal+500 WHERE deptno=20; (7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 2300.0, None, 20) (7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 4475.0, None, 20) (7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1987, 4, 19, 0, 0), 4500.0, None, 20) (7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1987, 5, 23, 0, 0), 2600.0, None, 20) (7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 4500.0, None, 20) |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Note: Please test scripts in Non Prod before trying in Production.
Loading...
