Source code

Download source archive ↓

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