· 5 years ago · Dec 04, 2020, 02:38 PM
1# Assignment 3 - Basi di dati
2
3# IMPORT
4import psycopg2
5from psycopg2.extras import execute_values
6from datetime import datetime
7from random import random, randint, choice, choices
8from string import ascii_letters, digits
9from sys import stderr
10
11###############################################################################
12
13# DATABASE LOCALE
14DB_HOST = "127.0.0.1"
15DB_NAME = "postgres"
16DB_USER = "postgres"
17DB_PASSWORD = "postgres"
18DB_PORT = "5432"
19
20###############################################################################
21
22# VARIABILI AUSILIARIE
23
24# Lista contenente i nomi
25nameList = [
26 "FirstForename", "Jack", "Lewis", "James", "Logan",
27 "Daniel", "Ryan", "Aaron", "Oliver", "Liam",
28 "Jamie", "Ethan", "Alexander", "Cameron", "Finlay",
29 "Kyle", "Adam", "Harry", "Matthew", "Callum",
30 "Lucas", "Nathan", "Aiden", "Dylan", "Charlie",
31 "Connor", "Thomas", "Alfie", "Joshua", "William",
32 "Jayden", "Andrew", "Kai", "Max", "Ben",
33 "Samuel", "Luke", "Tyler", "Rory", "David",
34 "Michael", "Riley", "Noah", "Cole", "Joseph",
35 "John", "Archie", "Jacob", "Fraser", "Rhys",
36 "Ross", "Calum", "Jay", "Josh", "Euan",
37 "Mason", "Owen", "Sam", "Leo", "Robert",
38 "Scott", "Leon", "Robbie", "Benjamin", "Caleb",
39 "Oscar", "Harris", "Murray", "Sean", "Christopher",
40 "Kieran", "Aidan", "Jake", "Evan", "Kayden",
41 "Arran", "Angus", "Brodie", "Ewan", "Muhammad",
42 "Alex", "Declan", "Finn", "Blair", "Ollie",
43 "Reece", "Corey", "Kian", "Harrison", "Taylor",
44 "Kaiden", "Kenzie", "Cody", "Craig", "Mohammed",
45 "Calvin", "Mark", "Jude", "Luca", "Ciaran"
46 ]
47
48# Lista contenente gli indirizzi
49addressList = [
50 "Via Francobollo, 45",
51 "Via G. Pascoli, 75/B",
52 "Via XX Settembre, 12",
53 "Via G. Marchesoni, 43",
54 "Via Canestrini, 7/A",
55 "Via Italia, 11",
56 "Via Mantova, 36",
57 "Via Brescia, 87",
58 "Via Milano, 33",
59 "Via Roma, 91",
60 "Via Cesare Battisti, 39"
61 ]
62
63###############################################################################
64
65# Creo la connessione al database
66connection = None
67try:
68 connection = psycopg2.connect(
69 host=DB_HOST,
70 port=DB_PORT,
71 database=DB_NAME,
72 user=DB_USER,
73 password=DB_PASSWORD
74 )
75except (Exception, psycopg2.Error) as error:
76 print("Impossibile connettersi al database.", error)
77
78cursor = connection.cursor()
79
80###############################################################################
81
82# FUNZIONI AUSILIARIE
83
84# decorator per stampare a schermo il tempo impiegato per eseguire uno step
85def printStepExecutionTime(stepNumber: str):
86 def wrapper(f):
87 def inner(*args, **kwargs):
88 start = datetime.now()
89
90 f(*args, **kwargs)
91
92 end = datetime.now()
93 timeDiff = end - start
94
95 nanoseconds = timeDiff.microseconds / 10**3 + timeDiff.seconds * 10**9
96
97 print("Step " + stepNumber + " needs " + str(round(nanoseconds)) + " ns")
98 return inner
99 return wrapper
100
101# funzione che genera una stringa arbitraria di N caratteri
102def generateRandomString(length=25):
103 #return "".join(choice(ascii_letters + digits) for i in range(length))
104 return ''.join(choices(ascii_letters + digits, k=length))
105
106###############################################################################
107
108# FUNZIONI CHE IMPLEMENTANO GLI STEP
109
110# Fa il drop delle due tabelle dalla base di dati se sono già presenti.
111@printStepExecutionTime("1")
112def step1():
113 query = 'DROP TABLE IF EXISTS "Boat";'
114 cursor.execute(query)
115
116 query = 'DROP TABLE IF EXISTS "Sailor";'
117 cursor.execute(query)
118
119 connection.commit()
120
121# Crea le due tabelle come descritte nell'assignment
122@printStepExecutionTime("2")
123def step2():
124 query = """CREATE TABLE "Sailor" (id integer PRIMARY KEY,
125 name character(50) NOT NULL,
126 address character(50) NOT NULL,
127 age integer NOT NULL,
128 level float NOT NULL);"""
129 cursor.execute(query)
130
131 query = """CREATE TABLE "Boat" (bid character(25) PRIMARY KEY,
132 bname character(50) NOT NULL,
133 size character(30) NOT NULL,
134 captain integer NOT NULL,
135 FOREIGN KEY (captain) REFERENCES "Sailor"(id)
136 );"""
137 cursor.execute(query)
138
139 connection.commit()
140
141# Genera 1 milione di tuple casuali 1, in modo tale che ogni tupla abbia un
142# valore diverso per l’attributo level, e le inserisce nella tabella Sailor.
143# Assicurarsi inoltre che l’ultima tupla inserita, e solo quella, abbia come
144# valore dell’attributo level, il valore 185.
145@printStepExecutionTime("3")
146def step3():
147 # genero un set contenente i diversi level
148 targetSize = 1000000
149 levels = set()
150 previousSetSize = 0
151 currentSetSize = 0
152 argList = []
153
154 while currentSetSize < targetSize:
155 # genero un numero causale fino a 10 milioni
156 randomNumber = random() * 10000000
157 if randomNumber != 185.0:
158 levels.add(randomNumber)
159
160 previousSetSize = currentSetSize
161 currentSetSize = len(levels)
162
163 if currentSetSize > previousSetSize:
164 args = None
165 name = choice(nameList)
166 address = choice(addressList)
167
168 if currentSetSize == targetSize:
169 args = (currentSetSize - 1, name, address, randint(16, 65), 185)
170 else:
171 args = (currentSetSize - 1, name, address, randint(16, 65), levels.pop())
172
173 argList.append(args)
174
175 # query per inserire un nuovo Sailor
176 query = """INSERT INTO "Sailor" VALUES %s;"""
177
178 # eseguo la query su tutte le tuple generate
179 execute_values(cursor, query, argList)
180
181 # committo le query
182 connection.commit()
183
184# Genera 1 ulteriore milione di tuple (casuali) e le inserisce nella
185# tabella Boat.
186@printStepExecutionTime("4")
187def step4():
188 # genero un set contenente i diversi level
189 targetSize = 1000000
190 bids = set()
191 previousSetSize = 0
192 currentSetSize = 0
193 argList = []
194
195 while currentSetSize < targetSize:
196 bid = generateRandomString(randint(10, 25))
197 bids.add(bid)
198
199 previousSetSize = currentSetSize
200 currentSetSize = len(bids)
201
202 if currentSetSize > previousSetSize:
203 args = None
204
205 bname = generateRandomString(randint(10, 25))
206 size = generateRandomString(randint(10, 25))
207 captain = randint(0, 999999)
208
209 args = (bids.pop(), bname, size, captain)
210
211 argList.append(args)
212
213 # query per inserire un nuovo Sailor
214 query = """INSERT INTO "Boat" VALUES %s;"""
215
216 # eseguo la query su tutte le tuple generate
217 execute_values(cursor, query, argList)
218
219 # committo le query
220 connection.commit()
221
222# Ottiene dal database tutti gli id del milione di tuple della tabella Sailor
223# e li stampa su stderr.
224@printStepExecutionTime("5")
225def step5():
226 # ricavo gli id dalla tabella Sailor
227 query = """SELECT S.id FROM "Sailor" S;"""
228
229 # eseguo la query
230 cursor.execute(query)
231
232 # ricavo tutti i risultati tramite fetchall
233 ids = cursor.fetchall()
234
235 # TODO: Chiedere in che formato stamparli
236 # stampo gli id su stderr
237 print(ids, file=stderr)
238
239# Tutte le tuple con valore di level pari a 185 vengono modificate, cambiando
240# il valore di level a 200 (la vostra query dovrà funzionare anche se la base
241# di dati contiene più di una tupla con valore di level pari a 185).
242@printStepExecutionTime("6")
243def step6():
244 # query per aggiornare tutti i record con level uguale a 185
245 query = """UPDATE "Sailor" SET level = 200 WHERE level = 185;"""
246
247 # eseguo la query
248 cursor.execute(query)
249
250 # effettuo il commit
251 connection.commit()
252
253# Seleziona l’id e l’address di tutte le tuple della tabella Sailor che hanno
254# valore di level pari a 200, e li stampa su stderr
255@printStepExecutionTime("7")
256def step7():
257 # query per ricavare id e address dalla tabella Sailor con level uguale
258 # a 200
259 query = """SELECT S.id, S.address FROM "Sailor" S WHERE level = 200;"""
260
261 # eseguo la query
262 cursor.execute(query)
263
264 # ricavo i risultati tramite fetchall
265 data = cursor.fetchall()
266
267 # stampo gli id e l'address su stderr
268 print(data, file=stderr)
269
270# Crea un indice B+tree sull’attributo level.
271@printStepExecutionTime("8")
272def step8():
273 # query per creare il B+ tree su level
274 query = """CREATE INDEX LevelIndex ON "Sailor" (level);"""
275
276 # eseguo la query
277 cursor.execute(query)
278
279 # effettuo il commit
280 connection.commit()
281
282# Ottiene dal database tutti gli id del milione di tuple della tabella Sailor
283# e li stampa su stderr.
284# è uguale allo step 5
285@printStepExecutionTime("9")
286def step9():
287 # ricavo gli id dalla tabella Sailor
288 query = """SELECT S.id FROM "Sailor" S;"""
289
290 # eseguo la query
291 cursor.execute(query)
292
293 # ricavo tutti i risultati tramite fetchall
294 ids = cursor.fetchall()
295
296 # TODO: Chiedere in che formato stamparli
297 # stampo gli id su stderr
298 print(ids, file=stderr)
299
300# Tutte le tuple con valore di level pari a 200 vengono modificate, cambiando
301# il valore di level a 210 (la vostra query dovrà funzionare anche se la base
302# di dati contiene più di una tupla con valore di level pari a 200)
303@printStepExecutionTime("10")
304def step10():
305 # query per aggiornare tutti i record con level uguale a 200
306 query = """UPDATE "Sailor" SET level = 210 WHERE level = 200;"""
307
308 # eseguo la query
309 cursor.execute(query)
310
311 # effettuo il commit
312 connection.commit()
313
314# Seleziona l’id e l’address di tutte le tuple della tabella Sailor che hanno
315# valore di level pari a 210, e li stampa su stderr.
316@printStepExecutionTime("11")
317def step11():
318 # query per ricavare id e address dalla tabella Sailor con level uguale
319 # a 210
320 query = """SELECT S.id, S.address FROM "Sailor" S WHERE level = 210;"""
321
322 # eseguo la query
323 cursor.execute(query)
324
325 # ricavo i risultati tramite fetchall
326 data = cursor.fetchall()
327
328 # stampo gli id e l'address su stderr
329 print(data, file=stderr)
330
331###############################################################################
332
333# TODO: Togliere prima di consegnare
334start = datetime.now()
335
336# ESEGUO L'ASSIGNMENT
337step1()
338step2()
339step3()
340step4()
341step5()
342step6()
343step7()
344step8()
345step9()
346step10()
347step11()
348
349# TODO: Togliere prima della consegna
350end = datetime.now()
351print((end - start).seconds)
352
353###############################################################################
354
355# CHIUDO LA CONNESSIONE AL DATABASE
356if connection != None:
357 # TOGLIERE IL COMMENTO
358 cursor.close()
359 connection.close()