initdb_from_csv.py
2.26 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
#!/usr/bin/env python3.4
# -*- coding: utf-8 -*-
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
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', type=str, help='CSV filename')
args = argparser.parse_args()
# --------- Parse CSV --------
with open(args.csvfile, encoding='iso-8859-1') as csvfile: # SIIUE format
reader = csv.DictReader(csvfile, delimiter=';', quotechar='"')
db_exists = os.path.exists(args.db)
with sqlite3.connect(args.db) as c:
if not db_exists:
print('Creating new database "{}"...'.format(args.db))
c.executescript(sql_cmd)
c.execute('INSERT INTO students VALUES (?,?,?)', ('0', 'Professor', ''))
else:
print('Database "{}" already exists.'.format(args.db))
print('Inserting students into database... ', end='')
try:
c.executemany('INSERT INTO students VALUES (?,?,?)', genstudent(reader))
except sqlite3.IntegrityError:
print('\rStudents already in the database. Aborting!!!')