sqlitis 0.0.2

convert sql to sqlalchemy expressions

sqlitis

This is a tool to convert plain SQL queries to SQLAlchemy expressions. It is usable from the command line or as a library.

This converts to the SQLAlchemy expression language. It does not support the SQLAlchemy ORM.

$ pip install sqlitis

Examples

Turning a select query into a sqlachemy expression:

$ sqlitis 'select foo.name, bar.value from foo join bar'
select([foo.c.name, bar.c.value]).select_from(foo.join(bar))

Converting a join:

$ sqlitis 'foo join bar on foo.id = bar.foo_id and (foo.val < 100 or bar.val < 100)'
foo.join(bar, and_(foo.c.id == bar.c.foo_id, or_(foo.c.val < 100, bar.c.val < 100)))

Running tests

This repository includes a data-driven test suite as well as style checks (with flake8) and automatic code formatting (with yapf).

Use tox to run the tests.

$ pip install tox
### Run everything
$ tox
### Run just the unit/functional tests
$ tox -e py27
### Run just style/formatting checks
$ tox -e flake8

yapf is used to automatically fix code style/formatting errors. It will reformat your code in place.

### Auto-fix style/formatting checks
$ tox -e yapf

There are three types of tests:

• Unit tests of the internal models

• Unit tests of the core to_sqla function

• Functional tests that execute the generated SQLAlchemy expressions, against a sqlite database.

Most of these tests are generated from data in tests/data.json.

SQL Support Checklist

• [ ] Select

  • [x] Star: SELECT * FROM foo

  • [x] Multiple columns: SELECT a, b, c FROM foo

  • [x] Qualified column names: SELECT foo.a, foo.b FROM foo

  • [x] Column aliases: SELECT foo.id AS foo_id, foo.name AS foo_name from FOO

  • [ ] Joins

    • [x] Plain Join: SELECT foo.a, bar.b FROM foo JOIN bar

    • [x] Inner Join: SELECT foo.a, bar.b FROM foo INNER JOIN bar

    • [ ] Left/Right Joins

    • [ ] Outer Joins

  • [x] On Clauses: SELECT foo.a, bar.b FROM foo JOIN bar ON foo.id = bar.foo_id

  • [x] Conjuctions (AND/OR): SELECT foo.a, bar.b FROM foo join bar ON foo.id = bar.foo_id AND foo.val > 1

  • [x] Select from subquery: SELECT id FROM (SELECT * FROM foo)

  • [x] Where: SELECT id FROM foo WHERE id = 123

  • [x] Between: SELECT a FROM foo WHERE foo.val BETWEEN 1 AND 20

  • [x] Select distinct: SELECT DISTINCT a FROM foo

  • [ ] Aggregate functions (SUM, AVG, COUNT, MIN, MAX): SELECT COUNT(*) FROM foo

  • [ ] Group by: SELECT COUNT(*) FROM foo GROUP BY column

  • [ ] Like

  • [ ] Limit/offset

  • [ ] Order by

• [ ] Insert

• [ ] Update

• [ ] Delete