sqlalchemy-datatables: sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis

Thanks for your package. I’m using it with flask and I’m getting the message in the title with this setup / configuration.

This is my flask view function:

from sqlalchemy.orm import mapper

@theapp.route('/preview')
def preview():

    columns = []
    columns.append(ColumnDT('part_no'))
    columns.append(ColumnDT('part_nm'))
    columns.append(ColumnDT('material_spec'))
    columns.append(ColumnDT('thickness'))

    m = db.MetaData()
    ff = db.Table('fabs_features', m, autoload = True, autoload_with = db.engine)

    class Fabsfeatures(object):
        def __init__(self, part_no, part_nm, material_spec, thickness):
            self.part_no = part_no
            self.part_nm = part_nm
            self.material_spec = material_spec
            self.thickness = thickness

    mapper(Fabsfeatures, ff)

    qry = db.session.query(Fabsfeatures)

    rowTable = DataTables(request.args, Fabsfeatures, qry, columns)
    return jsonify(rowTable.output_result())

index.html

{% extends "base.html" %}

{% block styles %}
<link rel="stylesheet" href="{{url_for('.static', filename='jquery.dataTables.min.css')}}">
{{super()}}
{% endblock %}

{% block scripts %}
  {{super()}}
  <script src="{{url_for('.static', filename='jquery.dataTables.min.js')}}"> </script>
  <script type="text/javascript">
        $(document).ready(function() {
          $('#previewTable').DataTable( {
          "processing": true,
          "serverSide": true,
          "ajax": "{{ url_for('preview') }}"
          });
      });
  </script>
{% endblock %}

{% block content %}
<div class="container">
    <h3>Index Page</h3>
    <table id="previewTable" class="display" cellspacing="0" width="50%">

      <thead>
                <tr>
                    <th>part_no</th>
                    <th>part_nm</th>
                    <th>material_spec</th>
                    <th>thickness</th>
                </tr>
      </thead>
      <tbody>
      </tbody>

    </table>

</div>
{% endblock %}

With this setup, the ajax request fails with a 500 response. In the response body, the stack trace is:

Traceback (most recent call last):
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\flask\app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\okyernb\pythonprojects\datatables\app\views.py", line 51, in preview
    rowTable = DataTables(request.args, Fabsfeatures, qry, columns)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 101, in __init__
    self.run()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 152, in run
    self.filtering()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\datatables\__init__.py", line 265, in filtering
    self.cardinality_filtered = self.query.count()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2790, in count
    return self.from_self(col).scalar()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2559, in scalar
    ret = self.one()
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2528, in one
    ret = list(self)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2571, in __iter__
    return self._execute_and_instances(context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\orm\query.py", line 2586, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
    context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\util\compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\util\compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context
    context)
  File "C:\Users\okyernb\pythonprojects\datatables\env\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis
 [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT fabs_features.id AS fabs_features_id, fabs_features.part_no AS fabs_features_part_no, fabs_features.part_nm AS fabs_features_part_nm, fabs_features.material_spec AS fabs_features_material_s_1, fabs_features.thickness AS fabs_features_thickness \nFROM fabs_features \nWHERE lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.material_spec AS VARCHAR2)) LIKE lower(:param_3) AND lower(CAST(fabs_features.part_no AS VARCHAR2)) LIKE lower(:param_1) AND lower(CAST(fabs_features.part_nm AS VARCHAR2)) LIKE lower(:param_2) AND lower(CAST(fabs_features.material_spec AS VARCHAR2)) LIKE lower(:param_3) AND lower(CAST(fabs_features.thickness AS VARCHAR2)) LIKE lower(:param_4)) anon_1'] [parameters: {'param_3': '%%', 'param_1': '%%', 'param_2': '%%', 'param_4': '%%'}]

I’ve tried to mimic the flask example in the documentation but still this is what I get. Am I doing something wrong or this is a bug in the library? Thank you.