-
Notifications
You must be signed in to change notification settings - Fork 1
/
texttosql.py
110 lines (95 loc) · 3.89 KB
/
texttosql.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
import psycopg2
college_info = [['AERONAUTICAL ENGINEERING',
[['ACE16AN007', [['BE103', 'C']]],
['ACE16AN012', [['EC100', 'Nochange'], ['BE100', 'Nochange']]],
['ACE16AN014', [['BE100', 'Nochange']]], ['ACE16AN016', [['BE100', 'Nochange']]],
['ACE16AN022', [['BE100', 'Nochange']]], ['ACE16AN029', [['BE100', 'Nochange']]]]],
['CIVIL ENGINEERING',
[['ACE16CE001', [['BE10101', 'Nochange']]],
['ACE16CE002', [['BE10101', 'A']]],
['ACE16CE005', [['BE110', 'Nochange'],['PH100', 'Nochange'], ['BE10101', 'Nochange']]],
['ACE16CE006', [['BE10101', 'Nochange']]], ['ACE16CE007', [['BE10101', 'Nochange']]],
['ACE16CE011', [['BE10101', 'Nochange'], ['BE110', 'C'], ['MA101', 'Nochange']]],
['ACE16CE012', [['BE10101', 'C']]], ['ACE16CE013', [['BE10101', 'Nochange']]],
['ACE16CE014', [['BE10101', 'Nochange']]],
['ACE16CE021', [['BE10101', 'Nochange'], ['BE110', 'Nochange']]], ['ACE16CE023', [['MA101', 'C'], ['BE10101', 'Nochange']]],
['ACE16CE025', [['BE10101', 'Nochange']]], ['ACE16CE026', [['BE10101', 'Nochange']]],
['ACE16CE027', [['BE10101', 'Nochange'], ['PH100', 'B+']]],
['ACE16CE029',[['BE10101', 'Nochange']]], ['ACE16CE030', [['BE10101', 'Nochange']]],
['ACE16CE031', [['PH100', 'Nochange']]], ['ACE16CE032', [['BE10101', 'Nochange']]],
['ACE16CE034', [['BE10101', 'Nochange'], ['BE110', 'Nochange']]],
['ACE16CE037', [['BE110', 'Nochange']]],
['ACE16CE040', [['BE110', 'Nochange'], ['BE10101', 'Nochange']]],
['ACE16CE041', [['EE100', 'Nochange']]]]],
['ELECTRICAL AND ELECTRONICS ENGINEERING',
[['ACE16EE011', [['CE100', 'Nochange']]]]],
['ELECTRONICS & COMMUNICATION ENGG',
[['ACE16EC003', [['MA101', 'C']]]]],
['MECHANICAL ENGINEERING',
[['ACE15ME047', [['CY100', 'C']]],
['ACE16ME005', [['BE100', 'Nochange']]],
['ACE16ME009', [['BE103', 'Nochange']]],
['ACE16ME014', [['BE103', 'Nochange'], ['MA101', 'Nochange'], ['EE100', 'Nochange']]],
['ACE16ME015', [['BE103', 'Nochange'], ['MA101', 'Nochange']]],
['ACE16ME016', [['BE100', 'Nochange']]],
['ACE16ME019', [['EE100', 'Nochange'], ['BE10102', 'Nochange']]],
['ACE16ME027', [['BE100', 'Nochange'], ['CY100', 'P']]],
['ACE16ME029', [['BE100', 'Nochange']]], ['ACE16ME030', [['BE10102', 'Nochange']]],
['ACE16ME031', [['EE100', 'C']]], ['ACE16ME033', [['BE10102', 'Nochange']]],
['ACE16ME035', [['EE100', 'Nochange'], ['BE10102', 'Nochange']]],
['ACE16ME036', [['BE100', 'Nochange']]]]]]
def make_sql(l,c_name):
#
#
# print(dept_list)
# for i in dept_list:
# print(i[0])
# for j in i[1]:
# print(j[0])
# for k,l in j[1]:
# print("{} grade for {}".format(l,k))
#
with open("./database.sql","a") as f:
f.write("INSERT INTO college (college_name) values ('{}'); ".format(c_name))
for i in (dept_list):
f.write("INSERT into department (dept_name,) values ('{}'); ".format(c_name))
def create_tables():
with open("./database.sql","w+") as f:
table = """
drop table dept_course;
drop table student_marks;
drop table dept_student;
drop table course;
drop table department;
drop table college;
CREATE TABLE college (
college_id serial PRIMARY KEY,
college_name varchar(80) NOT NULL
);
CREATE TABLE department (
dept_id serial PRIMARY KEY,
dept_name varchar(50) NOT NULl,
colleg_id serial references college(college_id)
);
CREATE TABLE course (
course_id serial PRIMARY KEY,
course_code varchar(20) UNIQUE
);
CREATE TABLE dept_student (
register_id varchar(15) PRIMARY KEY,
dept_id serial references department(dept_id)
);
CREATE TABLE student_marks (
mark_id serial PRIMARY KEY,
register_id varchar(15) references dept_student(register_id),
course_id serial references course(course_id)
);
CREATE TABLE dept_course (
dc_id serial PRIMARY KEY,
dept_id serial references department(dept_id),
course_id serial references course(course_id)
);
"""
f.write(table)
if __name__ == '__main__':
create_tables()