datasette_pytables/__init__.py

   1 import tables
   2 import datasette_connectors as dc
   3 from .utils import parse_sql
   4
   5
   6 class PyTablesConnection(dc.Connection):
   7     def __init__(self, path, connector):
   8         super().__init__(path, connector)
   9         self.h5file = tables.open_file(path)
  10
  11
  12 class PyTablesConnector(dc.Connector):
  13     connector_type = 'pytables'
  14     connection_class = PyTablesConnection
  15
  16     operators = {
  17         'eq': '==',
  18         'neq': '!=',
  19         'gt': '>',
  20         'gte': '>=',
  21         'lt': '<',
  22         'lte': '<=',
  23         'and': '&',
  24         'or': '|',
  25     }
  26
  27     def table_names(self):
  28         return [
  29             node._v_pathname
  30             for node in self.conn.h5file
  31             if not(isinstance(node, tables.group.Group))
  32         ]
  33
  34     def table_count(self, table_name):
  35         table = self.conn.h5file.get_node(table_name)
  36         return int(table.nrows)
  37
  38     def table_info(self, table_name):
  39         table = self.conn.h5file.get_node(table_name)
  40         colnames = ['value']
  41         if isinstance(table, tables.table.Table):
  42             colnames = table.colnames
  43
  44         return [
  45             {
  46                 'idx': idx,
  47                 'name': colname,
  48                 'primary_key': False,
  49             }
  50             for idx, colname in enumerate(colnames)
  51         ]
  52
  53     def hidden_table_names(self):
  54         return []
  55
  56     def detect_spatialite(self):
  57         return False
  58
  59     def view_names(self):
  60         return []
  61
  62     def detect_fts(self, table_name):
  63         return False
  64
  65     def foreign_keys(self, table_name):
  66         return []
  67
  68     def execute(
  69         self,
  70         sql,
  71         params=None,
  72         truncate=False,
  73         custom_time_limit=None,
  74         page_size=None,
  75         log_sql_errors=True,
  76     ):
  77         results = []
  78         truncated = False
  79         description = ()
  80
  81         parsed_sql = parse_sql(sql, params)
  82
  83         table = self.conn.h5file.get_node(parsed_sql['from'])
  84         table_rows = []
  85         fields = parsed_sql['select']
  86         colnames = ['value']
  87         if type(table) is tables.table.Table:
  88             colnames = table.colnames
  89
  90         query = ''
  91         start = 0
  92         end = table.nrows
  93
  94         # Use 'where' statement or get all the rows
  95         def _cast_param(field, pname):
  96             # Cast value to the column type
  97             coltype = table.dtype.name
  98             if type(table) is tables.table.Table:
  99                 coltype = table.coltypes[field]
 100             fcast = None
 101             if coltype == 'string':
 102                 fcast = str
 103             elif coltype.startswith('int'):
 104                 fcast = int
 105             elif coltype.startswith('float'):
 106                 fcast = float
 107             if fcast:
 108                 params[pname] = fcast(params[pname])
 109
 110         def _translate_where(where):
 111             # Translate SQL to PyTables expression
 112             nonlocal start, end
 113             expr = ''
 114             operator = list(where)[0]
 115
 116             if operator in ['and', 'or']:
 117                 subexpr = [_translate_where(e) for e in where[operator]]
 118                 subexpr = filter(lambda e: e, subexpr)
 119                 subexpr = ["({})".format(e) for e in subexpr]
 120                 expr = " {} ".format(self.operators[operator]).join(subexpr)
 121             elif operator == 'exists':
 122                 pass
 123             elif where == {'eq': ['rowid', 'p0']}:
 124                 start = int(params['p0'])
 125                 end = start + 1
 126             elif where == {'gt': ['rowid', 'p0']}:
 127                 start = int(params['p0']) + 1
 128             else:
 129                 left, right = where[operator]
 130                 if left in params:
 131                     _cast_param(right, left)
 132                 elif right in params:
 133                     _cast_param(left, right)
 134
 135                 expr = "{left} {operator} {right}".format(left=left, operator=self.operators.get(operator, operator), right=right)
 136
 137             return expr
 138
 139         if 'where' in parsed_sql:
 140             if type(parsed_sql['where']) is dict:
 141                 query = _translate_where(parsed_sql['where'])
 142             else:
 143                 query = parsed_sql['where']
 144
 145         # Sort by column
 146         orderby = ''
 147         if 'orderby' in parsed_sql:
 148             orderby = parsed_sql['orderby']
 149             if type(orderby) is list:
 150                 orderby = orderby[0]
 151             orderby = orderby['value']
 152             if orderby == 'rowid':
 153                 orderby = ''
 154
 155         # Limit number of rows
 156         limit = None
 157         if 'limit' in parsed_sql:
 158             limit = int(parsed_sql['limit'])
 159
 160         # Truncate if needed
 161         if page_size and max_returned_rows and truncate:
 162             if max_returned_rows == page_size:
 163                 max_returned_rows += 1
 164
 165         # Execute query
 166         if query:
 167             table_rows = table.where(query, params, start, end)
 168         elif orderby:
 169             table_rows = table.itersorted(orderby, start=start, stop=end)
 170         else:
 171             table_rows = table.iterrows(start, end)
 172
 173         # Prepare rows
 174         def normalize_field_value(value):
 175             if type(value) is bytes:
 176                 return value.decode('utf-8')
 177             elif not type(value) in (int, float, complex):
 178                 return str(value)
 179             else:
 180                 return value
 181
 182         def make_get_rowid():
 183             if type(table) is tables.table.Table:
 184                 def get_rowid(row):
 185                     return int(row.nrow)
 186             else:
 187                 rowid = start - 1
 188                 def get_rowid(row):
 189                     nonlocal rowid
 190                     rowid += 1
 191                     return rowid
 192             return get_rowid
 193
 194         def make_get_row_value():
 195             if type(table) is tables.table.Table:
 196                 def get_row_value(row, field):
 197                     return row[field]
 198             else:
 199                 def get_row_value(row, field):
 200                     return row
 201             return get_row_value
 202
 203         if len(fields) == 1 and type(fields[0]['value']) is dict and \
 204            fields[0]['value'].get('count') == '*':
 205             results.append({'count(*)': int(table.nrows)})
 206         else:
 207             get_rowid = make_get_rowid()
 208             get_row_value = make_get_row_value()
 209             count = 0
 210             for table_row in table_rows:
 211                 count += 1
 212                 if limit and count > limit:
 213                     break
 214                 if truncate and max_returned_rows and count > max_returned_rows:
 215                     truncated = True
 216                     break
 217                 row = {}
 218                 for field in fields:
 219                     field_name = field['value']
 220                     if type(field_name) is dict and 'distinct' in field_name:
 221                         field_name = field_name['distinct']
 222                     if field_name == 'rowid':
 223                         row['rowid'] = get_rowid(table_row)
 224                     elif field_name == '*':
 225                         for col in colnames:
 226                             row[col] = normalize_field_value(get_row_value(table_row, col))
 227                     else:
 228                         row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
 229                 results.append(row)
 230
 231         # Prepare query description
 232         for field in [f['value'] for f in fields]:
 233             if field == '*':
 234                 for col in colnames:
 235                     description += ((col,),)
 236             else:
 237                 description += ((field,),)
 238
 239         return results, truncated, description