· 6 years ago · Apr 08, 2019, 10:22 AM
1class DB:
2 def __init__(self):
3 conn = sqlite3.connect(config.dbname) #Connect to db
4 conn.execute("PRAGMA foreign_keys = 1")
5 cursor = conn.cursor()
6
7 cursor.execute('''
8 CREATE TABLE IF NOT EXISTS {tname} (
9 {field_userid} INTEGER PRIMARY KEY
10 )'''.format(
11 tname = config.table_users,
12 field_userid = config.field_users_userID))
13
14 cursor.execute('''
15 CREATE TABLE IF NOT EXISTS {tname} (
16 {field_id} INTEGER PRIMARY KEY,
17 {field_direction} INTEGER,
18 {field_timestamp} INTEGER,
19 {field_type} INTEGER,
20 {field_price} INTEGER,
21 {field_note} TEXT,
22 {field_userID} INTEGER,
23 FOREIGN KEY ({field_userID}) REFERENCES {tname_users} ({field_userID})
24 )'''.format(
25 tname = config.table_entries,
26 field_id = config.field_entries_id,
27 field_direction = config.field_entries_direction,
28 field_timestamp = config.field_entries_timestamp,
29 field_userID = config.field_entries_userID,
30 field_type = config.field_entries_type,
31 field_price = config.field_entries_price,
32 field_note = config.field_entries_note,
33 tname_users = config.table_users,))
34
35 conn.commit()
36 conn.close()
37
38 def insert_user(self, id):
39 conn = sqlite3.connect(config.dbname)
40 cursor = conn.cursor()
41 cursor.execute('''
42 INSERT INTO {tname} ({user_id})
43 VALUES ('{id}')
44 '''.format(
45 tname = config.table_users,
46 user_id = config.field_users_userID,
47 id = id,)
48 )
49 conn.commit()
50 conn.close()
51
52 def get_users(self):
53 conn = sqlite3.connect(config.dbname)
54 cursor = conn.cursor()
55 sql = "SELECT * FROM {tname}".format(
56 tname = config.table_users)
57 cursor.execute(sql)
58 ids = {item[0] for item in cursor.fetchall()}
59 return ids
60 cursor.close()
61
62 def insert_entry(self, data, type):
63 conn = sqlite3.connect(config.dbname)
64 cursor = conn.cursor()
65
66 sql = '''
67 INSERT INTO {tname} ( direction, timestamp, type, price, note, userID )
68 VALUES (?, ?, ?, ?, ?, ?);'''.format(tname = config.table_entries)
69
70 cursor.execute(sql, (
71 data['direction'],
72 data['timestamp'],
73 type,
74 data['price'],
75 data['note'],
76 data['userID'],)
77 )
78 conn.commit()
79 conn.close()
80
81 def get_sum_period(self, dir, tst_from, tst_to, types, users):
82 start_time = time.time()
83 conn = sqlite3.connect(config.dbname)
84 cursor = conn.cursor()
85
86 where = 'direction == {}'.format(dir)
87
88 if tst_from > 0:
89 if where != '':
90 where += ' AND '
91 where += ' timestamp > {} '.format(tst_from)
92
93 if tst_to > 0:
94 if where != '':
95 where += ' AND '
96 where += ' timestamp < {} '.format(tst_to)
97
98 if len(types) > 0:
99 if where != '':
100 where += ' AND '
101 where += '('
102 for i, item in enumerate(types):
103 if i > 0:
104 where += ' OR '
105 where += ' type == {}'.format(item)
106 where += ')'
107
108 if len(users) > 0:
109 if where != '':
110 where += ' AND '
111 where += '('
112 for i, item in enumerate(users):
113 if i > 0:
114 where += ' OR '
115 where += ' userID == {}'.format(item)
116 where += ')'
117
118
119 sql = '''
120 SELECT price FROM {tname}
121 WHERE {where}
122 '''.format(
123 tname = config.table_entries,
124 where = where,)
125
126 print(sql)
127 cursor.execute(sql)
128 result = [item[0] for item in cursor.fetchall()]
129 conn.close()
130 end_time = time.time()-start_time
131 logging.debug('query time: {}'.format(end_time))
132 return sum(result)