SQL with Python in the Middle
SPyQL is SQL with Python in the middle, an open-source project fully written in Python for making command-line data processing more intuitive, readable and powerful. Try mixing in the same pot:
- a SQL SELECT for providing the structure;
- Python expressions for defining transformations and conditions;
- the essence of awk as a data-processing language;
- the JSON handling capabilities of jq.
How does a SPyQL query look like?
Simple, readable, and, as with all SPyQL programs, it’s an 1-liner. In a single statement, we are:
- reading a CSV (of purchases) with automatic header detection, dialect detection, type inference and casting,
- filtering out records that do not belong to the IT department or do not have a purchase timestamp
- summing the total purchases and grouping by how many days ago they happened,
- sorting from the most to the least recent day and
- writing the result in JSON format.
All this without loading the dataset into memory.
SPyQL will change data-processing in the terminal, making it accessible to anyone who knows a little-bit of Python and understands the basics of a SQL SELECT. On the other hand, it will give super-powers to experienced users. The possibilities are endless as you can import any Python library, and pipe data from/to any command-line tool. From querying APIs and Kafka, to write to files or databases, SPyQL will be the tool of choice for processing data in the command-line!
I'm very impressed - this is some very neat pragmatic software design. — Simon Willison, Creator of Datasette, co-creator of Django
I love this tool! I use it every day... — Alin Panaitiu, Creator of Lunar
Brilliant tool, thanks a lot for creating it and for the example here! — Greg Sadetsky, Co-founder and CTO at Decibel Ads