X-Git-Url: https://git.jsancho.org/?p=datasette-pytables.git;a=blobdiff_plain;f=datasette_pytables%2F__init__.py;h=4c1cc0f9837aff45375c1f6c1654b8e71d359094;hp=9dc2a099bc32666af22122d94f19d39d4da532a5;hb=29e72871ce6657fd858dddb1689de8b64d7b6f54;hpb=745f744d01ebe1eff321fb70c580eac1f8b3ad4f diff --git a/datasette_pytables/__init__.py b/datasette_pytables/__init__.py index 9dc2a09..4c1cc0f 100644 --- a/datasette_pytables/__init__.py +++ b/datasette_pytables/__init__.py @@ -26,30 +26,50 @@ class PyTablesConnector(dc.Connector): 'binary_or': '|', } + def _serialize_table_name(self, table_name): + return table_name.replace('/', '%') + + def _deserialize_table_name(self, table_name): + return table_name.replace('%', '/') + def table_names(self): return [ - node._v_pathname + self._serialize_table_name(node._v_pathname) for node in self.conn.h5file if not(isinstance(node, tables.group.Group)) ] def table_count(self, table_name): - table = self.conn.h5file.get_node(table_name) + table = self.conn.h5file.get_node(self._deserialize_table_name(table_name)) return int(table.nrows) def table_info(self, table_name): - table = self.conn.h5file.get_node(table_name) - colnames = ['value'] + table = self.conn.h5file.get_node(self._deserialize_table_name(table_name)) + columns = [ + { + 'name': 'value', + 'type': table.dtype.name, + } + ] if isinstance(table, tables.table.Table): - colnames = table.colnames + columns = [ + { + 'name': colname, + 'type': table.coltypes[colname], + } + for colname in table.colnames + ] return [ { - 'idx': idx, - 'name': colname, - 'primary_key': False, + 'cid': cid, + 'name': column['name'], + 'type': column['type'], + 'notnull': True, + 'default_value': None, + 'is_pk': False, } - for idx, colname in enumerate(colnames) + for cid, column in enumerate(columns) ] def hidden_table_names(self): @@ -67,6 +87,28 @@ class PyTablesConnector(dc.Connector): def foreign_keys(self, table_name): return [] + def table_exists(self, table_name): + try: + self.conn.h5file.get_node(self._deserialize_table_name(table_name)) + return True + except: + return False + + def table_definition(self, table_type, table_name): + table_name = self._deserialize_table_name(table_name) + table = self.conn.h5file.get_node(table_name) + colnames = ['value'] + if isinstance(table, tables.table.Table): + colnames = table.colnames + + return 'CREATE TABLE {} ({})'.format( + table_name, + ', '.join(colnames), + ) + + def indices_definition(self, table_name): + return [] + def execute( self, sql, @@ -80,9 +122,17 @@ class PyTablesConnector(dc.Connector): truncated = False description = () + # Some Datasette queries uses glob operand, not supported by Pytables + if ' glob ' in sql: + return results, truncated, description + parsed_sql = parse_sql(sql, params) - table = self.conn.h5file.get_node(parsed_sql['from']) + while isinstance(parsed_sql['from'], dict): + # Pytables does not support subqueries + parsed_sql['from'] = parsed_sql['from']['value']['from'] + + table = self.conn.h5file.get_node(self._deserialize_table_name(parsed_sql['from'])) table_rows = [] fields = parsed_sql['select'] colnames = ['value'] @@ -93,12 +143,16 @@ class PyTablesConnector(dc.Connector): start = 0 end = table.nrows - # Use 'where' statement or get all the rows - def _cast_param(field, pname): - # Cast value to the column type + def _get_field_type(field): coltype = table.dtype.name if type(table) is tables.table.Table: coltype = table.coltypes[field] + return coltype + + # Use 'where' statement or get all the rows + def _cast_param(field, pname): + # Cast value to the column type + coltype = _get_field_type(field) fcast = None if coltype == 'string': fcast = str @@ -169,6 +223,11 @@ class PyTablesConnector(dc.Connector): if 'limit' in parsed_sql: limit = int(parsed_sql['limit']) + # Offset + offset = None + if 'offset' in parsed_sql: + offset = int(parsed_sql['offset']) + # Truncate if needed if page_size and max_returned_rows and truncate: if max_returned_rows == page_size: @@ -212,36 +271,45 @@ class PyTablesConnector(dc.Connector): return row return get_row_value - if len(fields) == 1 and type(fields[0]['value']) is dict and \ - fields[0]['value'].get('count') == '*': - results.append({'count(*)': int(table.nrows)}) - else: - get_rowid = make_get_rowid() - get_row_value = make_get_row_value() - count = 0 - for table_row in table_rows: - count += 1 - if limit and count > limit: - break - if truncate and max_returned_rows and count > max_returned_rows: - truncated = True - break - row = {} - for field in fields: + # Get results + get_rowid = make_get_rowid() + get_row_value = make_get_row_value() + if offset: + table_rows = table_rows[offset:] + count = 0 + for table_row in table_rows: + count += 1 + if limit is not None and count > limit: + break + if truncate and max_returned_rows and count > max_returned_rows: + truncated = True + break + row = {} + for field in fields: + field_name = field + if isinstance(field, dict): field_name = field['value'] - if type(field_name) is dict and 'distinct' in field_name: - field_name = field_name['distinct'] - if field_name == 'rowid': - row['rowid'] = get_rowid(table_row) - elif field_name == '*': - for col in colnames: - row[col] = normalize_field_value(get_row_value(table_row, col)) + if isinstance(field_name, dict) and 'distinct' in field_name: + field_name = field_name['distinct'] + if field_name == 'rowid': + row['rowid'] = get_rowid(table_row) + elif field_name == '*': + for col in colnames: + row[col] = normalize_field_value(get_row_value(table_row, col)) + elif isinstance(field_name, dict): + if field_name.get('count') == '*': + row['count(*)'] = int(table.nrows) + elif field_name.get('json_type'): + field_name = field_name.get('json_type') + row['json_type(' + field_name + ')'] = _get_field_type(field_name) else: - row[field_name] = normalize_field_value(get_row_value(table_row, field_name)) - results.append(row) + raise Exception("Function not recognized") + else: + row[field_name] = normalize_field_value(get_row_value(table_row, field_name)) + results.append(row) # Prepare query description - for field in [f['value'] for f in fields]: + for field in [f['value'] if isinstance(f, dict) else f for f in fields]: if field == '*': for col in colnames: description += ((col,),)