· 8 years ago · Jan 14, 2018, 03:26 AM
1import sqlite3
2import subprocess as sp
3
4
5
6class Database():
7
8 conn = sqlite3.connect('stdb.sqlite3')
9 cursor = conn.cursor()
10
11 def create_table(self):
12
13 query = '''
14 CREATE TABLE IF NOT EXISTS student(
15 id INTEGER PRIMARY KEY,
16 roll INTEGER,
17 name TEXT,
18 phone TEXT,
19 address TEXT
20 )
21 '''
22
23
24
25 def add_student(self, roll, name, phone, address):
26
27 query = '''
28 INSERT INTO student( roll, name, phone, address )
29 VALUES ( ?,?,?,? )
30 '''
31
32 self.cursor.execute(query, (roll, name, phone, address))
33
34 self.conn.commit()
35
36
37 def get_students(self):
38
39
40 query = '''
41 SELECT roll, name, phone, address
42 FROM student
43 '''
44
45 self.cursor.execute(query)
46 all_rows = self.cursor.fetchall()
47
48 self.conn.commit()
49
50
51 return all_rows
52
53
54
55
56 def get_student_by_roll(self, roll):
57
58
59 query = '''
60 SELECT roll, name, phone, address
61 FROM student
62 WHERE roll = {}
63 '''.format(roll)
64
65 self.cursor.execute(query)
66 all_rows = self.cursor.fetchall()
67
68 self.conn.commit()
69
70
71 return all_rows
72
73 def update_student(self, roll, name, phone, address):
74
75
76 query = '''
77 UPDATE student
78 SET name = ?, phone = ?, address = ?
79 WHERE roll = ?
80 '''
81
82 self.cursor.execute(query, (name, phone, address, roll))
83
84 self.conn.commit()
85
86
87 def delete_student(self, roll):
88
89
90 query = '''
91 DELETE
92 FROM student
93 WHERE roll = {}
94 '''.format(roll)
95
96 self.cursor.execute(query)
97 all_rows = self.cursor.fetchall()
98
99 self.conn.commit()
100
101
102 return all_rows
103
104class Division(Database):
105
106
107 def get_students_of_Rangpur(self):
108
109
110 query = '''
111 SELECT roll, name, phone, address
112 FROM student
113 WHERE address LIKE "Din%"
114 OR address LIKE "Kuri%"
115 OR address LIKE "Pan%"
116 OR address LIKE "Thak%"
117 OR address LIKE "Ran%"
118 OR address LIKE "Nil%"
119 OR address LIKE "Lal%"
120 OR address LIKE "Gai%"
121 '''
122
123 self.cursor.execute(query)
124 all_rows = self.cursor.fetchall()
125
126 self.conn.commit()
127
128
129 return all_rows
130
131 def get_students_of_Rajshahi(self):
132
133
134 query = '''
135 SELECT roll, name, phone, address
136 FROM student
137 WHERE address LIKE "Raj%"
138 OR address LIKE "Bog%"
139 OR address LIKE "Chap%"
140 OR address LIKE "Nao%"
141 OR address LIKE "Pab%"
142 OR address LIKE "Joy%"
143 OR address LIKE "Sira%"
144 OR address LIKE "Nat%"
145 '''
146
147 self.cursor.execute(query)
148 all_rows = self.cursor.fetchall()
149
150 self.conn.commit()
151
152
153 return all_rows
154
155class Operator(Database):
156
157 def get_students_of_GP_User(self):
158 query = '''
159 SELECT roll, name, phone, address
160 FROM student
161 WHERE phone LIKE "017%"
162 '''
163
164 self.cursor.execute(query)
165 all_rows = self.cursor.fetchall()
166
167 self.conn.commit()
168
169
170 return all_rows
171
172
173
174 def get_students_of_Robi_User(self):
175
176 query = '''
177 SELECT roll, name, phone, address
178 FROM student
179 WHERE phone LIKE "018%"
180 '''
181
182 self.cursor.execute(query)
183 all_rows = self.cursor.fetchall()
184
185 self.conn.commit()
186
187 return all_rows
188
189
190
191
192class MainClass(Division,Operator):
193
194 def __init__(self):
195
196 self.create_table()
197 self.select()
198
199 def __del__(self):
200 self.conn.close()
201 print("\n>>> Good Bye <<<")
202
203
204
205
206
207 def add_data(self, id_, name, phone, address):
208 self.add_student(id_, name, phone, address)
209 print("\nThe student has been added")
210
211 def get_data(self):
212 return self.get_students()
213
214 def show_data(self):
215 students = self.get_data()
216 for student in students:
217 print(student)
218
219 def show_data_of_Rangpur(self):
220 students = self.get_students_of_Rangpur()
221 for student in students:
222 print(student)
223
224 def show_data_of_Rajshahi(self):
225 students = self.get_students_of_Rajshahi()
226 for student in students:
227 print(student)
228
229 def show_data_of_GP_user(self):
230 students = self.get_students_of_GP_User()
231 for student in students:
232 print(student)
233
234 def show_data_of_Robi_user(self):
235 students = self.get_students_of_Robi_User()
236 for student in students:
237 print(student)
238
239 def show_data_by_id(self, id_):
240 students = self.get_student_by_roll(id_)
241 if not students:
242 print("No data found at roll", id_)
243 else:
244 print(students)
245
246 def select(self):
247 sp.call('clear', shell=True)
248 sel = input("1. Add data\n2.Show Data\n3.Search\n4.Update\n5.Delete\n6.Exit\n\n")
249
250 if sel == '1':
251 sp.call('clear', shell=True)
252 id_ = int(input('id: '))
253 name = input('Name: ')
254 phone = input('phone: ')
255 address = input('address: ')
256 self.add_data(id_, name, phone, address)
257
258 elif sel == '2':
259 sp.call('clear', shell=True)
260 sel = input("1.All Students\n2.Show by Division\n3.Show by Operator\n\n")
261
262 if sel == '1':
263 sp.call('clear', shell=True)
264 self.show_data()
265 input("\n\npress enter to back:")
266
267 elif sel == '2':
268 sp.call('clear', shell=True)
269 sel = input("1.Students of Rangpur Division\n2.Students of Rajshahi Division\n\n")
270 if sel == '1':
271 sp.call('clear', shell=True)
272 self.show_data_of_Rangpur()
273 input("\n\npress enter to back:")
274
275 elif sel == '2':
276 sp.call('clear', shell=True)
277 self.show_data_of_Rajshahi()
278
279 elif sel == '3':
280 sp.call('clear', shell=True)
281 sel = input("1.Gp\n2.Robi\n3.BL\n4.Airtel\n5.Teletalk\n\n")
282
283 if sel == '1':
284 sp.call('clear', shell=True)
285 self.show_data_of_GP_user()
286
287 elif sel == '2':
288 sp.call('clear', shell=True)
289 self.show_data_of_Robi_user()
290
291 input("\n\npress enter to back:")
292
293
294 elif sel == '3':
295 sp.call('clear', shell=True)
296 id__ = int(input('Enter Id: '))
297 self.show_data_by_id(id__)
298 input("\n\npress enter to back:")
299
300 elif sel == '4':
301 sp.call('clear', shell=True)
302 id__ = int(input('Enter Id: '))
303 self.show_data_by_id(id__)
304 print()
305 name = input('Name: ')
306 phone = input('Phone: ')
307 address = input('Address: ')
308 self.update_student(id__, name, phone, address)
309 input("\nYour data has been updated \n\npress enter to back:")
310
311 elif sel == '5':
312 sp.call('clear', shell=True)
313 id__ = int(input('Enter Id: '))
314 self.show_data_by_id(id__)
315 self.delete_student(id__)
316 input("\nYour data has been deleted \npress enter to back:")
317 else:
318 return 0
319 return 1
320
321
322
323
324
325Output = MainClass()
326
327del Output