6

I am building a query engine for a database which is pulling data from SQL and other sources. For normal use cases the users can use a web form where the use can specify filtering parameters with select and ranged inputs. But for advanced use cases, I'd like to to specify a filtering equation box where the users could type

  • AND, OR

  • Nested parenthesis

  • variable names

  • , <, =, != operators

So the filtering equation could look something like:

 ((age > 50) or (weight > 100)) and diabetes='yes'

Then this input would be parsed, input errors detected (non-existing variable name, etc) and SQL Alchemy queries built based on it.

I saw an earlier post about the similar problem https://stackoverflow.com/a/1395854/315168

There seem to exist several language and mini-language parsers for Python http://navarra.ca/?p=538

However, does there exist any package which would be out of the box solution or near solution for my problem? If not what would be the simplest way to construct such query parser and constructor in Python?

Community
  • 1
  • 1
Mikko Ohtamaa
  • 82,057
  • 50
  • 264
  • 435
  • It's also option to parse the query on the client side and then post parsed query elements to the server over HTTP POST. – Mikko Ohtamaa Jul 03 '13 at 14:14
  • 1
    I guess a simple [EBNF](https://en.wikipedia.org/wiki/Extended_Backus%E2%80%93Naur_Form) or [PEG](http://en.wikipedia.org/wiki/Parsing_expression_grammar) grammar for this will be under 10 lines. There should be many options for Python and JS, I like [simpleparse](http://simpleparse.sourceforge.net/scanning_with_simpleparse.html) for Python. – Paulo Scardine Jul 03 '13 at 15:45
  • Found this http://redquerybuilder.appspot.com/ – Mikko Ohtamaa Jul 03 '13 at 17:32
  • 1
    http://www.wiggy.net/articles/sqlalchemy-in-reverse – Mikko Ohtamaa Jul 04 '13 at 07:34
  • You can post an answer if you found a solution. – Paulo Scardine Jul 05 '13 at 09:07
  • @PauloScardine: Not solution yet, but pieces of solution. RedQueryBuilder is not open source so it is no go. Will figure out SQL Alchemy + parsing, but is not very easy task and takes some time. – Mikko Ohtamaa Jul 05 '13 at 09:14

2 Answers2

1

Have a look at https://github.com/dfilatov/jspath

It's similar to xpath, so the syntax isn't as familiar as SQL, but it's powerful over hierarchical data.

flamingcow
  • 341
  • 2
  • 7
1

I don't know if this is still relevant to you, but here is my answer:

Firstly I have created a class that does exactly what you need. You may find it here: https://github.com/snow884/filter_expression_parser/ It takes a list of dictionaries as an input + filter query and returns the filtered results. You just have to define the list of fields that are allowed plus functions for checking the format of the constants passed as a part of filter expression.

The filter expression it ingests has to have the following format:

(time > 45.34) OR (((user_id eq 1) OR (date gt '2019-01-04')) AND (username ne 'john.doe'))

or just

username ne 'john123'

Secondly it was foolish of me to even create this code because dataframe.query(...) from pandas already does almost exactly what you need: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.query.html

James Swarowski
  • 31
  • 1
  • 3