|
2
|
1 import sqlite3
|
|
5
|
2 from collections import OrderedDict
|
|
|
3
|
|
|
4 class NoTableException(Exception):
|
|
|
5 def __init__(self, table):
|
|
|
6 Exception.__init__(self, "Table '{}' does not exist".format(table))
|
|
|
7
|
|
2
|
8
|
|
|
9 class SQLEx(object):
|
|
|
10 """
|
|
|
11 sqlite model
|
|
|
12 https://docs.python.org/2/library/sqlite3.html
|
|
|
13 """
|
|
|
14
|
|
|
15 def __init__(self, db):
|
|
|
16 self.db = db
|
|
|
17 self.conn = sqlite3.connect(self.db)
|
|
|
18
|
|
|
19 def __call__(self, sql, *args):
|
|
3
|
20 c = self.conn.cursor()
|
|
2
|
21 c.execute(sql, args)
|
|
3
|
22 self.conn.commit()
|
|
|
23 try:
|
|
|
24 return c.fetchall()
|
|
|
25 except Exception as e:
|
|
|
26 raise e
|
|
2
|
27
|
|
|
28 def __del__(self):
|
|
|
29 self.conn.close()
|
|
|
30
|
|
|
31 def tables(self):
|
|
|
32 """
|
|
|
33 returns table names in database
|
|
|
34 """
|
|
|
35 # http://stackoverflow.com/questions/82875/how-to-list-the-tables-in-an-sqlite-database-file-that-was-opened-with-attach
|
|
|
36
|
|
3
|
37 sql = "SELECT name FROM sqlite_master WHERE type='table';"
|
|
2
|
38 tables = self(sql)
|
|
3
|
39 return sum([list(item) for item in tables], [])
|
|
5
|
40
|
|
|
41 def ensure_table(self, table):
|
|
|
42 """
|
|
|
43 ensure that `table` exists;
|
|
|
44 if not, raise 1NoTableException
|
|
|
45 """
|
|
|
46
|
|
|
47 if table not in self.tables():
|
|
|
48 raise NoTableException(table)
|
|
|
49
|
|
|
50 def columns(self, table):
|
|
|
51 """
|
|
|
52 returns columns for `table`
|
|
|
53 """
|
|
|
54
|
|
|
55 self.ensure_table(table)
|
|
|
56
|
|
|
57 sql = "PRAGMA table_info({})".format(table)
|
|
|
58 data = self(sql)
|
|
|
59 # (Pdb) pp(columns)
|
|
|
60 # [(0, u'ROWID', u'INTEGER', 0, None, 1),
|
|
|
61 # (1, u'address', u'TEXT', 0, None, 0),
|
|
|
62 # (2, u'date', u'INTEGER', 0, None, 0),
|
|
|
63 NAME_INDEX = 1
|
|
|
64 TYPE_INDEX = 2
|
|
|
65 return OrderedDict([(row[NAME_INDEX], row[TYPE_INDEX])
|
|
|
66 for row in data])
|
|
|
67
|
