reids.py

from tabulate import tabulate
import sqlite3
import json
from datetime import datetime

con = sqlite3.connect('reids.db')
reid_json = json.load(open('reids.json'))
cur=con.cursor()

order_id = 1
charge_id = 1
payment_id = 1
item_id = 1


for order in reid_json['orders']:
    # Update the charges order
    charges_temp = order['charges']
    charge_str =f'{charge_id}, {order_id}, '
    for v in charges_temp:
        if v == 'date':
            # Convert the date time string to datetime object
            dt_object = datetime.strptime(charges_temp[v], '%m/%d/%y %H:%M')

            # Convert the datetime object to SQL-friendly string format
            var = dt_object.strftime('%Y-%m-%d %H:%M:%S')
            var = f'''\'{var}\''''
        else:
            var = charges_temp[v]
        charge_str+=f'{var}, '
    charge_str = charge_str.rstrip(', ')
    command_str = f"""INSERT INTO charges VALUES ({charge_str});"""
    cur.execute(command_str)
    con.commit()
    
    # Update the payment table
    payment_temp = order['payment']
    payment_str = f'{payment_id}, {order_id}, '
    for v in payment_temp:
        
        if v=='cardholder':
            temp = payment_temp[v].split(' ')
            first = temp[0]
            last = temp[1]
            payment_str +=f"""\'{first}\', \'{last}\', """
        elif v=='method':
            payment_str +=f"""\'{payment_temp[v]}\', """
            if payment_temp[v]=='cash':
                payment_str += 'NULL, '*5
        elif v=='card_type':
            payment_str +=f"""\'{payment_temp[v]}\', """
        else:
            var = payment_temp[v]
            payment_str +=f'{var}, '
    payment_str = payment_str.rstrip(', ')
    command_str = f"""INSERT INTO payment VALUES ({payment_str});"""
    cur.execute(command_str)
    con.commit()
    
    # Update the items table. NOTE: may contain multiple items
    item_arr = order['items']
    for item in item_arr:
        item_str = f'{item_id}, {order_id}, '
        for v in item:
            if v=='name':
                item_str += f"""\'{item[v]}\', """
            else:
                item_str += f'{item[v]}, '
        item_str = item_str.rstrip(', ')
        command_str = f"""INSERT INTO item VALUES ({item_str});"""
        cur.execute(command_str)
        con.commit()
        item_id+=1
    
    order_id+=1
    charge_id+=1
    payment_id+=1