pip install pandas
pip install openpyxl

import sqlite3
con = sqlite3.connect('db.sqlite3')
cur = con.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))

con.commit()
con.close()


#DELETE ALL DATA FROM TABLE 
cur.execute("DELETE FROM mainApp_drug")

cur.execute("UPDATE mainApp_drug SET ingredients = NULL WHERE ingredients='nan'"))





INSERT INTO mainApp_drug ( name_en, name_ar,  ingredients, notes, is_active)
VALUES ("betaserc 8mg  100 tab","بيتاسرك 8 مجم 100 قرص
","betahistine dihydrochloride
","","True");



import sqlite3
con = sqlite3.connect('db.sqlite3')
cur = con.cursor()
# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

import pandas as pd
df = pd.read_excel (r'projectData/sample200.xlsx')
df = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx')
print (df)





df1 = pd.read_excel(
     os.path.join(APP_PATH, "Data", "aug_latest.xlsm"),
     engine='openpyxl',
)

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 
#excel_data_dict = excel_data_df.to_dict()#each column as list of 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))

con.commit()
con.close()

    cur.execute("UPDATE mainApp_drug SET ingredients = NULL WHERE ingredients='nan'"))



for r in df1 : print(r)

for r in df1 : 
    for c in df1[r]:print(c)






