invoice items split of 100 records limit with varying offset - json dumps

invoiceItems_jsonDump_shards.py

print('iterate with index - steps of 100')

import mysql.connector
from mysql.connector import Error
import logging
import re
import time
from datetime import datetime , timedelta
import json

now = datetime.now()

fileName = now.strftime('%Y_%m_%d_%H_%M_%S')
logging.basicConfig(filename="logs\\shardingJSONS_invoiceItems_1_"+fileName+"_LOGS.log", level=logging.INFO, datefmt='%Y-%m-%d %H:%M:%S')

startTime = now
print('script started at : {}'.format(startTime.strftime('%Y-%m-%d %H:%M:%S')))
logging.info('script started at : {}\n'.format(startTime.strftime('%Y-%m-%d %H:%M:%S')))

print('creating shards of huge amount of data\n')

sizeOfShard = 100
index = 0
counter = 0
#lastIndex = 479,300
# invoice items /* 28,577 */

lastIndex = 28600 # test with leads table ~ around 96.5K records

alpha = re.compile(r"[a-z|A-Z]|[!@#$%^&*/]")


invoiceItems = 'select * from invoice_items'

#
# if query gives null response add condition 
#
#       try an outrage query
#   build condtion logic for that response
#


for i in range(index , lastIndex ,sizeOfShard):
    query = ''
    
    print(' ------------- {} -------------- '.format(counter))
    logging.info(' ------------- {} -------------- '.format(counter))

    # print('{} -> running for limit {},{}'.format(counter,i,sizeOfShard))
    print('{} ->  limit {} OFFSET {}'.format(counter,sizeOfShard , i))
    offSet = ' LIMIT {} OFFSET {} '.format(sizeOfShard,i)
    
    with mysql.connector.connect() as connectPointer:
        with connectPointer.cursor() as aCursor:
            query = invoiceItems+offSet
            print('running - ',query)
            print(' . .  fetching invoice items . . ')
            logging.info('\t1. fetching invoice items : {}'.format(query))
            aCursor.execute(query)
            result = aCursor.fetchall()
            if not result:
                print('--> no clients leads in table <--')
                logging.info('__________  no leads left to write  __________')
            else:
                invoiceItemCollList = []
                print('writing invoice intems')
                # payloads for invoice info ---------------------------------
                # Id,invoice_no,invoice_of,user_id,added_on,Email,payment_in,sale_rule,sale_amount 
                invoiceItemCollList = []
                for rows in result:
                    invItemColl = {}
                    invItemColl['local_id'] = rows[0]
                    invItemColl['Name'] = str(rows[0])
                    invItemColl['invoice_id'] = rows[1]
                    invItemColl['Data_Type'] = rows[2]
                    invItemColl['Query'] = rows[3]
#                    added_on = str(rows[11])
                    added_on = rows[11]
                    if added_on == None:
                        invItemColl['added_on'] = None
                    else:
                        invItemColl['added_on'] = str(added_on).replace(' ','T') + '+05:30'
                    invItemColl['Total_Months'] = rows[6]
                    invItemColl['Amount'] = rows[7]
                    invItemColl['Mode_Of_Delivery'] = rows[8]
                    invItemColl['status'] = rows[10]
                    invItemColl['Date_From'] = rows[4]
                    invItemColl['Date_To'] = rows[5]
                    invItemColl['user_id'] = rows[12]
                    invItemColl['Country'] = rows[16]
                    invItemColl['payment_status'] = rows[13]
                    invItemColl['data_status'] = rows[14]
                    invoiceItemCollList.append(invItemColl)
                    
                    del added_on
                invoiceItemsFile = 'invoiceItems\\invoiceItems_index_{}.json'.format(i)
                with open(invoiceItemsFile,'w',encoding='utf-8') as clientJson:
                        clientJson.write(json.dumps(
                            {"data":invoiceItemCollList},indent=4,default=str,sort_keys=True
                            ))
                
                print(f' . .. check {invoiceItemsFile}')
                logging.info(f' . .. check {invoiceItemsFile}')
    time.sleep(2)
    counter+=1

logging.info(' {0} {0} '.format(' - - - - - - - - - - - - - - - - - - - - - - - - - - - '))

now =  datetime.now()
endTime = now
print('script ended at : {}'.format(endTime.strftime('%Y-%m-%d %H:%M:%S')))
logging.info('script ended at : {}'.format(endTime.strftime('%Y-%m-%d %H:%M:%S')))