Parses and transforms SQL queries.
This project started is an experiement, and isn't in a stable state. Please don't use it in production code yet.
-
Parser
- Grammar: SELECT ... FROM ... [ WHERE ... ] [ ORDER BY ... ]
- Where clause expression tree
- Source tables/models (in FROM)
- Projections
-
Query builder
- Transform SQL into Mongo queries
- Transform SQL into SqlAlchemy queries
Here's a list of features that are currently in the plans for development:
-
Grammar
- Aliases
- Joins
- Unions
- Pivots
- Functions
- Bitwise operators
- Math expressions
- GROUP BY and HAVING
- LIMIT and OFFSET
- EXCEPT and INTERSECT
-
Parser
- Null-safe equality
- Type checking
- Logical optimizations
- Multiple tables/models
-
General
- Better test coverage
- Sphinx documentation
To run unit tests:
./setup.py test or make test
Parsing SQL query into a pyparsing parse tree:
>>> ast = sqlparse.parseString('select a from b where c = 1 and d = 2 or e = "f"')
>>> print ast.asXML('query')
<query>
<columns>
<column>a</column>
</columns>
<tables>
<table>b</table>
</tables>
<where>(and (= c 1) (or (= d 2) (= e "f")))</where>
</query>
>>> print ast.tables.asXML('tables')
<tables>
<table>b</table>
</tables>
Building a SqlAlchemy query object from a parsed SQL query:
>>> builder = sqlparse.bulders.SqlAlchemyQueryBuilder(sa_session, globals())
>>> sqlalchemy_query = builder.parse_and_build("""
... select * from User where
... not (last_name = 'Jacob' or
... (first_name != 'Chris' and last_name != 'Lyon')) and
... not is_active = 1
... """)
>>> for result in sqlalchemy_query.all():
... # do something
Building a MongoDB query object from a parsed SQL query:
>>> builder = sqlparse.builders.MongoQueryBuilder(pymongo_database)
>>> mongo_query = builder.parse_and_build("""
... select * from User where
... not (last_name = 'Jacob' or
... (first_name != 'Chris' and last_name != 'Lyon')) and
... not is_active = 1
... """)
>>> print json.dumps(mongo_query, indent=4)
{
"$and": [
{
"$nor": [
{
"$or": [
{
"last_name": "Jacob"
},
{
"$and": [
{
"first_name": {
"$ne": "Chris"
}
},
{
"last_name": {
"$ne": "Lyon"
}
}
]
}
]
}
]
},
{
"$nor": [
{
"is_active": 1
}
]
}
]
}
No documentation exists yet, except for what you see in this README file.
Copyright 2014 Chris Lyon
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.