search

pudo / dataset

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.
https://dataset.readthedocs.org/
MIT License
4.78k stars 298 forks source link

Queries not formatted correctly for SQLite #275

Closed tinder-haroldmartin closed 4 years ago

tinder-haroldmartin commented 5 years ago

When using SQLite, running a query like elderly_users = table.find(age={'=': 70}) results in the error:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

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

Traceback (most recent call last):
  File "db_open.py", line 6, in <module>
    two = table.find(age={'=': 70})
  File "/usr/local/lib/python3.7/site-packages/dataset/table.py", line 474, in find
    return ResultIter(conn.execute(query),
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError: <exception str() failed>

Inspecting the thrown sqlalchemy.exc.InterfaceError shows that the query and params are passed as:

SELECT <...>
FROM user 
WHERE user.age = ?
 LIMIT ? OFFSET ?
({'=': 70}, -1, 0)

So it appears that the WHERE clause is not correctly stringified. I tried to verify this theory and noticed that elderly_users = table.find(age=70) works as expected

pudo commented 4 years ago

Is it possible that you're using an outdated version of dataset or that the dict you submitted for the query wasn't a dict? I just tested it and it works.