-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
55 lines (43 loc) · 1.61 KB
/
database.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
import sqlite3
import csv
conn = sqlite3.connect('unifespSubjects.db')
cur = conn.cursor()
def create_database(conn, cur):
cur.execute("""
CREATE TABLE if not exists ucs
(
id unique primary key,
subjectName varchar(50),
category varchar(50),
chTotal int,
requirements varchar(300),
semesterAvailable varchar(25)
)
""")
conn.commit()
cur.execute("""
CREATE UNIQUE INDEX IF NOT EXISTS
temp_line_index on ucs (subjectName)
""")
def process_file(conn, cur, filename):
with open(filename, "rt") as f:
reader = csv.reader(f)
next(csv.reader(f), None)
for entry in reader:
try:
print(reader.line_num)
record = (entry[0], entry[1], entry[2], entry[3], entry[4], entry[5])
# build SQL Insert statement with placeholders
stmt = "INSERT INTO ucs(id, subjectName, category, chTotal, requirements, semesterAvailable)";
stmt += "values(?, ?, ?, ?, ?, ?)"
# execute statement with tuple data
cur.execute(stmt, record)
if cur.lastrowid % 268 == 0:
conn.commit()
except csv.Error as e:
print(f"line: {reader.line_num}, Record:{record}")
create_database(conn, cur)
process_file(conn, cur, '/Users/lewdamy/my-projects/fix_unifesp/csvFiles/Materias-Unifesp-Atualizada.csv')
conn.commit()
conn.close()
print("Job Complete")