Problem
I am new to python. I have been given a task to verify the data from 2 apis which give similar data but in different format i.e. Json and XML. I have written the code but it is taking too much time to execute. I am unfamiliar with formatting and etc so please help me here. (I am excluding urls and header etc. for security purpose)
from openpyxl import load_workbook
from xml.etree import ElementTree
import json
import urllib.request
import requests
wb = load_workbook("CNIC.xlsx")
source = wb["Sheet1"]
for cniclist in source['A']:
cnic = cniclist.value
url = "/%s" % cnic
urlobj = urllib.request.urlopen(url)
try:
string = urlobj.read().decode('utf-8')
json_obj = json.loads(string)
except:
print(urlobj.readlines())
url2 = "url2"
payload = "<CUSTNO>{}</CUSTNO>"
headers = {}
response = requests.request("POST", url2, data=payload.format(cnic), headers=headers)
tree = ElementTree.ElementTree(ElementTree.fromstring(response.text))
json_message = (json_obj.get('Message'))
if json_message == "Success":
json_record = (json_obj.get('Records'))
if json_record == 1:
for item in json_obj.get('OutData'):
json_acc = item.get('Accounts')['NoAccounts']
print("No Of Accounts in Json Service is ", json_acc)
elif json_record > 1:
json_acc = 0
for item in json_obj.get('OutData'):
json_acc = item.get('Accounts')['NoAccounts']
print("No Of Accounts in Json Service is ", json_acc)
else:
print("No JSON Records found")
root = tree.getroot()
count = 0
xml_acc_no = []
for FCDB_BODY in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCNO"):
temp = FCDB_BODY.text
xml_acc_no.append(temp)
count += 1
print("No Of Accounts in XML Service is ", str(count))
# Checking Number of Accounts in both services
if str(json_acc) == str(count):
print("Number of Accounts are same in both services")
elif str(json_acc) > str(count):
print("Json Service has more accounts ")
else:
print("XML Service has more accounts ")
# Getting all data from Json Service
for item1 in json_obj.get('OutData'):
json_file_account = item1.get('Accounts')[
'AccountList'] # Getting Accountlist details from jason service file
json_accounts_list = []
for i in json_file_account:
json_acc_no = i['ACC#'] # Getting Account Number from Json service file
json_accounts_list.append(json_acc_no)
json_title_list = []
for i in json_file_account:
json_acc_title = i['TITLE'] # Getting Account title from Json service file
json_title_list.append(json_acc_title)
json_type_list = []
for i in json_file_account:
json_account_type = i['STYPE'] # Getting Account type from Json service file
json_type_list.append(json_account_type)
json_desc_list = []
for i in json_file_account:
json_account_desc = i['STPDESC'] # Getting Account description from Json service file
if json_account_desc is not "":
json_desc_list.append(json_account_desc)
else:
pass
json_bal_list = []
for i in json_file_account:
json_account_bal1 = i['ABAL'] # Getting Account balance from Json service file
json_account_bal = int(json_account_bal1) / 100
json_account_bal = ('%f' % json_account_bal).rstrip('0').rstrip('.')
json_bal_list.append(str(json_account_bal))
# Getting all data from Json Service
xml_title_list = []
for j in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCOUNTTITLE"):
xml_acc_title = j.text
xml_title_list.append(xml_acc_title)
xml_type_list = []
for k in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCOUNTTYPEDETAIL"):
xml_acc_type = k.text
xml_type_list.append(xml_acc_type)
xml_desc_list = []
for l in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCPRDDESC"):
xml_acc_desc = l.text
xml_desc_list.append(xml_acc_desc)
xml_bal_list = []
for m in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/BAL_AVAIL"):
xml_acc_bal = float(m.text)
xml_acc_bal = ('%f' % xml_acc_bal).rstrip('0').rstrip('.')
xml_bal_list.append(xml_acc_bal)
# Checks for Account Number,Title, Type, Description and Balance
acc_check = [[x for x in xml_acc_no if x not in json_accounts_list],
[x for x in json_accounts_list if x not in xml_acc_no]]
if len(acc_check[0]) or len(acc_check[1]):
if len(acc_check[0]):
print("Accounts which are unmatched in XML are ", acc_check[0])
if len(acc_check[1]):
print("Accounts which are unmatched in JSON are ", acc_check[1])
else:
print("No Unmatched Accounts found")
title_check = [[y for y in xml_title_list if y not in json_title_list],
[y for y in json_title_list if y not in xml_title_list]]
if len(title_check[0]) or len(title_check[1]):
if len(title_check[0]):
print("Title which are unmatched in XML are ", title_check[0])
if len(title_check[1]):
print("Title which are unmatched in JSON are ", title_check[1])
else:
print("No Unmatched Title found")
type_check = [[z for z in xml_type_list if z not in json_type_list],
[z for z in json_type_list if z not in xml_type_list]]
if len(type_check[0]) or len(type_check[1]):
if len(type_check[0]):
print("Type which are unmatched in XML are ", type_check[0])
if len(type_check[1]):
print("Type which are unmatched in JSON are ", type_check[1])
else:
print("No Unmatched type found")
desc_check = [[q for q in xml_desc_list if q not in json_desc_list],
[q for q in json_desc_list if q not in xml_desc_list]]
if len(desc_check[0]) or len(title_check[1]):
if len(desc_check[0]):
print("Description which are unmatched in XML are ", desc_check[0])
if len(desc_check[1]):
print("Description which are unmatched in JSON are ", desc_check[1])
else:
print("No Unmatched Description found")
balance_check = [[w for w in xml_bal_list if w not in json_bal_list],
[w for w in json_bal_list if w not in xml_bal_list]]
if len(balance_check[0]) or len(balance_check[1]):
if len(balance_check[0]):
print("Balance which are unmatched in XML are ", balance_check[0])
if len(balance_check[1]):
print("Balance which are unmatched in JSON are ", balance_check[1])
else:
print("No Unmatched Balance found")
print("-----------------------------------")
else:
print("CNIC NOT FOUND in Json Service ")
print("-----------------------------------")
Solution
List Comprehension
It doesn’t look like you’re new to list comprehension. So why do you use it some places but not others? When you get the Account Number/Title/Type from the json service file, you use standard loops. These loops can be reduced to three lines:
json_accounts_list = [i['ACC#'] for i in json_file_account]
json_title_list = [i['TITLE'] for i in json_file_account]
json_type_list = [i['STYPE'] for i in json_file_account]
This reduction can also be applied to when you get all the data from the json service:
xml_title_list = [j.text for j in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCOUNTTITLE")]
xml_type_list = [k.text for k in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCOUNTTYPEDETAIL")]
xml_desc_list = [l.text for l in root.findall("./FCDB_BODY/CUSTACCOUNT/ACCOUNT/ACCPRDDESC")]
String Formatting
This one is personal preference, but can provide some cleanliness to your code:
('%f' % json_account_bal).rstrip('0').rstrip('.')
can be
f"{json_account_bal}".rstrip('0').rstrip('.')
This removes the use of parentheses and looks just a bit neater. This can be applied to other parts of your program as well.
Functions
Your entire program is enveloped in a for loop. I would recommend splitting parts of your code into functions so you can separate and manage what parts do what.
Printing
Just a little aside about printing. When you print like print("Hi ", name), there are two spaces between “Hi” and whatever the name is. The print function inserts a space whenever you’re printing like this, so a little neater output would be print("Hi", name).