Laba3/SQLAlchemy.py at main · Gurikap/Laba3 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
from time import perf_counter
import pandas as pd
import sqlalchemy

tengine = sqlalchemy.create_engine("sqlite:///tiny.db")
tiny = tengine.connect()
bengine = sqlalchemy.create_engine("sqlite:///big.db")
big = bengine.connect()
datat = pd.read_csv(r"nyc_yellow_tiny.csv")
datat.to_sql('trips', tiny, if_exists='replace', index=False)
datab = pd.read_csv(r"nyc_yellow_big.csv")
datat.to_sql('trips', big, if_exists='replace', index=False)

first = sqlalchemy.text('''SELECT "VendorID", count(*) FROM trips GROUP BY 1;''')
second = sqlalchemy.text('''SELECT "passenger_count", avg(total_amount) FROM trips GROUP BY 1;''')
third = sqlalchemy.text('''SELECT "passenger_count", strftime('%Y', "tpep_pickup_datetime"), count(*) FROM trips GROUP BY 1, 2;''')
fourth = sqlalchemy.text('''SELECT "passenger_count", strftime('%Y', "tpep_pickup_datetime"), round("trip_distance"), count(*) FROM trips GROUP BY 1, 2, 3 ORDER BY 2, 4 desc;''')

test = 0
aver1 = 0
aver2 = 0
aver3 = 0
aver4 = 0
print("Tiny data:")
while test < 10:
    start1 = perf_counter()
    fresultt = tiny.execute(first)
    end1 = perf_counter()
    aver1 = aver1 + (end1 - start1)
    start2 = perf_counter()
    sresultt = tiny.execute(second)
    end2 = perf_counter()
    aver2 = aver2 + (end2 - start2)
    start3 = perf_counter()
    tresultt = tiny.execute(third)
    end3 = perf_counter()
    aver3 = aver3 + (end3 - start3)
    start4 = perf_counter()
    foresultt = tiny.execute(fourth)
    end4 = perf_counter()
    aver4 = aver4 + (end4 - start4)
    test += 1
print("First:")
print(aver1 / 10)
print("Second:")
print(aver2 / 10)
print("Third:")
print(aver3 / 10)
print("Fourth:")
print(aver4 / 10)

test = 0
aver1 = 0
aver2 = 0
aver3 = 0
aver4 = 0
print("Big data:")
while test < 10:
    start1 = perf_counter()
    fresultb = big.execute(first)
    end1 = perf_counter()
    aver1 = aver1 + (end1 - start1)
    start2 = perf_counter()
    sresultb = big.execute(second)
    end2 = perf_counter()
    aver2 = aver2 + (end2 - start2)
    start3 = perf_counter()
    tresultb = big.execute(third)
    end3 = perf_counter()
    aver3 = aver3 + (end3 - start3)
    start4 = perf_counter()
    foresultb = big.execute(fourth)
    end4 = perf_counter()
    aver4 = aver4 + (end4 - start4)
    test += 1
print("First:")
print(aver1 / 10)
print("Second:")
print(aver2 / 10)
print("Third:")
print(aver3 / 10)
print("Fourth:")
print(aver4 / 10)