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):
70 def table_exists(self, table_name):
72 self.conn.h5file.get_node(table_name)
77 def table_definition(self, table_type, table_name):
78 table = self.conn.h5file.get_node(table_name)
80 if isinstance(table, tables.table.Table):
81 colnames = table.colnames
83 return 'CREATE TABLE {} ({})'.format(
88 def indices_definition(self, table_name):
96 custom_time_limit=None,
104 parsed_sql = parse_sql(sql, params)
106 while isinstance(parsed_sql['from'], dict):
107 # Pytables does not support subqueries
108 parsed_sql['from'] = parsed_sql['from']['value']['from']
110 table = self.conn.h5file.get_node(parsed_sql['from'])
112 fields = parsed_sql['select']
114 if type(table) is tables.table.Table:
115 colnames = table.colnames
121 def _get_field_type(field):
122 coltype = table.dtype.name
123 if type(table) is tables.table.Table:
124 coltype = table.coltypes[field]
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)
132 if coltype == 'string':
134 elif coltype.startswith('int'):
136 elif coltype.startswith('float'):
139 params[pname] = fcast(params[pname])
141 def _translate_where(where):
142 # Translate SQL to PyTables expression
145 operator = list(where)[0]
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':
154 elif where == {'eq': ['rowid', 'p0']}:
155 start = int(params['p0'])
157 elif where == {'gt': ['rowid', 'p0']}:
158 start = int(params['p0']) + 1
160 left, right = where[operator]
162 if isinstance(left, dict):
163 left = "(" + _translate_where(left) + ")"
165 _cast_param(right, left)
167 if isinstance(right, dict):
168 right = "(" + _translate_where(right) + ")"
169 elif right in params:
170 _cast_param(left, right)
172 expr = "{left} {operator} {right}".format(
174 operator=self.operators.get(operator, operator),
180 if 'where' in parsed_sql:
181 if type(parsed_sql['where']) is dict:
182 query = _translate_where(parsed_sql['where'])
184 query = parsed_sql['where']
188 if 'orderby' in parsed_sql:
189 orderby = parsed_sql['orderby']
190 if type(orderby) is list:
192 orderby = orderby['value']
193 if orderby == 'rowid':
196 # Limit number of rows
198 if 'limit' in parsed_sql:
199 limit = int(parsed_sql['limit'])
202 if page_size and max_returned_rows and truncate:
203 if max_returned_rows == page_size:
204 max_returned_rows += 1
208 if not ' glob ' in query:
209 table_rows = table.where(query, params, start, end)
211 table_rows = table.itersorted(orderby, start=start, stop=end)
213 table_rows = table.iterrows(start, end)
216 def normalize_field_value(value):
217 if type(value) is bytes:
218 return value.decode('utf-8')
219 elif not type(value) in (int, float, complex):
224 def make_get_rowid():
225 if type(table) is tables.table.Table:
236 def make_get_row_value():
237 if type(table) is tables.table.Table:
238 def get_row_value(row, field):
241 def get_row_value(row, field):
246 get_rowid = make_get_rowid()
247 get_row_value = make_get_row_value()
249 for table_row in table_rows:
251 if limit is not None and count > limit:
253 if truncate and max_returned_rows and count > max_returned_rows:
259 if isinstance(field, dict):
260 field_name = field['value']
261 if isinstance(field_name, dict) and 'distinct' in field_name:
262 field_name = field_name['distinct']
263 if field_name == 'rowid':
264 row['rowid'] = get_rowid(table_row)
265 elif field_name == '*':
267 row[col] = normalize_field_value(get_row_value(table_row, col))
268 elif isinstance(field_name, dict):
269 if field_name.get('count') == '*':
270 row['count(*)'] = int(table.nrows)
271 elif field_name.get('json_type'):
272 field_name = field_name.get('json_type')
273 row['json_type(' + field_name + ')'] = _get_field_type(field)
275 raise Exception("Function not recognized")
277 row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
280 # Prepare query description
281 for field in [f['value'] if isinstance(f, dict) else f for f in fields]:
284 description += ((col,),)
286 description += ((field,),)
288 return results, truncated, description