DataAnalyzerService/dbmanipulation.py at master · tynorantoni/DataAnalyzerService · 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
84
85
86
87
88
89
90
91
92
93
94
95
96
import psycopg2
from dbconnector import connect_to_db


# return dataset from DB from Krakow or Brussels
def select_data_from_city(city, start_date, end_date):
    try:
        conn = connect_to_db()
        cur = conn.cursor()
        if city == 'krk':
            cur.execute(
                '''SELECT * FROM krakow_data WHERE (date_of_count >= '{}' AND date_of_count <= '{}');'''.format(
                    start_date, end_date
                ))
            conn.commit()
            desired_data = cur.fetchall()
        elif city == "br":
            cur.execute(
                '''SELECT * FROM brussels_data WHERE (date_of_count >= '{}' AND date_of_count <= '{}');'''.format(
                    start_date, end_date
                ))

            conn.commit()
            desired_data = cur.fetchall()
        return desired_data
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        return error

    finally:
        cur.close()
        conn.close()


# return all data from selected city (Krakow or Brussels)
def select_all_data_from_city(city):
    try:
        conn = connect_to_db()
        cur = conn.cursor()
        if city == 'krk':
            cur.execute(
                '''SELECT * FROM krakow_data;''')
            conn.commit()
            desired_data = cur.fetchall()
        elif city == "br":
            cur.execute(
                '''SELECT * FROM brussels_data;''')

            conn.commit()
            desired_data = cur.fetchall()
        return desired_data
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        return error

    finally:
        cur.close()
        conn.close()

def select_last_3_hours_from_weather():
    try:
        conn = connect_to_db()
        cur = conn.cursor()

        cur.execute(
            '''SELECT * FROM weather_data ORDER BY id DESC LIMIT 3;''')
        conn.commit()
        desired_data = cur.fetchall()

        return desired_data
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        return error

    finally:
        cur.close()
        conn.close()

def select_last_health_status():
    try:
        conn = connect_to_db()
        cur = conn.cursor()

        cur.execute(
            '''SELECT * FROM ping_data ORDER BY id DESC LIMIT 1;''')
        conn.commit()
        desired_data = cur.fetchall()

        return desired_data
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        return error

    finally:
        cur.close()
        conn.close()