X-Git-Url: https://git.jsancho.org/?p=datasette-pytables.git;a=blobdiff_plain;f=datasette_pytables%2F__init__.py;h=50628e7848ae704859bc41300b3f4c71f5531821;hp=50180fdc5c35c1d6ae93d817080dabd6d824ed9d;hb=fd6cd6a911d1d99e79c14b9796c6a2e918a1291a;hpb=b61c78ff44e9c719615cc6955c63cb732be55847

diff --git a/datasette_pytables/__init__.py b/datasette_pytables/__init__.py
index 50180fd..50628e7 100644
--- a/datasette_pytables/__init__.py
+++ b/datasette_pytables/__init__.py
@@ -1,6 +1,6 @@
-import tables
-import sqlparse
 from collections import OrderedDict
+import sqlparse
+import tables
 
 _connector_type = 'pytables'
 
@@ -19,7 +19,7 @@ def inspect(path):
             'name': table._v_pathname,
             'columns': colnames,
             'primary_keys': [],
-            'count': table.nrows,
+            'count': int(table.nrows),
             'label_column': None,
             'hidden': False,
             'fts_table': None,
@@ -50,12 +50,44 @@ def _parse_sql(sql):
                 parsed_sql[current_keyword] += str(token)
     return parsed_sql
 
+_operators = {
+    '=': '==',
+}
+
+def _translate_condition(table, condition, params):
+    field = condition.left.get_real_name()
+
+    operator = list(filter(lambda t: t.ttype == sqlparse.tokens.Comparison, condition.tokens))[0]
+    if operator.value in _operators:
+        operator = _operators[operator.value]
+    else:
+        operator = operator.value
+
+    value = condition.right.value
+    if value.startswith(':'):
+        # Value is a parameters
+        value = value[1:]
+        if value in params:
+            # Cast value to the column type
+            coltype = table.coltypes[field]
+            if coltype == 'string':
+                params[value] = str(params[value])
+            elif coltype.startswith('int'):
+                params[value] = int(params[value])
+            elif coltype.startswith('float'):
+                params[value] = float(params[value])
+
+    translated = "{left} {operator} {right}".format(left=field, operator=operator, right=value)
+    return translated, params
+
 class Connection:
     def __init__(self, path):
         self.path = path
         self.h5file = tables.open_file(path)
 
     def execute(self, sql, params=None, truncate=False):
+        if params is None:
+            params = {}
         rows = []
         truncated = False
         description = []
@@ -70,12 +102,24 @@ class Connection:
             query = ''
             start = 0
             end = table.nrows
-            for condition in parsed_sql['where'].get_sublists():
-                if str(condition) == '"rowid"=:p0':
-                    start = int(params['p0'])
-                    end = start + 1
+            try:
+                conditions = []
+                for condition in parsed_sql['where'].get_sublists():
+                    if str(condition) == '"rowid"=:p0':
+                        start = int(params['p0'])
+                        end = start + 1
+                    else:
+                        translated, params = _translate_condition(table, condition, params)
+                        conditions.append(translated)
+                if conditions:
+                    query = ') & ('.join(conditions)
+                    query = '(' + query + ')'
+            except:
+                # Probably it's a PyTables query
+                query = str(parsed_sql['where'])[6:]    # without where keyword
+
             if query:
-                table_rows = table.where(query, start, end)
+                table_rows = table.where(query, params, start, end)
             else:
                 table_rows = table.iterrows(start, end)
         else:
@@ -83,13 +127,13 @@ class Connection:
 
         # Prepare rows
         if len(fields) == 1 and fields[0] == 'count(*)':
-            rows.append(Row({fields[0]: table.nrows}))
+            rows.append(Row({fields[0]: int(table.nrows)}))
         else:
             for table_row in table_rows:
                 row = Row()
                 for field in fields:
                     if field == 'rowid':
-                        row[field] = table_row.nrow
+                        row[field] = int(table_row.nrow)
                     elif field == '*':
                         for col in table.colnames:
                             value = table_row[col]
@@ -108,6 +152,7 @@ class Connection:
             else:
                 description.append((field,))
 
+        # Return the rows
         if truncate:
             return rows, truncated, tuple(description)
         else: