Skip to content
Let's make a SQL parser so we can provide a familiar interface to non-sql datastores!
Python
Branch: dev
Clone or download
Kyle Lahnakoski
Kyle Lahnakoski ignore svn
Latest commit 429eee0 Sep 25, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
docs
moz_sql_parser update version number Jul 24, 2019
tests
.gitignore
.travis.yml
CODE_OF_CONDUCT.md
LICENSE first commit Nov 13, 2016
README.md Merge branch 'dev' of https://github.com/mozilla/moz-sql-parser into dev Apr 3, 2019
contribute.json
requirements.txt
setup.py
setuptools.json

README.md

Moz SQL Parser

Let's make a SQL parser so we can provide a familiar interface to non-sql datastores!

Branch Status
master Build Status
dev Build Status

Problem Statement

SQL is a familiar language used to access databases. Although, each database vendor has its quirky implementation, the average developer does not know enough SQL to be concerned with those quirks. This familiar core SQL (lowest common denominator, if you will) is useful enough to explore data in primitive ways. It is hoped that, once programmers have reviewed a datastore with basic SQL queries, and they see the value of that data, they will be motivated to use the datastore's native query format.

Objectives

The primary objective of this library is to convert some subset of SQL-92 queries to JSON-izable parse trees. A big enough subset to provide superficial data access via SQL, but not so much as we must deal with the document-relational impedance mismatch.

Non-Objectives

  • No plans to provide update statements, like update or insert
  • No plans to expand the language to all of SQL:2011
  • No plans to provide data access tools

Project Status

There are over 400 tests. This parser is good enough for basic usage, including inner queries.

You can see the parser in action at https://sql.telemetry.mozilla.org/ while using the ActiveData datasource

Install

pip install moz-sql-parser

Usage

>>> from moz_sql_parser import parse
>>> import json
>>> json.dumps(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.

>>> json.dumps(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.

Run Tests

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

More about implementation

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"]}

Notes

You can’t perform that action at this time.