· 4 years ago · Apr 19, 2021, 05:18 PM
1import sqlite3
2
3DBNAME = 'cab_share.db'
4
5
6class Schema:
7 def __init__(self):
8 self.conn = sqlite3.connect(DBNAME)
9 self.conn.executescript(
10 """
11 CREATE TABLE IF NOT EXISTS "Student" (
12 s_id varchar(15) PRIMARY KEY,
13 name varchar(30),
14 email varchar(50),
15 password varchar(15),
16 gender varchar(6),
17 phone_no varchar(15),
18 room_no varchar(8)
19 );
20 CREATE TABLE IF NOT EXISTS "Admin" (
21 admin_id varchar(15) PRIMARY KEY,
22 name varchar(30),
23 email varchar(50),
24 password varchar(15),
25 phone_no varchar(15)
26 );
27 CREATE TABLE IF NOT EXISTS "Car" (
28 car_no varchar(12) PRIMARY KEY,
29 admin_id varchar(15) ,
30 model varchar(10),
31 car_capacity int,
32 driver_name varchar(30),
33 driver_phone varchar(15) ,
34 FOREIGN KEY(admin_id) REFERENCES Admin(admin_id)
35 );
36
37 CREATE TABLE IF NOT EXISTS "Trip" (
38 trip_id integer PRIMARY KEY AUTOINCREMENT,
39 s_id varchar(15) ,
40 source varchar(20),
41 destination varchar(20),
42 leave_by_earliest varchar(20),
43 leave_by_latest varchar(20),
44 car_no varchar (20),
45 status varchar(20),
46 FOREIGN KEY (s_id)
47 REFERENCES Student(s_id)
48 );
49 CREATE TABLE IF NOT EXISTS "Pickup_details" (
50 car_no varchar(12),
51 location varchar(20),
52 start_time date,
53 end_time date,
54 PRIMARY KEY (car_no, location, start_time),
55 FOREIGN KEY (car_no)
56 REFERENCES Car(car_no)
57 );
58 """
59 )
60
61 def __del__(self):
62 self.conn.commit()
63 self.conn.close()
64
65
66class Student:
67 TABLENAME = "Student"
68
69 def __init__(self):
70 self.conn = sqlite3.connect(DBNAME)
71
72 def __del__(self):
73 self.conn.commit()
74 self.conn.close()
75
76 def create(self, params):
77 attributes = ['s_id', 'name', 'email', 'password', 'gender', 'phone_no', 'room_no']
78 self.conn.execute(f"""
79 INSERT INTO {self.TABLENAME} ({', '.join(attributes)})
80 VALUES ({', '.join(f'"{params.get(attr)}"' for attr in attributes)});
81 """)
82
83 # get details of student by passing s_id
84 def get_details(self, id, pas):
85 print("inside model" + id)
86 cur = self.conn.cursor()
87 temp = cur.execute(f"""
88 SELECT s_id,name, email, gender, phone_no, room_no
89 FROM Student
90 WHERE s_id ="{id}" and password="{pas}";
91 """).fetchone()
92 # print(temp)
93 dict = {"s_id": temp[0], "name": temp[1], "email": temp[2], "gender": temp[3], "phone_no": temp[4],
94 "room_no": temp[5]}
95 print(dict)
96 return dict
97
98
99class Admin:
100 TABLENAME = "Admin"
101
102 def __init__(self):
103 self.conn = sqlite3.connect(DBNAME)
104
105 def __del__(self):
106 self.conn.commit()
107 self.conn.close()
108
109 # remember not to pass admin_id here in params (it is auto generated)
110 def create(self, params):
111 attributes = ['name', 'email', 'password', 'phone_no']
112 self.conn.execute(f"""
113 INSERT INTO {self.TABLENAME} ({', '.join(attributes)})
114 VALUES ({', '.join(f'"{params.get(attr)}"' for attr in attributes)});
115 """)
116
117 def search(self, id, password):
118 print(id + " " + password + "service")
119 result = self.conn.execute(
120 f"""
121 SELECT admin_id from Admin WHERE admin_id="{id}" and password="{password}" ;
122"""
123 ).fetchone()
124 print(result)
125 return result
126
127
128class Car:
129 TABLENAME = "Car"
130
131 def __init__(self):
132 self.conn = sqlite3.connect(DBNAME)
133
134 def __del__(self):
135 self.conn.commit()
136 self.conn.close()
137
138 def create(self, params):
139 attributes = ['car_no', 'admin_id', 'model', 'car_capacity', 'driver_name', 'driver_phone']
140 self.conn.execute(f"""
141 INSERT INTO {self.TABLENAME} ({', '.join(attributes)})
142 VALUES ({', '.join(f'"{params.get(attr)}"' for attr in attributes)});
143 """)
144
145 def find_cars(self, location, start_time, end_time):
146 result = self.conn.execute(f"""
147 SELECT p.car_no, c.model, c.car_capacity, p.location,
148 p.start_time,
149 p.end_time ,
150 c.driver_name, c.driver_phone
151 FROM car c, Pickup_details p
152 WHERE c.car_no = p.car_no
153 AND location = '{location}' AND ( NOT( (p.end_time <'{start_time}'
154 OR p.start_time > '{end_time}')));
155 """).fetchall()
156 dict = []
157 for r in result:
158 dict.append({'car_no': r[0], 'model': r[1], 'car_capacity': r[2], 'location': r[3], 'driver_name': r[6],
159 'driver_no': r[7]})
160 print(dict)
161 return dict
162
163
164class Pickup_details:
165 TABLENAME = "Pickup_details"
166
167 def __init__(self):
168 self.conn = sqlite3.connect(DBNAME)
169
170 def __del__(self):
171 self.conn.commit()
172 self.conn.close()
173
174 def create(self, params):
175 print(params.get('car_no') + " " + params.get('location') + " " + params.get('start_time') + " " + params.get(
176 'end_time'))
177 return self.conn.execute(f"""
178 INSERT INTO {self.TABLENAME} (car_no, location, start_time, end_time)
179 VALUES ("{params.get("car_no")}", "{params.get("location")}",
180 "{params.get('start_time')}",
181 "{params.get('end_time')}");
182 """)
183
184 # def create(self, params):
185 # attributes = ['car_no', 'location' , 'start_time' , 'end_time']
186 # self.conn.execute(f"""
187 # INSERT INTO {self.TABLENAME} ({', '.join(attributes)})
188 # VALUES ({', '.join(f'"{params.get(attr)}"' for attr in attributes)});
189 # """)
190
191
192class Trip:
193 TABLENAME = "Trip"
194
195 def __init__(self):
196 self.conn = sqlite3.connect(DBNAME)
197
198 def __del__(self):
199 self.conn.commit()
200 self.conn.close()
201
202 def create(self, params):
203 self.conn.execute(f"""
204 INSERT INTO {self.TABLENAME} (s_id, source, destination, leave_by_earliest, leave_by_latest, car_no)
205 VALUES ("{params.get('s_id')}", "{params.get('source')}", "{params.get('destination')}",
206 "{params.get('leave_by_earliest')}",
207 "{params.get('leave_by_latest')}",
208 "{params.get('car_no', 'NULL')}");
209 """) # car_no = NULL if it does not exist
210
211 def trip_history(self, s_id):
212 print("inside models")
213 results = self.conn.execute(
214 f""" SELECT * FROM {self.TABLENAME} WHERE s_id="{s_id}" """
215 ).fetchall()
216 results_dic = []
217 for result in results:
218 results_dic.append(
219 {"trip_id": result[0], "s_id": result[1], "location": result[2], "destination": result[3],
220 "leave_by_earliest": result[4], "leave_by_latest": result[5]})
221 return results_dic
222
223 def search(self, source, destination, leave_by_earliest, leave_by_latest):
224 # also needed s_name, email, phone_no, room_no
225 results = self.conn.execute(f"""
226 SELECT *
227 FROM Trip T, Student S
228 WHERE T.source = '{source}' AND T.destination ='{destination}' AND (NOT((T.leave_by_earliest > '{leave_by_latest}')
229 OR T.leave_by_latest < '{leave_by_earliest}')) AND T.s_id=S.s_id;
230 """).fetchall()
231 print(results)
232 results_dic = []
233 for result in results:
234 results_dic.append(
235 {"trip_id": result[0], "s_id": result[1], "location": result[2], "destination": result[3],
236 "leave_by_earliest": result[4], "leave_by_latest": result[5], "cab_no": result[6], "name": result[8],
237 "email": result[9], "gender": result[10], "phone_no": result[11],
238 "room_no": result[12]})
239 return results_dic
240
241 # attributes to be changed are passed in attribs
242 def linkcar(self, trip_id, s_id, source, destination, leave_by_earliest, leave_by_latest, car_no):
243
244 self.conn.execute(f"""
245 Update Trip set s_id='{s_id}', source='{source}', destination='{destination}', leave_by_earliest='{leave_by_earliest}', leave_by_latest='{leave_by_latest}', car_no='{car_no}' where trip_id={trip_id};
246 """)
247 return "donee"
248
249 def update(self, trip_id, s_id, source, destination, leave_by_earliest, leave_by_latest):
250
251 self.conn.execute(f"""
252 Update Trip set s_id='{s_id}', source='{source}', destination='{destination}', leave_by_earliest='{leave_by_earliest}', leave_by_latest='{leave_by_latest}' where trip_id={trip_id};
253 """)
254 return "donee"
255
256 def delete(self, trip_id):
257 self.conn.execute(f"""
258 DELETE from {self.TABLENAME}
259 WHERE trip_id = {trip_id};
260 """)
261 return "wooosh"
262