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
27 def table_names(self):
30 for node in self.conn.h5file
31 if not(isinstance(node, tables.group.Group))
34 def table_count(self, table_name):
35 table = self.conn.h5file.get_node(table_name)
36 return int(table.nrows)
38 def table_info(self, table_name):
39 table = self.conn.h5file.get_node(table_name)
41 if isinstance(table, tables.table.Table):
42 colnames = table.colnames
50 for idx, colname in enumerate(colnames)
53 def hidden_table_names(self):
56 def detect_spatialite(self):
62 def detect_fts(self, table_name):
65 def foreign_keys(self, table_name):
73 custom_time_limit=None,
81 parsed_sql = parse_sql(sql, params)
83 table = self.conn.h5file.get_node(parsed_sql['from'])
85 fields = parsed_sql['select']
87 if type(table) is tables.table.Table:
88 colnames = table.colnames
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]
101 if coltype == 'string':
103 elif coltype.startswith('int'):
105 elif coltype.startswith('float'):
108 params[pname] = fcast(params[pname])
110 def _translate_where(where):
111 # Translate SQL to PyTables expression
114 operator = list(where)[0]
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':
123 elif where == {'eq': ['rowid', 'p0']}:
124 start = int(params['p0'])
126 elif where == {'gt': ['rowid', 'p0']}:
127 start = int(params['p0']) + 1
129 left, right = where[operator]
131 _cast_param(right, left)
132 elif right in params:
133 _cast_param(left, right)
135 expr = "{left} {operator} {right}".format(left=left, operator=self.operators.get(operator, operator), right=right)
139 if 'where' in parsed_sql:
140 if type(parsed_sql['where']) is dict:
141 query = _translate_where(parsed_sql['where'])
143 query = parsed_sql['where']
147 if 'orderby' in parsed_sql:
148 orderby = parsed_sql['orderby']
149 if type(orderby) is list:
151 orderby = orderby['value']
152 if orderby == 'rowid':
155 # Limit number of rows
157 if 'limit' in parsed_sql:
158 limit = int(parsed_sql['limit'])
161 if page_size and max_returned_rows and truncate:
162 if max_returned_rows == page_size:
163 max_returned_rows += 1
167 table_rows = table.where(query, params, start, end)
169 table_rows = table.itersorted(orderby, start=start, stop=end)
171 table_rows = table.iterrows(start, end)
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):
182 def make_get_rowid():
183 if type(table) is tables.table.Table:
194 def make_get_row_value():
195 if type(table) is tables.table.Table:
196 def get_row_value(row, field):
199 def get_row_value(row, field):
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)})
207 get_rowid = make_get_rowid()
208 get_row_value = make_get_row_value()
210 for table_row in table_rows:
212 if limit and count > limit:
214 if truncate and max_returned_rows and count > max_returned_rows:
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 == '*':
226 row[col] = normalize_field_value(get_row_value(table_row, col))
228 row[field_name] = normalize_field_value(get_row_value(table_row, field_name))
231 # Prepare query description
232 for field in [f['value'] for f in fields]:
235 description += ((col,),)
237 description += ((field,),)
239 return results, truncated, description