· 5 years ago · Feb 28, 2020, 09:10 AM
1from bottle import get, post, run, request, response
2import sqlite3
3import json
4
5
6HOST = 'localhost'
7PORT = 7007
8
9conn = sqlite3.connect("movies.db")
10
11
12def url(resource):
13 return f"http://{localhost}:{7007}{resource}"
14
15
16def format_response(d):
17 return json.dumps(d, indent=4) + "\n"
18
19
20#Pong
21@get('/ping')
22def ping():
23 print ("pong")
24 return "pong" + "\n"
25
26
27@get('/movies')
28def get_movies():
29 query = """
30 SELECT *
31 FROM Movies
32 WHERE 1 = 1
33 """
34 params = []
35 if request.query.name:
36 query += "AND title = ?"
37 params.append(request.query.name)
38 if request.query.year:
39 query += "AND year = ?"
40 params.append(request.query.year)
41 c = conn.cursor()
42 c.execute(
43 query,
44 params
45 )
46 s = [{"title": title, "year": year, "IMBD": IMBD, "length_min": length_min}
47 for (title, year, IMBD, length_min) in c]
48 response.status = 200
49 return json.dumps({"data": s}, indent=4)
50
51
52@get('/movies/<imdb>')
53def get_imbd(imdb):
54 c = conn.cursor()
55 c.execute(
56 """
57 SELECT *
58 FROM Movies
59 WHERE IMDB = ?
60 """,
61 [imdb]
62
63
64 )
65 s = [{"title": title, "year": year, "IMDB": IMDB, "length_min": length_min}
66 for (title, year, IMDB, length_min) in c]
67 response.status = 200
68 return format_response({"data":s})
69
70
71
72@get('/performances')
73def get_preformance():
74
75 c = conn.cursor()
76 c.execute(
77 """
78 SELECT screen_id, date, time, title, year, remainingSeats, screening.cinema_name
79 FROM Screening
80 JOIN Movies
81 USING (imdb)
82 LEFT JOIN Ticket
83 USING (screen_id)
84 JOIN Cinema
85 USING(cinema_name)
86 GROUP by screen_id
87 """
88 )
89 s = [{ 'screen_id': screen_id, 'date': date, 'time': time, "title": title, "year": year, 'remainingSeats': remainingSeats, 'cinema_name': cinema_name }
90 for (screen_id, date, time, title, year, remainingSeats, cinema_name) in c]
91 response.status = 200
92 return format_response({"data":s})
93
94
95
96
97def get_cinema_seats(screen_id):
98 c = conn.cursor()
99 c.execute(
100 """
101 SELECT cinema.capacity
102 FROM Cinema
103 JOIN Screening
104 USING (cinema_name)
105 WHERE screen_id == ?
106 """,
107 [screen_id]
108 )
109 g = [int(record[0]) for record in c.fetchall()]
110 a = g[0]
111
112 c.execute(
113 """
114 SELECT count()
115 FROM Cinema
116 JOIN Screening
117 USING (cinema_name)
118 JOIN Ticket
119 USING (screen_id)
120 WHERE screen_id == ?
121 """,
122 [screen_id]
123 )
124 m = [int(record[0]) for record in c.fetchall()]
125 b = m[0]
126 if (b == a):
127 return("No seats left.")
128 else:
129 return("Seats available.")
130
131
132
133
134@post('/performances')
135def post_performance():
136 response.content_type = 'application/json'
137 imdb = request.query.imdb
138 theater = request.query.theater
139 date = request.query.date
140 time = request.query.time
141
142
143 if not (imdb and theater and date and time):
144 response.status = 400
145 return format_response({"error": "Missing parameter"})
146
147 c = conn.cursor()
148 c.execute(
149 """
150 INSERT
151 INTO Screening(imdb, cinema_name, date, time, remainingSeats)
152 VALUES (?, ?, ?, ?, (SELECT capacity
153 FROM Cinema
154 where cinema_name == ?))
155 """,
156 [imdb, theater, date, time, theater]
157 )
158 conn.commit()
159
160
161
162
163@post('/tickets')
164def post_ticket():
165
166 screen_id = request.query.performance
167 username = request.query.user
168 password = request.query.pwd
169
170 if not (screen_id and username and password):
171 response.status = 400
172 return format_response({"error": "Missing parameter"})
173
174 if get_cinema_seats(screen_id) == "No seats left.":
175 response.status = 400
176 return format_response({"error": "No seats left"})
177
178
179
180 c = conn.cursor()
181 c.execute(
182 """
183 INSERT
184 INTO Ticket(screen_id, username, password)
185 VALUES (?, ?, ?)
186
187
188 """,
189 [screen_id, username, password]
190 )
191 conn.commit()
192
193
194
195
196
197
198@post('/reset')
199def post_reset():
200 c = conn.cursor()
201 c.executescript(
202 """
203
204 PRAGMA foreign_keys=OFF;
205
206 DROP TABLE IF EXISTS Account;
207 DROP TABLE IF EXISTS User;
208 DROP TABLE IF EXISTS Ticket;
209 DROP TABLE IF EXISTS Screening;
210 DROP TABLE IF EXISTS Hall;
211 DROP TABLE IF EXISTS Cinema;
212 DROP TABLE IF EXISTS Movies;
213
214 PRAGMA foreign_keys=ON;
215
216 CREATE TABLE User (
217 ssn int,
218 first_name varchar(20),
219 last_name varchar(40),
220 PRIMARY KEY (ssn)
221 );
222
223 CREATE TABLE Account (
224 ssn int,
225 username varchar(16),
226 password varchar(36),
227 PRIMARY KEY (username),
228 FOREIGN KEY (ssn) REFERENCES User(ssn)
229 );
230
231 CREATE TABLE Ticket (
232 ticket_id TEXT DEFAULT (lower(hex(randomblob(16)))),
233 screen_id varchar(20),
234 username varchar(16),
235 password varchar(36),
236
237 FOREIGN KEY (username) REFERENCES Account(username),
238 FOREIGN KEY (screen_id) REFERENCES Screening(screen_id)
239 );
240
241 CREATE TABLE Screening (
242 screen_id TEXT DEFAULT (lower(hex(randomblob(16)))),
243 cinema_name varchar(16) not NULL,
244 imdb varchar(16) not NULL,
245 date DATE,
246 time TIME,
247 remainingSeats int,
248 PRIMARY KEY (screen_id),
249 FOREIGN KEY (cinema_name) REFERENCES Cinema(cinema_name),
250 FOREIGN KEY (imdb) REFERENCES Movies(IMDB)
251 );
252
253
254
255 CREATE TABLE Cinema (
256 cinema_name varchar(16) not NULL UNIQUE,
257 adress varchar(32),
258 capacity int,
259 PRIMARY KEY (cinema_name)
260 );
261
262
263 CREATE TABLE Movies (
264 title varchar(30) not NULL,
265 year int not NULL,
266 IMDB varchar(20) not NULL,
267 length_min varchar(3) not NULL,
268 PRIMARY KEY (IMDB)
269 );
270
271
272
273 """
274 )
275
276 conn.commit()
277 c.executescript(
278 """
279 INSERT INTO User (ssn, first_name, last_name)
280 VALUES
281 ('930804-4122', 'Alice', 'Persson'),
282 ('931004-4123', 'Bob', 'Åke');
283
284 INSERT INTO Account (ssn,username, password)
285 VALUES
286 ('930804-4122', 'alice', 'dobido'),
287 ('931004-4123', 'bob', 'whatsinaname');
288
289
290
291
292 INSERT INTO Cinema(cinema_name, adress, capacity)
293 VALUES
294 ('Kino', 'Hötorget 3, 103 91 Stockholm', '10'),
295 ('Södran', 'Kungsgatan 16, 111 35 Stockholm', '16'),
296 ('Skandia', 'Drottninggatan 82, 111 36 Stockholm', '100');
297
298
299
300 INSERT INTO Movies(title, year, IMDB, length_min)
301 VALUES
302 ('The Shape of Water', '2017', 'tt5580390', '144'),
303 ('Moonlight', '2016', 'tt4975722', '175'),
304 ('Spotlight', '2015', 'tt0468569', '150'),
305 ('Birdman', '2014', 'tt2562232', '150');
306 """
307 )
308 conn.commit()
309 return "OK"
310
311
312run(host=HOST, port=PORT, debug=True)