upstream/tools/query_tabular/query_db.py
1#!/usr/bin/env python
2
3from __future__ import print_function
4
5import math
6import re
7import sqlite3 as sqlite
8import sys
9
10
11TABLE_QUERY = \
12 """
13 SELECT name, sql
14 FROM sqlite_master
15 WHERE type='table'
16 ORDER BY name
17 """
18
19
20def msg(e):
21 print(e, file=sys.stderr)
22
23
24def regex_match(expr, item):
25 return re.match(expr, item) is not None
26
27
28def regex_search(expr, item):
29 return re.search(expr, item) is not None
30
31
32def regex_sub(expr, replace, item):
33 return re.sub(expr, replace, item)
34
35
36def math_acos(x):
37 try:
38 return math.acos(x)
39 except ValueError as ve:
40 msg('acos(%s): %s' % (x, ve))
41 return None
42
43
44def math_acosh(x):
45 try:
46 return math.acosh(x)
47 except ValueError as ve:
48 msg(f'acosh({x}): {ve}')
49 return None
50
51
52def math_asin(x):
53 try:
54 return math.asin(x)
55 except ValueError as ve:
56 msg(f'asin({x}): {ve}')
57 return None
58
59
60def math_asinh(x):
61 try:
62 return math.asinh(x)
63 except ValueError as ve:
64 msg(f'asinh({x}): {ve}')
65 return None
66
67
68def math_atan(x):
69 try:
70 return math.atan(x)
71 except ValueError as ve:
72 msg(f'atan({x}): {ve}')
73 return None
74
75
76def math_atanh(x):
77 try:
78 return math.atanh(x)
79 except ValueError as ve:
80 msg(f'atanh({x}): {ve}')
81 return None
82
83
84def math_atan2(x, y):
85 try:
86 return math.atan2(x, y)
87 except ValueError as ve:
88 msg(f'atan2({x}, {y}): {ve}')
89 return None
90
91
92def math_ceil(x):
93 try:
94 return math.ceil(x)
95 except ValueError as ve:
96 msg(f'ceil({x}): {ve}')
97 return None
98
99
100def math_cos(x):
101 try:
102 return math.cos(x)
103 except ValueError as ve:
104 msg(f'cos({x}): {ve}')
105 return None
106
107
108def math_cosh(x):
109 try:
110 return math.cosh(x)
111 except ValueError as ve:
112 msg(f'cosh({x}): {ve}')
113 return None
114
115
116def math_degrees(x):
117 try:
118 return math.degrees(x)
119 except ValueError as ve:
120 msg(f'degrees({x}): {ve}')
121 return None
122
123
124def math_exp(x):
125 try:
126 return math.exp(x)
127 except ValueError as ve:
128 msg(f'exp({x}): {ve}')
129 return None
130
131
132def math_expm1(x):
133 try:
134 return math.expm1(x)
135 except ValueError as ve:
136 msg(f'expm1({x}): {ve}')
137 return None
138
139
140def math_fabs(x):
141 try:
142 return math.fabs(x)
143 except ValueError as ve:
144 msg(f'fabs({x}): {ve}')
145 return None
146
147
148def math_floor(x):
149 try:
150 return math.floor(x)
151 except ValueError as ve:
152 msg(f'floor({x}): {ve}')
153 return None
154
155
156def math_fmod(x, y):
157 try:
158 return math.fmod(x, y)
159 except ValueError as ve:
160 msg(f'fmod({x}, {y}): {ve}')
161 return None
162
163
164def math_blog(b, x):
165 try:
166 return math.log(b, x)
167 except ValueError as ve:
168 msg(f'log({b}, {x}): {ve}')
169 return None
170
171
172def math_log(x):
173 try:
174 return math.log(x)
175 except ValueError as ve:
176 msg(f'log({x}): {ve}')
177 return None
178
179
180def math_log10(x):
181 try:
182 return math.log10(x)
183 except ValueError as ve:
184 msg(f'log10({x}): {ve}')
185 return None
186
187
188def math_log1p(x):
189 try:
190 return math.log1p(x)
191 except ValueError as ve:
192 msg(f'log1p({x}): {ve}')
193 return None
194
195
196def math_log2(x):
197 try:
198 return math.log2(x)
199 except ValueError as ve:
200 msg(f'log2({x}): {ve}')
201 return None
202
203
204def math_mod(x, y):
205 try:
206 return x % y
207 except ValueError as ve:
208 msg(f'mod({x}, {y}): {ve}')
209 return None
210
211
212def math_pow(x, y):
213 try:
214 return math.pow(x, y)
215 except ValueError as ve:
216 msg(f'pow({x}, {y}): {ve}')
217 return None
218
219
220def math_radians(x):
221 try:
222 return math.radians(x)
223 except ValueError as ve:
224 msg(f'radians({x}): {ve}')
225 return None
226
227
228def math_sin(x):
229 try:
230 return math.sin(x)
231 except ValueError as ve:
232 msg(f'sin({x}): {ve}')
233 return None
234
235
236def math_sinh(x):
237 try:
238 return math.sinh(x)
239 except ValueError as ve:
240 msg(f'sinh({x}): {ve}')
241 return None
242
243
244def math_sqrt(x):
245 try:
246 return math.sqrt(x)
247 except ValueError as ve:
248 msg(f'sqrt({x}): {ve}')
249 return None
250
251
252def math_tan(x):
253 try:
254 return math.tan(x)
255 except ValueError as ve:
256 msg(f'tan({x}): {ve}')
257 return None
258
259
260def math_tanh(x):
261 try:
262 return math.tanh(x)
263 except ValueError as ve:
264 msg(f'tanh({x}): {ve}')
265 return None
266
267
268def math_trunc(x):
269 try:
270 return math.trunc(x)
271 except ValueError as ve:
272 msg(f'trunc({x}): {ve}')
273 return None
274
275
276def get_connection(sqlitedb_path, addfunctions=True):
277 sqlite.enable_callback_tracebacks(addfunctions)
278 conn = sqlite.connect(sqlitedb_path)
279 if addfunctions:
280 conn.create_function("re_match", 2, regex_match)
281 conn.create_function("re_search", 2, regex_search)
282 conn.create_function("re_sub", 3, regex_sub)
283 conn.create_function("acos", 1, math_acos)
284 conn.create_function("acosh", 1, math_acosh)
285 conn.create_function("asin", 1, math_asin)
286 conn.create_function("asinh", 1, math_asinh)
287 conn.create_function("atan", 1, math_atan)
288 conn.create_function("atanh", 1, math_atanh)
289 conn.create_function("atan2", 2, math_atan2)
290 conn.create_function("ceil", 1, math_ceil)
291 conn.create_function("cos", 1, math_cos)
292 conn.create_function("cosh", 1, math_cosh)
293 conn.create_function("degrees", 1, math_degrees)
294 conn.create_function("exp", 1, math_exp)
295 conn.create_function("expm1", 1, math_expm1)
296 conn.create_function("fabs", 1, math_fabs)
297 conn.create_function("floor", 1, math_floor)
298 conn.create_function("fmod", 2, math_fmod)
299 conn.create_function("log", 1, math_log)
300 conn.create_function("log", 2, math_blog)
301 conn.create_function("log10", 1, math_log10)
302 conn.create_function("log2", 1, math_log2)
303 conn.create_function("log1p", 1, math_log1p)
304 conn.create_function("mod", 2, math_mod)
305 conn.create_function("pow", 2, math_pow)
306 conn.create_function("radians", 1, math_radians)
307 conn.create_function("sin", 1, math_sin)
308 conn.create_function("sinh", 1, math_sinh)
309 conn.create_function("sqrt", 1, math_sqrt)
310 conn.create_function("tan", 1, math_tan)
311 conn.create_function("tanh", 1, math_tanh)
312 conn.create_function("trunc", 1, math_trunc)
313 return conn
314
315
316def describe_tables(conn, outputFile):
317 try:
318 c = conn.cursor()
319 tables_query = TABLE_QUERY
320 rslt = c.execute(tables_query).fetchall()
321 for table, sql in rslt:
322 print("Table %s:" % table, file=outputFile)
323 try:
324 col_query = 'SELECT * FROM %s LIMIT 0' % table
325 cur = conn.cursor().execute(col_query)
326 cols = [col[0] for col in cur.description]
327 print(" Columns: %s" % cols, file=outputFile)
328 except Exception as exc:
329 print("Warning: %s" % exc, file=sys.stderr)
330 except Exception as e:
331 exit('describe_tables Error: %s' % (e))
332 exit(0)
333
334
335def run_query(conn, query, outputFile, no_header=False, comment_char='#'):
336 cur = conn.cursor()
337 results = cur.execute(query)
338 if outputFile is not None:
339 if not no_header:
340 outputFile.write("%s%s\n" % (comment_char, '\t'.join(
341 [str(col[0]) for col in cur.description])))
342 for i, row in enumerate(results):
343 outputFile.write("%s\n" % '\t'.join(
344 [str(val) if val is not None else '' for val in row]))
345 else:
346 conn.commit()
347 return results