initdb_from_csv.py
2.97 KB
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sys
import sqlite3
import csv
import argparse
import bcrypt
import os
import string
import re
# SIIUE names have alien strings like "(TE)" and are sometimes capitalized
# We remove them so that students dont keep asking what it means
def fixname(s):
return string.capwords(re.sub('\(.*\)', '', s).strip())
def genstudent(reader, pw=''):
for i, r in enumerate(reader):
num = r['N.º']
name = fixname(r['Nome'])
yield (r['N.º'], fixname(r['Nome']), '')
print('{} students inserted.'.format(i+1))
# ---- DATABASE SCHEMA ----
sql_cmd = '''PRAGMA foreign_keys = ON;
CREATE TABLE students (
number TEXT PRIMARY KEY,
name TEXT,
password TEXT
);
CREATE TABLE tests (
test_id TEXT NOT NULL,
student_id TEXT NOT NULL,
grade REAL,
start_time TEXT,
finish_time TEXT,
FOREIGN KEY(student_id) REFERENCES students(number)
);
CREATE TABLE questions (
test_id TEXT NOT NULL,
question_id TEXT NOT NULL,
student_id TEXT NOT NULL,
grade REAL,
time TEXT,
FOREIGN KEY(student_id) REFERENCES students(number)
);'''
# --------- Parse command line options -----------
argparser = argparse.ArgumentParser(description='Create new database from a CSV file (SIIUE format)')
argparser.add_argument('--db', default='students.db', type=str, help='database filename')
argparser.add_argument('csvfile', nargs='?', type=str, default='', help='CSV filename')
args = argparser.parse_args()
db_exists = os.path.exists(args.db)
with sqlite3.connect(args.db) as c:
# use existing or create new database schema
if db_exists:
print('-> Using previous database "{}"...'.format(args.db))
else:
print('-> Creating new database "{}"...'.format(args.db))
c.executescript(sql_cmd)
# get students
if args.csvfile:
csvfile = open(args.csvfile, encoding='iso-8859-1')
print('-> Using students from CSV file "{}"...'.format(args.csvfile))
students = genstudent(csv.DictReader(csvfile, delimiter=';', quotechar='"'))
else:
print('-> Creating fake students numbered 1 to 5...'.format(args.csvfile))
students = [
('1', 'Student1', ''),
('2', 'Student2', ''),
('3', 'Student3', ''),
('4', 'Student4', ''),
('5', 'Student5', '')
]
# insert students into database
print('-> Inserting students into database... ')
try:
c.executemany('INSERT INTO students VALUES (?,?,?)', students)
except sqlite3.IntegrityError:
print('** ERROR ** Students already exist. Aborted!')
sys.exit(1)
# insert professor into database
print('-> Inserting professor (id=0)...')
try:
c.execute('INSERT INTO students VALUES (?,?,?)', ('0', 'Professor', ''))
except sqlite3.IntegrityError:
print('** WARNING ** Professor already exists.')
print('Done.')