Skip to main content
Join the official 2019 Python Developers SurveyStart the survey!

sqloose is a less rigid SQL that allows ranges and negative indices.

Project description

sqloose is a SQL-like query language that maps directly to SQL. This module does not implement a database itself, but is instead a translator from sqloose to SQL.

Installation

pip install python-sqloose

Usage

from sqloose import sqloose

sql = sqloose.to_sql("SELECT age, race, gender, count(*) AS num FROM stats GROUP BY [1:3] ORDER BY -1 DESC")

Use Case

sqloose is designed as a less rigid SQL, offering a more convenient syntax. In particular, it allows ranges and negative indices to be used in GROUP BY and ORDER BY statements. The right index in the range specifies the final item, unlike Python in which the right index is the final item + 1.

Take the following SQL statement:

SELECT age, race, gender, count(*) AS num FROM stats GROUP BY 1,2,3 ORDER BY 4 DESC

In sqloose this can be represented many ways, such as:

SELECT age, race, gender, count(*) AS num FROM stats GROUP BY [1:3] ORDER BY -1 DESC
SELECT age, race, gender, count(*) AS num FROM stats GROUP BY [:3] ORDER BY -1 DESC
SELECT age, race, gender, count(*) AS num FROM stats GROUP BY [:-2] ORDER BY -1 DESC

Further, sqloose defines the GROUP TO and GROUP THROUGH constructs, which can be used in the same scenario:

SELECT age, race, gender, count(*) AS num FROM stats GROUP TO -1 ORDER BY -1 DESC
SELECT age, race, gender, count(*) AS num FROM stats GROUP TO 4 ORDER BY -1 DESC
SELECT age, race, gender, count(*) AS num FROM stats GROUP THROUGH 3 ORDER BY -1 DESC

SQL is often used in an interactive fashion, where data is being explored rather than simply being gathered. Queries are discovered, rather than being innately known. The pattern usually involves looking at aggregates (counts, sums, means), while changing both the selection of columns and the depth to find the most meaningful insights. The frustration in using this pattern is that we often must change the start of the query (the column list) and also the near end (the grouping indices). This can be made largely unnecessary by expanding SQL syntax to be more expressive.

sqloose does not validate the correctness of your SQL, but does validate the correctness of the constructs that are specific to sqloose, including the ranges and use of GROUP TO and GROUP THROUGH.

Authors

rdj - https://oddacious.github.io

History

0.1.0a1 (2016-09-25)

  • First pre-release on Github.

Project details


Release history Release notifications

Download files

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

Files for sqloose, version 0.1.0
Filename, size File type Python version Upload date Hashes
Filename, size sqloose-0.1.0.tar.gz (24.6 kB) File type Source Python version None Upload date Hashes View hashes

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