data.py 2.05 KB
Newer Older
Vladislav Rykov's avatar
Vladislav Rykov committed
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
import psycopg2
from psycopg2 import sql

# decorator implementation
def with_psql(f):
    def _with_psql(*args, **kwargs):
        conn = psycopg2.connect('dbname=gateway')
        cur = conn.cursor()

        try:
            res = f(cur, *args, **kwargs)
        except (Exception, psycopg2.DatabaseError) as error:
            conn.rollback()
            res = (False, error)
        else:
            conn.commit()
        finally:
            cur.close()
            conn.close()
    
        return res
    return _with_psql


@with_psql
Vladislav Rykov's avatar
Vladislav Rykov committed
26
27
def create_table(cur, appkey, devid):
    tn = 'dev_' +str(appkey)+ '_' +str(devid)
Vladislav Rykov's avatar
Vladislav Rykov committed
28
29
30
31
32
33
34
35
36
37
38
39
    cur.execute(
        sql.SQL(
            """CREATE TABLE {} (
                utc NUMERIC(10) NOT NULL,
                timedate VARCHAR(100) NOT NULL,
                data json NOT NULL
            )"""
        ).format(sql.Identifier(tn)))
    return (True,)

    
@with_psql
Vladislav Rykov's avatar
Vladislav Rykov committed
40
41
def delete_table(cur, appkey, devid):
    tn = 'dev_' +str(appkey)+ '_' +str(devid)
Vladislav Rykov's avatar
Vladislav Rykov committed
42
43
44
45
46
47
48
49
    cur.execute(
        psycopg2.sql.SQL(
            "DROP TABLE {}"
        ).format(sql.Identifier(tn)))
    return (True,)


@with_psql
Vladislav Rykov's avatar
Vladislav Rykov committed
50
51
def get_last_n(cur, appkey, devid, n):
    tn = 'dev_' +str(appkey)+ '_' +str(devid)
Vladislav Rykov's avatar
Vladislav Rykov committed
52
53
54
55
56
57
58
    query = """
        SELECT * FROM 
            {}
        ORDER BY 
            utc DESC
        LIMIT %s
        """
Vladislav Rykov's avatar
Vladislav Rykov committed
59
60
61
    cur.execute(
        sql.SQL(query).format(sql.Identifier(tn)), [n])
    data = cur.fetchall()
Vladislav Rykov's avatar
Vladislav Rykov committed
62
        
Vladislav Rykov's avatar
Vladislav Rykov committed
63
64
65
66
    if (data == []):
        return (False, 'There is no data for the device.')
    else:
        return (True, data)
Vladislav Rykov's avatar
Vladislav Rykov committed
67
68
69
70


@with_psql
def get_all(cur, appkey, devid):
Vladislav Rykov's avatar
Vladislav Rykov committed
71
72
    tn = 'dev_' +str(appkey)+ '_' +str(devid)
    query = """
Vladislav Rykov's avatar
Vladislav Rykov committed
73
74
75
        SELECT * FROM
            {}
        """
Vladislav Rykov's avatar
Vladislav Rykov committed
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
    cur.execute(
        sql.SQL(query).format(sql.Identifier(tn)))
    return (True, cur.fetchall())

@with_psql
def get_count(cur, appkey, devid):
    tn = 'dev_' +str(appkey)+ '_' +str(devid)
    query = """
        SELECT COUNT(utc) FROM
            {}
        """
    cur.execute(
        sql.SQL(query).format(sql.Identifier(tn)))
    return (True, cur.fetchone())

Vladislav Rykov's avatar
Vladislav Rykov committed
91