Mercurial > hg > Lemuriformes

annotate lemuriformes/sql.py @ 15:0d1b8bb1d97b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQL + data related functionality
author Jeff Hammel <k0scist@gmail.com>
date Sun, 10 Dec 2017 17:16:52 -0800
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
15
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
1 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
2 abstract SQL functionality
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
3 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
4
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
5
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
6 from abc import abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
7
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
8
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
9 class SQLConnection(object):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
10 """abstract base class for SQL connection"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
11
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
12 placeholder = '?' # VALUE placeholder
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
13
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
14 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
15 def __call__(self, sql, *args, **kwargs):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
16 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
17 execute `sql` against connection cursor with values in `args`;
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
18 `kwargs` should be passed to the connection
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
19 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
20
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
21 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
22 def tables(self):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
23 """return list of tables in the database"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
24
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
25 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
26 def columns(self, table):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
27 """return the columns in `table`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
28
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
29 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
30 def create_table(self, table, **columns):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
31 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
32 add a table to the database for the specific SQL type
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
33 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
34
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
35 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
36 def pytype2sql(self, pytype):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
37 """return the SQL type for the python type `pytype`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
38
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
39 @abstractmethod
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
40 def sqltype2py(self, sqltype):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
41 """return the python type for the SQL type `sqltype`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
42
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
43 def drop(self, table):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
44 """drop `table` if exists"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
45
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
46 if table in self.tables():
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
47 return # nothing to do
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
48
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
49 sql = "DROP TABLE {table}"
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
50 self(sql.format(table=table))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
51
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
52 def placeholders(self, number):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
53 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
54 return placeholder string appropriate to INSERT SQL;
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
55 `number` should be an integer or an iterable with a `len`
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
56 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
57
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
58 try:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
59 number = len(number) # iterable
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
60 except TypeError:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
61 pass # assume integer
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
62
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
63 return ','.join([self.placeholder for placeholder in range(number)])
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
64
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
65 def insert_sql(self, table, columns):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
66 """return insert SQL statement"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
67 sql = "INSERT INTO `{table}` ({columns}) VALUES ({values})"
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
68 column_str = ', '.join(["`{}`".format(key) for key in columns])
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
69 return sql.format(table=table,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
70 columns=column_str,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
71 values=self.placeholders(columns))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
72
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
73 def insert_row(self, table, **row):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
74 """insert one `row` into `table`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
75
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
76 columns = row.keys()
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
77 sql = "INSERT INTO {table} ({columns}) VALUES ({placeholders})".format(table=table,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
78 placeholders=self.placeholders(columns),
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
79 columns=', '.join(columns))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
80 values = tuple([row[column] for column in columns])
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
81 self(sql, *values)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
82
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
83 def count(self, table):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
84 """count + return number of rows in `table`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
85 # https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
86
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
87 sql = "select count(*) from {table}".format(table=table)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
88 data = self(sql)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
89 assert len(data) == 1
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
90 if len(data[0]) != 1:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
91 raise AssertionError
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
92 return data[0][0]