64

I want to create a SQL interface on top of a non-relational data store. Non-relational data store, but it makes sense to access the data in a relational manner.

I am looking into using ANTLR to produce an AST that represents the SQL as a relational algebra expression. Then return data by evaluating/walking the tree.

I have never implemented a parser before, and I would therefore like some advice on how to best implement a SQL parser and evaluator.

  • Does the approach described above sound about right?
  • Are there other tools/libraries I should look into? Like PLY or Pyparsing.
  • Pointers to articles, books or source code that will help me is appreciated.

Update:

I implemented a simple SQL parser using pyparsing. Combined with Python code that implement the relational operations against my data store, this was fairly simple.

As I said in one of the comments, the point of the exercise was to make the data available to reporting engines. To do this, I probably will need to implement an ODBC driver. This is probably a lot of work.

Improve this question
3
  • 3
    Why impose SQL limitations on objects? What's to be gained? What's wrong with OQL? en.wikipedia.org/wiki/Object_Query_Language Commented Sep 8, 2009 at 20:00
  • 11
    To be gained: A query interface that a huge number of reporting tools can use. I plan to implement an ODBC driver on the client. So that business users can use Crystal Reports, Excel etc. to fetch data from the data store. OQL, while probably a nice query language (I've never used it), is not as wide-spread as SQL. Commented Sep 9, 2009 at 11:29
  • 3
    +1 both: one of the biggest issues with OO databases is exactly the lack of reporting engines :( Commented Sep 19, 2009 at 11:43

7 Answers 7

Reset to default
47

I have looked into this issue quite extensively. Python-sqlparse is a non validating parser which is not really what you need. The examples in antlr need a lot of work to convert to a nice ast in python. The sql standard grammars are here, but it would be a full time job to convert them yourself and it is likely that you would only need a subset of them i.e no joins. You could try looking at the gadfly (a Python SQL database) as well, but I avoided it as they used their own parsing tool.

For my case, I only essentially needed a where clause. I tried booleneo (a boolean expression parser) written with pyparsing but ended up using pyparsing from scratch. The first link in the reddit post of Mark Rushakoff gives a SQL example using it. Whoosh a full text search engine also uses it but I have not looked at the source to see how.

Pyparsing is very easy to use and you can very easily customize it to not be exactly the same as SQL (most of the syntax you will not need). I did not like ply as it uses some magic using naming conventions.

In short give pyparsing a try, it will most likely be powerful enough to do what you need and the simple integration with python (with easy callbacks and error handling) will make the experience pretty painless.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for sharing your experiences. From initial, very limited testing of python-sqlparse, it seems that I might be able to use it. I will try to work with the returned value from the parse function in python-sqlparse. But I will look into pyparsing in any case.
Pyparsing is a good tool for this, with lots of examples of parsing sql around.
This poster on the pyparsing wiki (pyparsing.wikispaces.com/message/view/home/14105203) just reported completing a SQL SELECT parser - perhaps you could contact him/her for help, suggestions, or even the code.
TFTT. I have contacted the poster.
I implemented it using pyparsing. Pyparsing worked great for this.
13

This reddit post suggests python-sqlparse as an existing implementation, among a couple other links.

Improve this answer

3 Comments

Thank you for the suggestion. Python-sqlparse looks interesting, I will give it a try.
It's been a while since this was posted - there is now github.com/macbre/sql-metadata which is built on the mentioned sqlparse library. sql-metadata doesn't give you an AST, but will extract resolved column and table names from a query.
sql-metadata is amazing, i was looking for a ddl parser, and even though this doensn't handle ddls, its a great example of how to work with the tokenizer
8

SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine. Syntax errors are highlighted. However, it should be noted that SQL validation is not SQLGlot’s goal, so some syntax errors may go unnoticed. (reddit) (benchmarks)

sqltree is an experimental parser for SQL, providing a syntax tree for SQL queries.

sample-sql-translator is a hand-written recursive-descent parser for SQL.

sqloxide wraps rust bindings for sqlparser-rs into a python package.

tree-sitter-languages contains tree-sitter-sql and tree-sitter-sqlite, which offer fast incremental concrete-syntax-tree parsing for SQL. this is also useful if your app needs to parse many languages, like for example ast-grep.

Improve this answer

1 Comment

In 2024 this is probably the best answer. The idea that someone needs to write their own SQL parser using something like pyparsing as suggested in the top answer feels outdated.
5

TwoLaid's Python SQL Parser works very well for my purposes. It's written in C and needs to be compiled. It is robust. It parses out individual elements of each clause.

https://github.com/TwoLaid/python-sqlparser

I'm using it to parse out queries column names to use in report headers. Here is an example.

import sqlparser

def get_query_columns(sql):
   '''Return a list of column headers from given sqls select clause'''

   columns = []

   parser = sqlparser.Parser()

   # Parser does not like new lines
   sql2 = sql.replace('\n', ' ')

   # Check for syntax errors
   if parser.check_syntax(sql2) != 0:
      raise Exception('get_query_columns: SQL invalid.')

   stmt = parser.get_statement(0)
   root = stmt.get_root()
   qcolumns = root.__dict__['resultColumnList']
   for qcolumn in qcolumns.list:
      if qcolumn.aliasClause:
         alias = qcolumn.aliasClause.get_text()
         columns.append(alias)
      else:
         name = qcolumn.get_text()
         name = name.split('.')[-1] # remove table alias
         columns.append(name)

   return columns

sql = '''
SELECT 
   a.a,
   replace(coalesce(a.b, 'x'), 'x', 'y') as jim,
   a.bla as sally  -- some comment
FROM
   table_a as a
WHERE
   c > 20
'''

print get_query_columns(sql)

# output: ['a', 'jim', 'sally']
Improve this answer

1 Comment

Python SQL Parser does not support Python 3
1

Of course, it may be best to leverage python-sqlparse on Google Code

UPDATE: Now I see that this has been suggested - I concur that this is worthwhile:

Improve this answer

Comments

1

I am using python-sqlparse with great success.

In my case I am working with queries that are already validated, my AST-walking code can make some sane assumptions about the structure.

Improve this answer

Comments

1 
pip install sql-metadata

from sql_metadata import Parser

sample_query = "SELECT W.wslr_number as wslr_nbr FROM (select wslr_number from dbo.WSLR) W, budnet.BUDNET_WSLR BW WHERE W.wslr_number= BW.wslr_number"

tables_list = Parser(sample_query).tables

print(tables_list)

Here is an example of parsing a sql query using the parser library

Please find more details about the sql_metadata library here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.