|
20
|
1 #!/usr/bin/env python
|
|
|
2
|
|
|
3 from __future__ import print_function
|
|
|
4
|
|
|
5 import re
|
|
|
6 import sqlite3 as sqlite
|
|
|
7 import sys
|
|
|
8
|
|
|
9
|
|
|
10 TABLE_QUERY = \
|
|
|
11 """
|
|
|
12 SELECT name, sql
|
|
|
13 FROM sqlite_master
|
|
|
14 WHERE type='table'
|
|
|
15 ORDER BY name
|
|
|
16 """
|
|
|
17
|
|
|
18
|
|
|
19 def regex_match(expr, item):
|
|
|
20 return re.match(expr, item) is not None
|
|
|
21
|
|
|
22
|
|
|
23 def regex_search(expr, item):
|
|
|
24 return re.search(expr, item) is not None
|
|
|
25
|
|
|
26
|
|
|
27 def regex_sub(expr, replace, item):
|
|
|
28 return re.sub(expr, replace, item)
|
|
|
29
|
|
|
30
|
|
|
31 def get_connection(sqlitedb_path, addfunctions=False):
|
|
|
32 conn = sqlite.connect(sqlitedb_path)
|
|
|
33 if addfunctions:
|
|
|
34 conn.create_function("re_match", 2, regex_match)
|
|
|
35 conn.create_function("re_search", 2, regex_search)
|
|
|
36 conn.create_function("re_sub", 3, regex_sub)
|
|
|
37 return conn
|
|
|
38
|
|
|
39
|
|
|
40 def describe_tables(conn, outputFile):
|
|
|
41 try:
|
|
|
42 c = conn.cursor()
|
|
|
43 tables_query = TABLE_QUERY
|
|
|
44 rslt = c.execute(tables_query).fetchall()
|
|
|
45 for table, sql in rslt:
|
|
|
46 print("Table %s:" % table, file=sys.stderr)
|
|
|
47 try:
|
|
|
48 col_query = 'SELECT * FROM %s LIMIT 0' % table
|
|
|
49 cur = conn.cursor().execute(col_query)
|
|
|
50 cols = [col[0] for col in cur.description]
|
|
|
51 print(" Columns: %s" % cols, file=sys.stderr)
|
|
|
52 except Exception as exc:
|
|
|
53 print("Error: %s" % exc, file=sys.stderr)
|
|
|
54 except Exception as exc:
|
|
|
55 print("Error: %s" % exc, file=sys.stderr)
|
|
|
56 exit(0)
|
|
|
57
|
|
|
58
|
|
|
59 def run_query(conn, query, outputFile, no_header=False):
|
|
|
60 cur = conn.cursor()
|
|
|
61 results = cur.execute(query)
|
|
|
62 if not no_header:
|
|
|
63 outputFile.write("#%s\n" % '\t'.join(
|
|
|
64 [str(col[0]) for col in cur.description]))
|
|
|
65 # yield [col[0] for col in cur.description]
|
|
|
66 for i, row in enumerate(results):
|
|
|
67 # yield [val for val in row]
|
|
|
68 outputFile.write("%s\n" % '\t'.join(
|
|
|
69 [str(val) if val is not None else '' for val in row]))
|
