.find() not working with comparison-operator

[luerhard]

Hey there, I am currently trying to get the comparison-operators from the documentation to work, which says i can do this:

# Find by comparison operator
elderly_users = table.find(age={'>=': 70})
possible_customers = table.find(age={'between': [21, 80]})

I get an error (on sqlite) or no answer (on mysql) when i try this. To reproduce the error, I have taken the sample date from this repo to make it easy

import dataset
import sqlalchemy
import importlib.util
path = "/home/lukas/git/dataset/test/sample_data.py"
spec = importlib.util.spec_from_file_location("test_data", path)
testdata = importlib.util.module_from_spec(spec)
spec.loader.exec_module(testdata)

db = dataset.connect("sqlite:///:memory:")
table = db["weather"]
table.insert_many(testdata.TEST_DATA)

temperatures = table.find(temperature={'between': [0, 4]})

This throws the following error:

---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 

InterfaceError: Error binding parameter 0 - probably unsupported type.

The above exception was the direct cause of the following exception:

InterfaceError                            Traceback (most recent call last)
/opt/conda/envs/ma/lib/python3.7/site-packages/IPython/core/interactiveshell.py in run_code(self, code_obj, result, async_)
   3266                 else:
-> 3267                     exec(code_obj, self.user_global_ns, self.user_ns)
   3268             finally:

<ipython-input-1-bb46e1247e91> in <module>
     12 
---> 13 temperatures = table.find(temperature={'between': [0, 4]})

/opt/conda/envs/ma/lib/python3.7/site-packages/dataset/table.py in find(self, *_clauses, **kwargs)
    473 
--> 474         return ResultIter(conn.execute(query),
    475                           row_type=self.db.row_type,

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    947         else:
--> 948             return meth(self, multiparams, params)
    949 

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    268         if self.supports_execution:
--> 269             return connection._execute_clauseelement(self, multiparams, params)
    270         else:

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1059             distilled_params,
-> 1060             compiled_sql, distilled_params
   1061         )

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1199                 cursor,
-> 1200                 context)
   1201 

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    264     cause = exc_value if exc_value is not exception else None
--> 265     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    266 

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    247         if value.__traceback__ is not tb:
--> 248             raise value.with_traceback(tb)
    249         raise value

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 

<class 'str'>: (<class 'AttributeError'>, AttributeError("'int' object has no attribute 'items'"))

During handling of the above exception, another exception occurred:

AttributeError                            Traceback (most recent call last)
/opt/conda/envs/ma/lib/python3.7/site-packages/IPython/core/interactiveshell.py in run_code(self, code_obj, result, async_)
   3282             if result is not None:
   3283                 result.error_in_exec = sys.exc_info()[1]
-> 3284             self.showtraceback(running_compiled_code=True)
   3285         else:
   3286             outflag = False

/opt/conda/envs/ma/lib/python3.7/site-packages/IPython/core/interactiveshell.py in showtraceback(self, exc_tuple, filename, tb_offset, exception_only, running_compiled_code)
   2021                                             value, tb, tb_offset=tb_offset)
   2022 
-> 2023                     self._showtraceback(etype, value, stb)
   2024                     if self.call_pdb:
   2025                         # drop into debugger

/opt/conda/envs/ma/lib/python3.7/site-packages/ipykernel/zmqshell.py in _showtraceback(self, etype, evalue, stb)
    544             u'traceback' : stb,
    545             u'ename' : unicode_type(etype.__name__),
--> 546             u'evalue' : py3compat.safe_unicode(evalue),
    547         }
    548 

/opt/conda/envs/ma/lib/python3.7/site-packages/ipython_genutils/py3compat.py in safe_unicode(e)
     63     """
     64     try:
---> 65         return unicode_type(e)
     66     except UnicodeError:
     67         pass

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/exc.py in __str__(self)
    322             if self.params:
    323                 params_repr = util._repr_params(self.params, 10)
--> 324                 details.append("[parameters: %r]" % params_repr)
    325         code_str = self._code_str()
    326         if code_str:

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/sql/util.py in __repr__(self)
    370             ))
    371         elif ismulti:
--> 372             return self._repr_multi(self.params, typ)
    373         else:
    374             return self._repr_params(self.params, typ)

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/sql/util.py in _repr_multi(self, multi_params, typ)
    388             elements = ", ".join(
    389                 self._repr_params(params, elem_type)
--> 390                 for params in multi_params)
    391         else:
    392             elements = ""

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/sql/util.py in <genexpr>(.0)
    388             elements = ", ".join(
    389                 self._repr_params(params, elem_type)
--> 390                 for params in multi_params)
    391         else:
    392             elements = ""

/opt/conda/envs/ma/lib/python3.7/site-packages/sqlalchemy/sql/util.py in _repr_params(self, params, typ)
    403                 ", ".join(
    404                     "%r: %s" % (key, trunc(value))
--> 405                     for key, value in params.items()
    406                 )
    407             )

AttributeError: 'int' object has no attribute 'items'

This works however...

temperatures = table.find(sqlalchemy.and_(table.table.c.temperature > 0, table.table.c.temperature < 4))
print(list(temperatures))

[OrderedDict([('id', 1), ('date', datetime.datetime(2011, 1, 1, 0, 0)), ('temperature', 1), ('place', 'G€lway')])]

Am I doing something wrong?

My versions are:

python=3.7.1
sqlalchemy=1.2.14
dataset=1.1.0

Thanks in advance ! Regards, Lukas Erhard

[saimn]

This is because the feature is not yet in a released version, and by default the documentation is for the master branch (/latest/). It would be great to have a new version with this cool feature!

[luerhard]

This explains a lot of the confusion, I have had. Thanks for the clarification!

[impredicative]

@saimn The documentation settings should be changed to default to stable, not latest. This should be a trivial change at https://readthedocs.org/dashboard/dataset/advanced/ If this is not fixed, it will continue to be a source of grief.