-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.py
110 lines (72 loc) · 2.81 KB
/
schema.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
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
from libs import *
import bcrypt
def create_schema():
with open('./schema/schema_tables.txt', 'r') as schema:
queries = schema.readlines()
for query in queries:
db_cur.execute(query)
db_con.commit()
create_schema()
def add_comps():
with open('./schema/schema_comp.csv') as comps:
read_comps = comps.readlines()
#remove header row:
read_comps.pop(0)
for comp in read_comps:
comp_list = comp.strip().split(',')
query = ('INSERT INTO Competencies (name, date_created) VALUES (?,?)')
db_cur.execute(query, comp_list)
db_con.commit()
add_comps()
def add_users():
with open('./schema/schema_users.csv', encoding='utf-8-sig') as users:
read_users = users.readlines()
#remove header row:
header = read_users.pop(0)
header = header.strip().split(',')
salt = bcrypt.gensalt()
for row in read_users:
row = row.strip().split(',')
user_dict = dict(zip(header,row))
new_header = []
row_list = []
var_list = []
for key,value in user_dict.items():
if value:
if key == 'password':
#convert plaintext password to hash
hashed = bcrypt.hashpw(value.encode('utf'), salt)
value = hashed.decode()
new_header.append(key)
row_list.append(value)
var_list.append('?')
new_header = f'({",".join(new_header)})'
var_list =f'({",".join(var_list)})'
query = (f"INSERT INTO Users {new_header} VALUES {var_list}")
db_cur.execute(query, row_list)
db_con.commit()
add_users()
def add_assesments():
with open('./schema/schema_comp.csv') as comps:
read_comps = comps.readlines()
#remove header row:
read_comps.pop(0)
assess_list = ['Manager Evaluation', 'Peer Evaluation', 'Practical Skills', 'Self Evaluation']
assessments = {}
for comp_id,comp in enumerate(read_comps, start=1):
counter = 1
comp_list = comp.strip().split(',')
while counter <= 4:
assess_id = f'{comp_id}.{counter}'
assess_name = f'{comp_list[0]} - {assess_list[counter-1]}'
#Creates a dictionary for all the generated assesment names and corresponding ID's
assessments[assess_id] = assess_name
#parameters for inserting into db
assess_db = [assess_id,comp_id,assess_name,counter]
query = "INSERT INTO Assessments (assess_id, comp_id, name, assess_type) VALUES (?,?,?,?)"
db_cur.execute(query,assess_db)
counter += 1
db_con.commit()
# print(assessments)
#Write dictionary to db as ID, name
add_assesments()