annotate query_db.py @ 23:5bb148ee644a draft default tip

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