/
sqlite_dal.py
126 lines (103 loc) · 4.5 KB
/
sqlite_dal.py
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
# -*- coding: utf-8 -*-
"""The sqlite-based DAL writes data to a single-file SQL database
powered by Python's built-in sqlite3 module. The schema is based on a
prototype message found in dal/common.py.
The advantages of the SQLite DAL are:
* Faster loading - SQLite can efficiently seek to relevant data
* Efficient storage - SQLite is a binary format, may be more compact than text
* More flexible queries - SQL is powerful and easy to use
Drawbacks:
* Not human readable - Binary format makes it harder to read/grep
* More complex - More logic = more work = more time/resources?
"""
import os
import sqlite3
import boltons
SEP = '$' # '$' is valid in sqlite column names, no escaping required
TABLE_NAME = 'on_import_data'
CREATE_TABLE = ('CREATE TABLE IF NOT EXISTS ' + TABLE_NAME +
'(id integer primary key asc, ' +
'hostfqdn, hostname, python$argv, python$bin, ' +
'python$build_date, python$compiler, python$have_readline, ' +
'python$have_ucs4, python$is_64bit, python$version, python$version_full, ' +
'time$utc_epoch real, time$std_utc_offset real, ' +
'uname$0, uname$1, uname$2, uname$3, uname$4, uname$5, ' +
'username, uuid)')
total_count = 0
sqlite3.enable_callback_tracebacks(True)
class SQLiteDAL(object):
_extension = '.db'
def __init__(self, file_path):
self.__file_path = file_path
conn = sqlite3.connect(file_path)
conn.execute(CREATE_TABLE)
def add_record(self, in_dict):
query = ('INSERT INTO ' + TABLE_NAME +
' (hostfqdn, hostname, python$argv, python$bin, ' +
'python$build_date, python$compiler, python$have_readline, ' +
'python$have_ucs4, python$is_64bit, python$version, python$version_full, ' +
'time$utc_epoch, time$std_utc_offset, ' +
'uname$0, uname$1, uname$2, uname$3, uname$4, uname$5, ' +
'username, uuid) VALUES (')
query += '"' + in_dict['hostfqdn'] + '", '
query += '"' + in_dict['hostname'] + '", '
query += '"' + in_dict['python']['argv'] + '", '
query += '"' + in_dict['python']['bin'] + '", '
query += '"' + in_dict['python']['build_date'] + '", '
query += '"' + in_dict['python']['compiler'] + '", '
query += '"' + str(in_dict['python']['have_readline'])[0] + '", '
query += '"' + str(in_dict['python']['have_ucs4'])[0] + '", '
query += '"' + str(in_dict['python']['is_64bit'])[0] + '", '
query += '"' + in_dict['python']['version'] + '", '
query += '"' + in_dict['python']['version_full'] + '", '
query += '"' + str(in_dict['time']['utc_epoch']) + '", '
query += '"' + str(in_dict['time']['std_utc_offset']) + '", '
query += '"' + in_dict['uname'][0] + '", '
query += '"' + in_dict['uname'][1] + '", '
query += '"' + in_dict['uname'][2] + '", '
query += '"' + in_dict['uname'][3] + '", '
query += '"' + in_dict['uname'][4] + '", '
query += '"' + in_dict['uname'][5] + '", '
query += '"' + in_dict['username'] + '", '
query += '"' + in_dict['uuid']
query += '")'
try:
conn = sqlite3.connect(self.__file_path)
conn.isolation_level = None # autocommit
conn.execute(query)
conn.close()
except:
pass
global total_count
total_count += 1
return
def raw_query(self, query):
try:
conn = sqlite3.connect(self.__file_path)
rows = conn.execute(query).fetchall()
return rows
except:
return -1
def select_records(self, limit=None, group_by=None):
ret = {}
if not group_by:
return ret
group_by = group_by.replace('.', '$')
query = 'SELECT ROWID, ' + group_by + ', COUNT(*) FROM ' + TABLE_NAME
query += ' GROUP BY ' + str(group_by)
if limit:
query += ' ORDER BY ROWID DESC LIMIT ' + str(limit)
try:
conn = sqlite3.connect(self.__file_path)
rows = conn.execute(query).fetchall()
except:
return
ret['counts'] = {}
for _, group_key, group_count in rows:
ret['counts'][group_key] = group_count
ret['grouped_by'] = group_by
ret['grouped_key_count'] = len(rows)
ret['record_count'] = sum(ret['counts'].values())
return ret
if __name__ == '__main__':
SQLiteDAL('test.db')