from sqlalchemy import create_engine
from sqlalchemy.sql import text, func, select, and_, or_, not_, desc, bindparam
from db_tables import metadata, Questions, Answers, Results, Students, StudentsTest, Graphs
import pandas as pd
engine = create_engine('sqlite:///quizDB.db?check_same_thread=False', echo=True)
conn = engine.connect()
metadata.create_all(engine)
#CLEAR RESULTS TABLE
if engine.dialect.has_table(engine.connect(), "results"):
conn.execute(Results.delete())
df_questions = pd.read_excel('quizApp/data/question_table.xlsx', 'Sheet1')
#check for table and if it is there clear before writing to
if engine.dialect.has_table(engine.connect(), "questions"):
conn.execute(Questions.delete())
conn.execute(Questions.insert(), [{'question_id':data.question_id,
'dataset':data.dataset,
'question':data.question,
'question_type':data.question_type}
for ix, data in df_questions.iterrows()])
df_answers = pd.read_excel('quizApp/data/answer_table.xlsx', 'Sheet1')
#check for table and if it is there clear before writing to
if engine.dialect.has_table(engine.connect(), "answers"):
conn.execute(Answers.delete())
