Skip to main content

convert sql to sqlalchemy expressions

Project description

Build Status PyPI PyPI - Python Version

Overview

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

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

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

  • <input type="checkbox" disabled="" />

    Select

    • <input type="checkbox" checked="" disabled="" />

      Star: SELECT * FROM foo

    • <input type="checkbox" checked="" disabled="" />

      Multiple columns: SELECT a, b, c FROM foo

    • <input type="checkbox" checked="" disabled="" />

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

    • <input type="checkbox" checked="" disabled="" />

      Column aliases: SELECT foo.id AS foo_id, foo.name AS foo_name from FOO

    • <input type="checkbox" disabled="" />

      Joins

      • <input type="checkbox" checked="" disabled="" />

        Inner Join:

        • SELECT * FROM foo JOIN bar
        • SELECT * FROM foo INNER JOIN bar
      • <input type="checkbox" checked="" disabled="" />

        Cross Joins

        • SELECT * FROM foo, bar
        • SELECT * FROM foo CROSS JOIN bar
      • <input type="checkbox" disabled="" />

        Left/Right Joins

      • <input type="checkbox" disabled="" />

        Outer Joins

    • <input type="checkbox" checked="" disabled="" />

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

    • <input type="checkbox" checked="" disabled="" />

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

    • <input type="checkbox" checked="" disabled="" />

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

    • <input type="checkbox" checked="" disabled="" />

      Where: SELECT id FROM foo WHERE id = 123

    • <input type="checkbox" checked="" disabled="" />

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

    • <input type="checkbox" checked="" disabled="" />

      Select distinct: SELECT DISTINCT a FROM foo

    • <input type="checkbox" disabled="" />

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

    • <input type="checkbox" disabled="" />

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

    • <input type="checkbox" disabled="" />

      Like

    • <input type="checkbox" checked="" disabled="" />

      Limit/offset

    • <input type="checkbox" disabled="" />

      Order by

  • <input type="checkbox" disabled="" />

    Insert

  • <input type="checkbox" disabled="" />

    Update

  • <input type="checkbox" disabled="" />

    Delete

Project details


Download files

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

Files for sqlitis, version 0.0.5
Filename, size File type Python version Upload date Hashes
Filename, size sqlitis-0.0.5.tar.gz (9.7 kB) File type Source Python version None Upload date Hashes View

Supported by

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