-
Notifications
You must be signed in to change notification settings - Fork 0
/
combine_data.py
45 lines (38 loc) · 2.19 KB
/
combine_data.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import os
import csv
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import text, func, select, and_, or_, not_, desc, bindparam
from db_tables import metadata, Questions, Answers, Results, Students, StudentsTest, Graphs
# project_root = os.path.dirname(os.path.realpath('quizApp_Project/'))
# DATABASE = os.path.join(project_root, 'quizDB.db')
engine = create_engine('sqlite:///quizDB.db?check_same_thread=False', echo=True)
conn = engine.connect()
metadata.create_all(engine)
students = conn.execute(select([Students])).fetchall()
results = conn.execute(select([Results.c.student_id,
Results.c.answer,
Results.c.graph_id,
Answers.c.answer,
Questions.c.question,
StudentsTest.c.dataset]).\
select_from(Results.join(Students,
Students.c.student_id == Results.c.student_id).\
join(StudentsTest,
StudentsTest.c.student_test_id == Results.c.student_test_id).\
outerjoin(Questions,
Questions.c.question_id == StudentsTest.c.question_id).\
outerjoin(Answers,
Answers.c.answer_id == Results.c.answer)).\
where(Students.c.opt_in == 'na')).fetchall()
results = [[x.replace(',', '') if type(x) == type('s') else x for x in t] for t in results]
results = [['Student_id', 'answer', 'graph_id', 'answer_text', 'question', 'dataset']] + results
with open('student_table_list.csv', 'w', newline='') as csvfile:
spamwriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
[spamwriter.writerow(s) for s in students]
with open('results_table_list.csv', 'w', newline='') as csvfile:
spamwriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
[spamwriter.writerow(r) for r in results]