-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
229 lines (187 loc) · 8.16 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
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
import pymysql
class Database:
def __init__(self):
host = #Host
user = #Database User
password = #Database Password
db = #Database Name
self.con = pymysql.connect(
host=host, user=user, password=password, db=db, cursorclass=pymysql.cursors.DictCursor)
self.cur = self.con.cursor()
############################ STUDENTS #########################
def list_students(self):
self.cur.execute("SELECT * FROM ogrenci")
result = self.cur.fetchall()
return result
def get_students_number(self):
self.cur.execute(
"SELECT COUNT(*) FROM ogrenci")
result = self.cur.fetchone()
return result
def get_students_by_completing_internship(self, is_completed_internship):
self.cur.execute(
"SELECT * FROM ogrenci WHERE staj_bitti=%s", is_completed_internship)
result = self.cur.fetchall()
return result
def get_student_by_student_number(self, student_number):
self.cur.execute(
"SELECT * FROM ogrenci WHERE ogrenci_no=%s", student_number)
result = self.cur.fetchone()
return result
def add_student(self, data):
self.cur.execute(
"INSERT INTO ogrenci(ogrenci_no, isim, soyisim, ogretim) VALUES(%s, %s, %s, %s)", data)
self.con.commit()
def delete_student_by_student_number(self, student_number):
self.cur.execute(
"DELETE FROM ogrenci WHERE ogrenci_no=%s", student_number)
self.con.commit()
def update_student_by_student_number(self, data):
self.cur.execute(
"UPDATE ogrenci set staj_bitti=%s WHERE ogrenci_no=%s", data)
self.con.commit()
###############################################################
############################ TOPIC ############################
def list_topics(self):
self.cur.execute("SELECT * FROM konu")
result = self.cur.fetchall()
return result
def get_topics_number(self):
self.cur.execute("SELECT COUNT(*) FROM konu")
result = self.cur.fetchone()
return result
def add_topic(self, data):
self.cur.execute("INSERT INTO konu VALUES(%s)", data)
self.con.commit()
def update_topic(self, data):
self.cur.execute("UPDATE konu SET isim=%s WHERE isim=%s", data)
self.con.commit()
def delete_topic(self, data):
self.cur.execute("DELETE FROM konu WHERE isim=%s", data)
self.con.commit()
###############################################################
######################### INTERNSHIPS #########################
def list_internships(self):
self.cur.execute("SELECT * FROM staj")
result = self.cur.fetchall()
return result
def get_total_days_by_city(self, sehir):
self.cur.execute("SELECT SUM(toplam_gun) FROM staj WHERE sehir=%s", sehir)
result = self.cur.fetchone()
return result
def get_accepted_days_by_city(self, sehir):
self.cur.execute("SELECT SUM(kabul_edilen_gun) FROM staj WHERE sehir=%s", sehir)
result = self.cur.fetchone()
return result
def get_topic_number(self, data):
self.cur.execute("SELECT COUNT(*) FROM staj WHERE staj_konusu=%s", data)
result = self.cur.fetchone()
return result
def get_topic_number_by_internship(self, data):
self.cur.execute("SELECT COUNT(*) FROM staj WHERE staj_konusu=%s and SUBSTR(baslama_tarihi,1,4)=%s", data)
result = self.cur.fetchone()
return result
def get_sum_of_total_days(self, data):
self.cur.execute("SELECT SUM(toplam_gun) FROM staj WHERE staj_konusu=%s and SUBSTR(baslama_tarihi,1,4)=%s", data)
result = self.cur.fetchone()
return result
def get_sum_of_total_days_general(self, data):
self.cur.execute("SELECT SUM(toplam_gun) FROM staj WHERE staj_konusu=%s", data)
result = self.cur.fetchone()
return result
def get_sum_of_accepted_days(self, data):
self.cur.execute("SELECT SUM(kabul_edilen_gun) FROM staj WHERE staj_konusu=%s and SUBSTR(baslama_tarihi,1,4)=%s", data)
result = self.cur.fetchone()
return result
def get_sum_of_accepted_days_general(self, data):
self.cur.execute("SELECT SUM(kabul_edilen_gun) FROM staj WHERE staj_konusu=%s", data)
result = self.cur.fetchone()
return result
def get_internships_number(self):
self.cur.execute("SELECT COUNT(*) FROM staj")
result = self.cur.fetchone()
return result
def add_internship(self, data):
self.cur.execute(
"INSERT INTO staj VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", data)
self.con.commit()
def delete_internship(self, data):
self.cur.execute(
"DELETE FROM staj WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
self.con.commit()
def get_internship(self, data):
self.cur.execute(
"SELECT * FROM staj WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
result = self.cur.fetchone()
return result
def get_internships_by_is_interviewed(self, is_interviewed):
self.cur.execute(
"SELECT * FROM staj WHERE staj_degerlendirildi=%s", is_interviewed)
result = self.cur.fetchall()
return result
def get_internship(self, data):
self.cur.execute(
"SELECT * FROM staj WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
result = self.cur.fetchone()
return result
def get_internships_by_student_number(self, student_number):
self.cur.execute(
"SELECT * FROM staj WHERE ogrenci_no=%s", student_number)
result = self.cur.fetchall()
return result
def get_total_days_by_student_number(self, student_number):
self.cur.execute(
"SELECT SUM(toplam_gun) FROM staj WHERE ogrenci_no=%s", student_number)
result = self.cur.fetchone()
return result
def get_accepted_days_by_student_number(self, student_number):
self.cur.execute(
"SELECT SUM(kabul_edilen_gun) FROM staj WHERE ogrenci_no=%s", student_number)
result = self.cur.fetchone()
return result
def update_internship(self, data):
self.cur.execute(
"UPDATE Staj SET staj_degerlendirildi=%s, kabul_edilen_gun=%s WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
self.con.commit()
###############################################################
########################### TEACHERS ##########################
def get_teachers(self):
self.cur.execute(
"SELECT * FROM komisyon")
result = self.cur.fetchall()
return result
def get_teachers_by_id(self, id):
self.cur.execute(
"SELECT * FROM komisyon where id=%s", id)
result = self.cur.fetchone()
return result
######################### INTERVIEWS ##########################
def add_interview(self, data):
self.cur.execute(
"INSERT INTO Mulakat(ogrenci_no, baslama_tarihi, tarih, saat, komisyon_uye1, komisyon_uye2) VALUES(%s, %s, %s, %s, %s, %s)", data)
result = self.con.commit()
def get_interviews_number(self):
self.cur.execute(
"SELECT COUNT(*) FROM Mulakat")
result = self.cur.fetchone()
return result
def get_interviews(self):
self.cur.execute(
"SELECT * FROM Mulakat")
result = self.cur.fetchall()
return result
def delete_interview(self, data):
self.cur.execute(
"DELETE FROM Mulakat WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
self.con.commit()
def update_interview(self, data):
self.cur.execute(
"UPDATE Mulakat SET devam=%s, calisma=%s, isi_vaktinde_yapma=%s, amire_davranis=%s, is_arkadaslarina_davranis=%s, prove=%s, duzen=%s, sunum=%s, icerik=%s, mulakat=%s WHERE ogrenci_no=%s and baslama_tarihi=%s", data)
self.con.commit()
def add_commission(self, data):
self.cur.execute(
"INSERT INTO komisyon(unvan, isim_soyisim) VALUES(%s, %s)", data)
self.con.commit()
def close(self):
self.cur.close()
self.con.close()