I am new to python. I have tried the below code to find missing column by comparing the table. Currently, i have processed all the files together and find the missing column. I am expecting to find out the column missing along with the filename in the output of the email.
#!/usr/local/bin/python3
# coding=utf-8
from re import search
import pandas
import psycopg2
import smtplib
import os
import re
import sys
#******************************************************************
# Connect to Postgres
#******************************************************************
def connect_to_postgres(env):
envfile = open ("/pgenv", 'r')
for line in envfile:
line = line.rstrip('\n')
line = line.rstrip('\r')
elem = line.split(':')
if elem[0] == env:
host, port, db, user = elem[1:]
envfile.close()
conn_str = (
"dbname=" + db + " "
"user=" + user + " "
"host=" + host
)
conn = psycopg2.connect(conn_str)
return conn
#******************************************************************
# Input Parameter
#******************************************************************
progname, ctry, clt, list, type, date = sys.argv
env='prd'
conn = connect_to_postgres(env)
try:
cursor = conn.cursor()
postgreSQL_select_Query = "select UPPER(name) from ddl_table where ctry= %s and clt= %s "
cursor.execute(postgreSQL_select_Query, (ctry, clt))
mobile_records = cursor.fetchall()
postgreSQL_select_Query2 = "select distinct(CONCAT('DDL_',a.ctry,'_',a.clt,'_',a.list_code,'_LLL_P00',_num,'_',file_code,'_',%s,'_',%s,'.csv')) as INPUT_FILE from ddd_data a JOIN ddd_no b ON a.list_code = b.list_code and a.clt = b.clt and a.ctry = b.ctry where a.ctry= %s and a.clt= %s and a.list_code= %s "
cursor.execute(postgreSQL_select_Query2, (type, date, ctry, clt, list))
mobile_records2 = cursor.fetchall()
finally:
# closing database connection.
if conn:
cursor.close()
conn.close()
with open('listable.txt', 'w') as mfile:
for line in mobile_records:
mfile.write("%s\n" % line)
mfile.close()
with open('inputfile.txt', 'w') as nfile:
for line in mobile_records2:
nfile.write("%s\n" % line)
nfile.close()
#writing the column name
seen = set()
with open('inputfile.txt') as filenames, open('listfile.txt', 'w') as mfile:
for filename in filenames:
csvFile = pandas.read_csv(filename.strip(), sep="|", nrows=1)
# displaying the contents of the CSV file
for col in csvFile.columns:
COL= col.upper()
if not search("KEY", COL):
mfile.write(col.upper() + "\n")
mfile.close()
with open('listfile.txt', 'r') as file1:
# Read the lines from the first text file
lines5 = file1.readlines()
file1.close()
# Convert the lines from the first text file to a set
lines5_set = set(lines5)
# Open the second text file in read mode
with open('listable.txt', 'r') as file2:
# Read the lines from the second text file
lines10 = file2.readlines()
file2.close()
# Convert the lines from the second text file to a set
lines10_set = set(lines10)
# Find the uniq lines between the two text files
unique = lines5_set.difference(lines10_set)
# Iterate over the unique lines and print them
with open('output.txt', 'w') as sfile:
for line6 in unique:
sfile.write("%s" % line6)
sfile.close()
#send email to user
SERVER1 = "fp@mail.com"
TO=["test@mail.com"]
CC=["test@mail.com"]
FROM="test@mail.com"
SUBJECT = "extra field"
TEXT=""
TEXT0=""
email_file='/output.txt'
fp = open(email_file,"r")
TEXT_LIST = fp.readlines()
fp.close()
email_file1='inputfile.txt'
fp1 = open(email_file1,"r")
TEXT_LIST1 = fp1.readlines()
fp1.close()
for line in TEXT_LIST1:
TEXT0=TEXT0+"\n"+line
for line in TEXT_LIST:
TEXT=TEXT+"\n"+line
footer_msg=" "
header0= """
Processed Files :
"""
header= """
Below list need to be added:
"""
footer = """
Note: This is an automated mail
"""
TEXT = header0 + TEXT0 + header + TEXT + footer + footer_msg
# Prepare actual message
message = """\
From: %s
To: %s
Cc: %s
Subject: %s
%s
""" % (FROM, ", ".join(TO),", ".join(CC), SUBJECT, TEXT)
print(message)
# Send the mail
'''
server = smtplib.SMTP(SERVER1)
server.sendmail(FROM, TO, message)
server.set_debuglevel(2)
server.quit()
'''
try:
smtpObj = smtplib.SMTP(SERVER1)
smtpObj.sendmail(FROM, TO, message)
smtpObj.set_debuglevel(2)
print("Successfully sent email")
except:
print("Error: unable to send email")