Fetch order details using api with python script and generate id

Blogs

Staying Up-to-Date with SQL Server: Deprecated and Discontinued Features
August 8, 2024
Python script to check null values for column in file and validate in database
August 15, 2024

Fetch order details using api with python script and generate id

Demostrating a python script for fetching line level details for each order id which has payment mode of either prepaid/postpaid
or both and generating a client_ref_id value with which we are validation in database

The Python script performs the following main operations:

1) Database Queries and Data Processing (abcd_con function):

Reads a CSV file and creates a list of values from it.
Connects to two MySQL databases (abcd and abcd_old).
Executes SQL queries on both databases to fetch data based on the list of values from the CSV.
Merges the results from both databases into a single Pandas DataFrame.
Saves this DataFrame to a new CSV file (ords.csv).

2) API Calls and Further Data Processing (check_mode function):

Reads the CSV file created in the previous step.
Makes API calls to a specified endpoint (https://ord_details/) using the IDs from the CSV.
Extracts and processes payment information (COD and ONLINE amounts) from the API responses.
Categorizes each ID based on the payment mode (e.g., prepaid, postpaid, or both).
Merges this information back into the original DataFrame.
Generates a client_ref_id based on specific rules and saves the updated DataFrame to a new CSV file (new_df.csv).
Detailed Breakdown:

3) Imports and Initial Setup:

The script imports necessary libraries (pandas, pymysql, json, requests) and sets a directory path where files will be read from and saved.

3) Reading CSV File:

A file (filename.csv) is opened and each line is stripped of whitespace and added to a list (l).

4) abcd_con Function:

Database Connections: Establishes connections to two MySQL databases (abcd and abcd_old).
SQL Queries: Executes an SQL query on both databases to fetch data where col2 matches any of the values in the list l.
DataFrame Creation: Converts the query results into Pandas DataFrames and appends the results from the second database to the DataFrame from the first database.
CSV Export: Saves the combined DataFrame to a CSV file (ords.csv).

5) check_mode Function:

API Calls: Reads the CSV file created in the previous step, extracts IDs, and makes GET requests to an API endpoint for each ID.
Error Handling: Handles cases where the API call fails or returns incomplete data by logging the problematic IDs.
Data Processing: Parses the API response to extract payment details (COD and ONLINE amounts) and adds this information to a new DataFrame.
Payment Mode Determination: Adds a new column to indicate the payment mode based on the extracted amounts.
Client Reference ID Generation: Generates a client_ref_id based on the payment mode and other columns from the DataFrame.
CSV Export: Saves the final DataFrame with the generated client_ref_id to a CSV file (new_df.csv).

6) The script’s main execution block runs the abcd_con and check_mode functions in sequence when the script is executed.

Below is the python script

import pandas as pd
import pymysql as p
import json
import requests as r

dir=’/location/’

l=[]

f=open(dir+’filename.csv’,’r’)

for i in f.readlines():
l.append(str(i.strip()))

def abcd_con():

db_con=p.connect(user = “abcd”, passwd = “abcd”, host = “127.0.0.1”, port = 1234, database = “abcd”)

old_db_con=p.connect(user = “abcd1”, passwd = “abcd1”, host = “127.0.0.1”, port = 1234, database = “abcd_old”)

cur=db_con.cursor()

curs=old_db_con.cursor()

print(“checking in abcd……”)

q=f”select col1,col2,col3,col4 from tab1 ol join tab2 oa on
ol.col1=oa.col2 where oa.key=’pqrs’ and col2 in {tuple(l)}”

#print(q)

cur.execute(q)

df=pd.DataFrame(cur.fetchall(),columns=[i[0] for i in cur.description])

print(‘checking in oms old….’)

q1=f”select col1,col2,col3,col4 from tab1 ol join tab2 oa on
ol.col1=oa.col2 where oa.key=’pqrs’ and col2 in {tuple(l)}”

#print(q)

curs.execute(q1)

df1=pd.DataFrame(curs.fetchall(),columns=[i[0] for i in cur.description])

df=df.append(df1,ignore_index=True)

df.to_csv(dir+’ords.csv’,index=False)

print(“Fetched the ids with line ids ,pps id “)

def check_mode():

l=[]

print(“Checking with settlement util api……”)

cred = {‘Host’: ‘abcd.com’,
‘authorization’: ‘pqwr’, ‘content-type’: ‘application/json’,
‘accept’: ‘application/json’}
url = “https://ord_details/”

df=pd.read_csv(dir+’ords.csv’)

ids=df[‘id’].to_list()

d={‘id’:[],’cod’:[],’on’:[]}

for i in ids:

res=r.get(url+str(i),headers=cred)

#print(res)

a=res.json()

try :

for j in a[‘data’]:

d[‘cod’].append(j[‘paymentMethodEntries’][‘COD’][‘amount’])
d[‘on’].append(j[‘paymentMethodEntries’][‘ONLINE’][‘amount’])
d[‘id’].append(i)

except:

l.append(i)
pass

if len(l) > 0:

print(“ids not able to fetch…”)
for i in l:
print(i)

df1=pd.DataFrame(d)

print(“The ids with payment mode are checked “)
#print(df1.head())
#df1.to_csv(dir+’break_up.csv’,index=False)

df1[‘mode’]=df1.apply(lambda a: ‘both’ if a[‘on’] > 0 and a[‘cod’] > 0 else ‘prepaid’ if a[‘on’] > 0 else ‘postpaid’,axis=1)

new_df=df.merge(df1,on=’id’,how=’inner’)

print(“Generating client_ref_id for the ids….”)

def merge_val(r):

if r[‘mode’] == ‘both’:

b=’prepaid’+’_mp_packed_’+str(r[‘col1’])+’_’+r[‘value’]+’_’+str(r[‘col3′])+’,’+
‘postpaid’+’_mp_packed_’+str(r[‘col1’])+’_’+r[‘value’]+’_’+str(r[‘col3’])

else:

b=’payment_sale_payable_’+r[‘mode’]+’_mp_packed_’+str(r[‘col1’])+’_’+r[‘value’]+’_’+str(r[‘col3’])

return b

new_df[‘client_ref_id’]=new_df.apply(merge_val,axis=1)
new_df.to_csv(dir+’new_df.csv’,index=False)

print(“The ids saved in new_df.csv file “)

 

if __name__ == ‘__main__’:

abcd_con()
check_mode()


Pooja K

Leave a Reply

Your email address will not be published. Required fields are marked *