How do I create a SQL interface on top of a non-relational data store in Python?
I want to build a SQL interface for a non-relational data store, but it makes sense to access the data in a relational manner. My current approach involves using ANTLR to generate an AST that represents the SQL as a relational algebra expression, which I can then evaluate by walking the tree.
As I’ve never implemented a parser before, I’d appreciate advice on how to best implement a SQL parser and evaluator in Python. Does my approach sound reasonable? Are there any other tools or libraries like PLY or Pyparsing that I should consider? Any pointers to articles, books, or source code would be really helpful.
Update:
I’ve implemented a simple SQL parser using pyparsing. Combined with Python code that performs the relational operations on my data store, this turned out to be fairly simple. The goal of this exercise is to make the data accessible to reporting engines, and I may need to implement an ODBC driver for this, which could involve quite a bit of work.
I’ve worked extensively with this type of problem before, and your use of python sql parser tools is a solid start. After researching, I found that Python’s sqlparse is a non-validating parser, which might not fully meet your needs. ANTLR, while powerful, requires significant effort to convert its output into a usable AST for Python. Standard SQL grammars are available but overkill for most use cases, especially if you don’t need features like joins.
For a simpler use case, focusing on parsing WHERE clauses or specific SQL segments, pyparsing stands out. It’s easy to use, flexible, and integrates seamlessly with Python. For instance, Mark Rushakoff’s Reddit post provides an excellent example of using pyparsing for SQL. You might also look into Whoosh, which implements custom parsers with pyparsing.
While PLY is another option, its reliance on magic naming conventions can be cumbersome. In my experience, sticking to pyparsing offers a great balance of power and simplicity. It provides callbacks, error handling, and customization options that allow you to create parsers tailored to your exact requirements.
That’s a solid starting point, Babita! I’ve also dabbled with building SQL parsers for niche use cases, and I agree that pyparsing can be highly effective. However, if your requirements grow or involve handling more extensive grammars, you might revisit ANTLR.
ANTLR’s Python bindings allow you to use its robust parsing capabilities while generating custom grammar files. It’s more effort upfront than pyparsing, but if you ever need to scale your python sql parser beyond simple expressions, ANTLR could save you time in the long run.
Another idea: since your goal includes reporting engines, you could also consider designing an intermediary layer that translates SQL queries into equivalent non-relational operations. This way, you’re not just building a parser but a small translation engine. While this is additional work, it can make your tool much more adaptable.
Great points, both Babita and Madhurima! Based on your insights, I’d like to add another perspective for anyone building a python sql parser. If your use case doesn’t strictly require implementing a SQL parser from scratch, consider using libraries like SQLAlchemy.
SQLAlchemy’s SQL Expression Language allows you to construct and execute SQL queries programmatically without manually writing parser logic. This abstraction can significantly reduce development time, especially for straightforward use cases. It also integrates seamlessly with relational and non-relational stores.
For a custom solution, combining SQLAlchemy with pyparsing or ANTLR for more complex scenarios could give you the best of both worlds: a parser for intricate expressions and a higher-level interface for query management.
Finally, if you plan to implement an ODBC driver, tools like pyodbc might help you bridge the gap between your data store and reporting tools. Keep scalability in mind, as the demands on your parser grow with use. Good luck!