Skip to main content

convert sql to sqlalchemy expressions

Project description


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


Turning a select query into a sqlachemy expression:

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

Converting a join:

$ sqlitis 'foo join bar on = bar.foo_id and (foo.val < 100 or bar.val < 100)'
foo.join(bar, and_( == 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 AS foo_id, 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 = bar.foo_id
    • [x] Conjuctions (AND/OR): SELECT foo.a, bar.b FROM foo join bar ON = 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

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
sqlitis-0.0.3.tar.gz (7.8 kB) Copy SHA256 hash SHA256 Source None

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page