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         'binary_and': '&',
  26         'binary_or': '|',
  27     }
  28
  29     def table_names(self):
  30         return [
  31             node._v_pathname
  32             for node in self.conn.h5file
  33             if not(isinstance(node, tables.group.Group))
  34         ]
  35
  36     def table_count(self, table_name):
  37         table = self.conn.h5file.get_node(table_name)
  38         return int(table.nrows)
  39
  40     def table_info(self, table_name):
  41         table = self.conn.h5file.get_node(table_name)
  42         colnames = ['value']
  43         if isinstance(table, tables.table.Table):
  44             colnames = table.colnames
  45
  46         return [
  47             {
  48                 'idx': idx,
  49                 'name': colname,
  50                 'primary_key': False,
  51             }
  52             for idx, colname in enumerate(colnames)
  53         ]
  54
  55     def hidden_table_names(self):
  56         return []
  57
  58     def detect_spatialite(self):
  59         return False
  60
  61     def view_names(self):
  62         return []
  63
  64     def detect_fts(self, table_name):
  65         return False
  66
  67     def foreign_keys(self, table_name):
  68         return []
  69
  70     def table_exists(self, table_name):
  71         try:
  72             self.conn.h5file.get_node(table_name)
  73             return True
  74         except:
  75             return False
  76
  77     def table_definition(self, table_type, table_name):
  78         table = self.conn.h5file.get_node(table_name)
  79         colnames = ['value']
  80         if isinstance(table, tables.table.Table):
  81             colnames = table.colnames
  82
  83         return 'CREATE TABLE {} ({})'.format(
  84             table_name,
  85             ', '.join(colnames),
  86         )
  87
  88     def indices_definition(self, table_name):
  89         return []
  90
  91     def execute(
  92         self,
  93         sql,
  94         params=None,
  95         truncate=False,
  96         custom_time_limit=None,
  97         page_size=None,
  98         log_sql_errors=True,
  99     ):
 100         results = []
 101         truncated = False
 102         description = ()
 103
 104         parsed_sql = parse_sql(sql, params)
 105
 106         while isinstance(parsed_sql['from'], dict):
 107             # Pytables does not support subqueries
 108             parsed_sql['from'] = parsed_sql['from']['value']['from']
 109
 110         table = self.conn.h5file.get_node(parsed_sql['from'])
 111         table_rows = []
 112         fields = parsed_sql['select']
 113         colnames = ['value']
 114         if type(table) is tables.table.Table:
 115             colnames = table.colnames
 116
 117         query = ''
 118         start = 0
 119         end = table.nrows
 120
 121         def _get_field_type(field):
 122             coltype = table.dtype.name
 123             if type(table) is tables.table.Table:
 124                 coltype = table.coltypes[field]
 125             return coltype
 126
 127         # Use 'where' statement or get all the rows
 128         def _cast_param(field, pname):
 129             # Cast value to the column type
 130             coltype = _get_field_type(field)
 131             fcast = None
 132             if coltype == 'string':
 133                 fcast = str
 134             elif coltype.startswith('int'):
 135                 fcast = int
 136             elif coltype.startswith('float'):
 137                 fcast = float
 138             if fcast:
 139                 params[pname] = fcast(params[pname])
 140
 141         def _translate_where(where):
 142             # Translate SQL to PyTables expression
 143             nonlocal start, end
 144             expr = ''
 145             operator = list(where)[0]
 146
 147             if operator in ['and', 'or']:
 148                 subexpr = [_translate_where(e) for e in where[operator]]
 149                 subexpr = filter(lambda e: e, subexpr)
 150                 subexpr = ["({})".format(e) for e in subexpr]
 151                 expr = " {} ".format(self.operators[operator]).join(subexpr)
 152             elif operator == 'exists':
 153                 pass
 154             elif where == {'eq': ['rowid', 'p0']}:
 155                 start = int(params['p0'])
 156                 end = start + 1
 157             elif where == {'gt': ['rowid', 'p0']}:
 158                 start = int(params['p0']) + 1
 159             else:
 160                 left, right = where[operator]
 161
 162                 if isinstance(left, dict):
 163                     left = "(" + _translate_where(left) + ")"
 164                 elif left in params:
 165                     _cast_param(right, left)
 166
 167                 if isinstance(right, dict):
 168                     right = "(" + _translate_where(right) + ")"
 169                 elif right in params:
 170                     _cast_param(left, right)
 171
 172                 expr = "{left} {operator} {right}".format(
 173                     left=left,
 174                     operator=self.operators.get(operator, operator),
 175                     right=right,
 176                 )
 177
 178             return expr
 179
 180         if 'where' in parsed_sql:
 181             if type(parsed_sql['where']) is dict:
 182                 query = _translate_where(parsed_sql['where'])
 183             else:
 184                 query = parsed_sql['where']
 185
 186         # Sort by column
 187         orderby = ''
 188         if 'orderby' in parsed_sql:
 189             orderby = parsed_sql['orderby']
 190             if type(orderby) is list:
 191                 orderby = orderby[0]
 192             orderby = orderby['value']
 193             if orderby == 'rowid':
 194                 orderby = ''
 195
 196         # Limit number of rows
 197         limit = None
 198         if 'limit' in parsed_sql:
 199             limit = int(parsed_sql['limit'])
 200
 201         # Offset
 202         offset = None
 203         if 'offset' in parsed_sql:
 204             offset = int(parsed_sql['offset'])
 205
 206         # Truncate if needed
 207         if page_size and max_returned_rows and truncate:
 208             if max_returned_rows == page_size:
 209                 max_returned_rows += 1
 210
 211         # Execute query
 212         if query:
 213             if not ' glob ' in query:
 214                 table_rows = table.where(query, params, start, end)
 215         elif orderby:
 216             table_rows = table.itersorted(orderby, start=start, stop=end)
 217         else:
 218             table_rows = table.iterrows(start, end)
 219
 220         # Prepare rows
 221         def normalize_field_value(value):
 222             if type(value) is bytes:
 223                 return value.decode('utf-8')
 224             elif not type(value) in (int, float, complex):
 225                 return str(value)
 226             else:
 227                 return value
 228
 229         def make_get_rowid():
 230             if type(table) is tables.table.Table:
 231                 def get_rowid(row):
 232                     return int(row.nrow)
 233             else:
 234                 rowid = start - 1
 235                 def get_rowid(row):
 236                     nonlocal rowid
 237                     rowid += 1
 238                     return rowid
 239             return get_rowid
 240
 241         def make_get_row_value():
 242             if type(table) is tables.table.Table:
 243                 def get_row_value(row, field):
 244                     return row[field]
 245             else:
 246                 def get_row_value(row, field):
 247                     return row
 248             return get_row_value
 249
 250         # Get results
 251         get_rowid = make_get_rowid()
 252         get_row_value = make_get_row_value()
 253         if offset:
 254             table_rows = table_rows[offset:]
 255         count = 0
 256         for table_row in table_rows:
 257             count += 1
 258             if limit is not None and count > limit:
 259                 break
 260             if truncate and max_returned_rows and count > max_returned_rows:
 261                 truncated = True
 262                 break
 263             row = {}
 264             for field in fields:
 265                 field_name = field
 266                 if isinstance(field, dict):
 267                     field_name = field['value']
 268                 if isinstance(field_name, dict) and 'distinct' in field_name:
 269                     field_name = field_name['distinct']
 270                 if field_name == 'rowid':
 271                     row['rowid'] = get_rowid(table_row)
 272                 elif field_name == '*':
 273                     for col in colnames:
 274                         row[col] = normalize_field_value(get_row_value(table_row, col))
 275                 elif isinstance(field_name, dict):
 276                     if field_name.get('count') == '*':
 277                         row['count(*)'] = int(table.nrows)
 278                     elif field_name.get('json_type'):
 279                         field_name = field_name.get('json_type')
 280                         row['json_type(' + field_name + ')'] = _get_field_type(field_name)
 281                     else:
 282                         raise Exception("Function not recognized")
 283                 else:
 284                     row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
 285             results.append(row)
 286
 287         # Prepare query description
 288         for field in [f['value'] if isinstance(f, dict) else f for f in fields]:
 289             if field == '*':
 290                 for col in colnames:
 291                     description += ((col,),)
 292             else:
 293                 description += ((field,),)
 294
 295         return results, truncated, description