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