2 import datasette_connectors as dc
3 from .utils import parse_sql
6 class PyTablesConnection(dc.Connection):
7 def __init__(self, path, connector):
8 super().__init__(path, connector)
9 self.h5file = tables.open_file(path)
12 class PyTablesConnector(dc.Connector):
13 connector_type = 'pytables'
14 connection_class = PyTablesConnection
29 def table_names(self):
32 for node in self.conn.h5file
33 if not(isinstance(node, tables.group.Group))
36 def table_count(self, table_name):
37 table = self.conn.h5file.get_node(table_name)
38 return int(table.nrows)
40 def table_info(self, table_name):
41 table = self.conn.h5file.get_node(table_name)
43 if isinstance(table, tables.table.Table):
44 colnames = table.colnames
52 for idx, colname in enumerate(colnames)
55 def hidden_table_names(self):
58 def detect_spatialite(self):
64 def detect_fts(self, table_name):
67 def foreign_keys(self, table_name):
75 custom_time_limit=None,
83 parsed_sql = parse_sql(sql, params)
85 table = self.conn.h5file.get_node(parsed_sql['from'])
87 fields = parsed_sql['select']
89 if type(table) is tables.table.Table:
90 colnames = table.colnames
96 # Use 'where' statement or get all the rows
97 def _cast_param(field, pname):
98 # Cast value to the column type
99 coltype = table.dtype.name
100 if type(table) is tables.table.Table:
101 coltype = table.coltypes[field]
103 if coltype == 'string':
105 elif coltype.startswith('int'):
107 elif coltype.startswith('float'):
110 params[pname] = fcast(params[pname])
112 def _translate_where(where):
113 # Translate SQL to PyTables expression
116 operator = list(where)[0]
118 if operator in ['and', 'or']:
119 subexpr = [_translate_where(e) for e in where[operator]]
120 subexpr = filter(lambda e: e, subexpr)
121 subexpr = ["({})".format(e) for e in subexpr]
122 expr = " {} ".format(self.operators[operator]).join(subexpr)
123 elif operator == 'exists':
125 elif where == {'eq': ['rowid', 'p0']}:
126 start = int(params['p0'])
128 elif where == {'gt': ['rowid', 'p0']}:
129 start = int(params['p0']) + 1
131 left, right = where[operator]
133 if isinstance(left, dict):
134 left = "(" + _translate_where(left) + ")"
136 _cast_param(right, left)
138 if isinstance(right, dict):
139 right = "(" + _translate_where(right) + ")"
140 elif right in params:
141 _cast_param(left, right)
143 expr = "{left} {operator} {right}".format(
145 operator=self.operators.get(operator, operator),
151 if 'where' in parsed_sql:
152 if type(parsed_sql['where']) is dict:
153 query = _translate_where(parsed_sql['where'])
155 query = parsed_sql['where']
159 if 'orderby' in parsed_sql:
160 orderby = parsed_sql['orderby']
161 if type(orderby) is list:
163 orderby = orderby['value']
164 if orderby == 'rowid':
167 # Limit number of rows
169 if 'limit' in parsed_sql:
170 limit = int(parsed_sql['limit'])
173 if page_size and max_returned_rows and truncate:
174 if max_returned_rows == page_size:
175 max_returned_rows += 1
179 table_rows = table.where(query, params, start, end)
181 table_rows = table.itersorted(orderby, start=start, stop=end)
183 table_rows = table.iterrows(start, end)
186 def normalize_field_value(value):
187 if type(value) is bytes:
188 return value.decode('utf-8')
189 elif not type(value) in (int, float, complex):
194 def make_get_rowid():
195 if type(table) is tables.table.Table:
206 def make_get_row_value():
207 if type(table) is tables.table.Table:
208 def get_row_value(row, field):
211 def get_row_value(row, field):
215 if len(fields) == 1 and type(fields[0]['value']) is dict and \
216 fields[0]['value'].get('count') == '*':
217 results.append({'count(*)': int(table.nrows)})
219 get_rowid = make_get_rowid()
220 get_row_value = make_get_row_value()
222 for table_row in table_rows:
224 if limit and count > limit:
226 if truncate and max_returned_rows and count > max_returned_rows:
231 field_name = field['value']
232 if type(field_name) is dict and 'distinct' in field_name:
233 field_name = field_name['distinct']
234 if field_name == 'rowid':
235 row['rowid'] = get_rowid(table_row)
236 elif field_name == '*':
238 row[col] = normalize_field_value(get_row_value(table_row, col))
240 row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
243 # Prepare query description
244 for field in [f['value'] for f in fields]:
247 description += ((col,),)
249 description += ((field,),)
251 return results, truncated, description