Parse SQL into JSON so we can translate it for other datastores!

See changes

The objective is to convert SQL queries to JSON-izable parse trees. This originally targeted MySQL, but has grown to include other database engines. Please paste some SQL into a new issue if it does not work for you

September 2022 - I continue to resolve issues as they are raised. There are over 1000 tests, that covers most SQL for most databases, with limited DML support, including:

• inner queries,
• with clauses,
• window functions
• create/drop/alter tables and views
• insert/update/delete statements
• lambda (->) functions

pip install mo-sql-parsing

>>> from mo_sql_parsing import parse
>>> parse("select count(1) from jobs")
{'select': {'value': {'count': 1}}, 'from': 'jobs'}

Each SQL query is parsed to an object: Each clause is assigned to an object property of the same name.

>>> parse("select a as hello, b as world from jobs")
{'select': [{'value': 'a', 'name': 'hello'}, {'value': 'b', 'name': 'world'}], 'from': 'jobs'}

The SELECT clause is an array of objects containing name and value properties.

There are a few parsing modes you may be interested in:

SQLServer uses square brackets to delimit identifiers. For example

SELECT [Timestamp] FROM [table]

which conflicts with BigQuery array constructor (eg [1, 2, 3, 4]). You may use the SqlServer flavour with

from mo_sql_parsing import parse_sqlserver as parse

The default output for this parser is to emit a null function {"null":{}} wherever NULL is encountered in the SQL. If you would like something different, you can replace nulls with None (or anything else for that matter):

result = parse(sql, null=None)

this has been implemented with a post-parse rewriting of the parse tree.

The default behaviour of the parser is to output function calls in simple_op format: The operator being a key in the object; {op: params}. This form can be difficult to work with because the object must be scanned for known operators, or possible optional arguments, or at least distinguished from a query object.

You can have the parser emit function calls in normal_op format

>>> from mo_sql_parsing import parse, normal_op
>>> parse("select trim(' ' from b+c)", calls=normal_op)

which produces calls in a normalized format

{"op": op, "args": args, "kwargs": kwargs}

here is the pretty-printed JSON from the example above:

{'select': {'value': {
    'op': 'trim', 
    'args': [{'op': 'add', 'args': ['b', 'c']}], 
    'kwargs': {'characters': {'literal': ' '}}
}}}

MySQL uses both double quotes and single quotes to declare literal strings. This is not ansi behaviour, but it is more forgiving for programmers coming from other languages. A specific parse function is provided:

result = parse_mysql(sql)

You may also generate SQL from the a given JSON document. This is done by the formatter, which is in Alpha state (Oct2021).

>>> from mo_sql_parsing import format
>>> format({"from":"test", "select":["a.b", "c"]})
'SELECT a.b, c FROM test'

In the event that the parser is not working for you, you can help make this better but simply pasting your sql (or JSON) into a new issue. Extra points if you describe the problem. Even more points if you submit a PR with a test. If you also submit a fix, then you also have my gratitude.

See the tests directory for instructions running tests, or writing new ones.

SQL queries are translated to JSON objects: Each clause is assigned to an object property of the same name.

# SELECT * FROM dual WHERE a>b ORDER BY a+b
{
    "select": "*", 
    "from": "dual", 
    "where": {"gt": ["a", "b"]}, 
    "orderby": {"value": {"add": ["a", "b"]}}
}

Expressions are also objects, but with only one property: The name of the operation, and the value holding (an array of) parameters for that operation.

{op: parameters}

and you can see this pattern in the previous example:

{"gt": ["a","b"]}

The mo-sql-parsing.scrub() method is used liberally throughout the code, and it "simplifies" the JSON. You may find this form a bit tedious to work with because the JSON property values can be values, lists of values, or missing. Please consider converting everything to arrays:

def listwrap(value):
    if value is None:
        return []
    elif isinstance(value, list)
        return value
    else:
        return [value]

then you may avoid all the is-it-a-list checks :

for select in listwrap(parsed_result.get('select')):
    do_something(select)

September 2022

• Added ALTER TABLE and COPY command parsing for Snowflake

November 2021

• Prefer BigQuery [] (create array) over SQLServer [] (identity)

• Added basic DML (INSERT/UPDATE/DELETE)

• flatter CREATE TABLE structures. The option list in column definition has been flattened:
  Old column format

    {"create table": {
        "columns": {
            "name": "name",
            "type": {"decimal": [2, 3]},
            "option": [
                "not null",
                "check": {"lt": [{"length": "name"}, 10]}
            ]
        }
    }}
  

  New column format

    {"create table": {
        "columns": {
            "name": "name", 
            "type": {"decimal": [2, 3]}
            "nullable": False,
            "check": {"lt": [{"length": "name"}, 10]} 
        }
    }}
  

October 2021

• changed error reporting; still terrible
• upgraded mo-parsing library which forced version change

October 2021

• fixed SELECT DISTINCT parsing
• added DISTINCT ON parsing

August 2021

• remove inline module mo-parsing
• support CREATE TABLE, add SQL "flavours" emit {null:{}} for None

November 2021

• changed parse result of SELECT DISTINCT
• simpler ORDER BY clause in window functions