Skip to main content

convert sql to sqlalchemy expressions

Project description

Build Status PyPI PyPI - Python Version


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

Sqlitis 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

Use tox to run the tests.

$ pip install tox

### Run everything
$ tox

### Run the unit/functional tests
$ tox -e py36

### Run style checks
$ tox -e flake8 black

The code is formatted using black.

### Reformat the code with black
$ make format

### Check if black needs to be run. This does not reformat.
$ tox -e black

There are three types of tests:

  • Unit tests of the internal model classes
  • Unit tests of the core to_sqla function
  • Functional tests that execute the generated SQLAlchemy expressions against a sqlite database
  • Functional tests of the CLI

These tests are parameterized (data driven) using data from test/*.yaml

SQL Support Checklist

  • Select

    • Star: SELECT * FROM foo

    • Multiple columns: SELECT a, b, c FROM foo

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

    • Column aliases: SELECT AS foo_id, AS foo_name from FOO

    • Joins

      • Inner Join:

        • SELECT * FROM foo JOIN bar
        • SELECT * FROM foo INNER JOIN bar
      • Cross Joins

        • SELECT * FROM foo, bar
        • SELECT * FROM foo CROSS JOIN bar
      • Left/Right Joins

      • Outer Joins

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

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

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

    • Where: SELECT id FROM foo WHERE id = 123

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

    • 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.

Source Distribution

sqlitis-0.0.5.tar.gz (9.7 kB view hashes)

Uploaded source

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page