#https://www.w3schools.com/python/default.asp

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="clicawjc_cms_user",
  password="15177247025m1@M",
  database="clicawjc_cms_db"     
)

mycursor = mydb.cursor()

import pandas as pd
excel_data_df = pd.read_excel(
     'projectData/sample200.xlsx',
     engine='openpyxl',
)

excel_data_dict = excel_data_df.to_dict(orient='record')#each row as dict 

for r in excel_data_dict:
    ingredients = r['ingredients'] if r['ingredients'] else None
    cur.execute("INSERT INTO mainApp_drug (name_en,name_ar,ingredients,is_active) VALUES ('{}','{}','{}','1')".format(r['name_en'],r['name_ar'],ingredients))
    
    
    
mydb.commit()


############################################

#https://products.aspose.app/cells/conversion/xlsx-to-json
import pymysql
db = pymysql.connect(host='localhost',user='clicawjc_cms_user',password='15177247025m1@M',database='clicawjc_cms_db')

import json

mycursor = db.cursor()  
# Opening JSON file
# Opening JSON file
f = open('projectData/sample200.json')
data = json.load(f)

for i in data['emp_details']:
    print(i)


for r in data:
    ingredients = r['ingredients'] if r['ingredients'] else None
    mycursor.execute("INSERT INTO mainApp_drug (name_en,name_ar,ingredients,is_active) VALUES ('{}','{}','{}','1')".format(r['name_en'],r['name_ar'],ingredients))
 
 #output = mycursor.fetchall()
  db.commit()
# Closing file
f.close()

mycursor.close()
##