SPyQL – SQL with Python in the middle
SPyQL (https://github.com/dcmoura/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, and the JSON handling capabilities of jq. How does a SPyQL query looks like? $ spyql “ IMPORT pendulum AS p SELECT (p.now() - p.from_timestamp(purchase_ts)).in_days() AS days_ago, sum_agg(price…
What it does
In the maker’s words, at launch
SPyQL (https://github.com/dcmoura/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, and the JSON handling capabilities of jq. How does a SPyQL query looks like? $ spyql “ IMPORT pendulum AS p SELECT (p.now() - p.from_timestamp(purchase_ts)).in_days() AS days_ago, sum_agg(price * quantity) AS total FROM csv WHERE department.upper() == 'IT' and purchase_ts is not Null GROUP BY 1 ORDER BY 1 TO json” < my_purchases.csv In a single statement we are 1) reading a CSV (of purchases) with automatic header detection, dialect detection, type inference and casting, 2) filtering out records that do not belong to the IT department or do not have a purchase timestamp 3) summing the total purchases and grouping by how many days ago they happened, 4) sorting from the most to the least recent day and 5) writing the result in JSON format. All this without loading the full dataset into memory. The Readme is loaded with recipes and there is also a demo video: https://vimeo.com/danielcmoura/spyqldemo Any feedback is welcomed! Thank you.
Does the same job
all alternatives →- CPCsql – Python lib for composeable SQL queries2020 · github.com · ▲89
- SQSQLite query inside a Bash function2021 · ▲134
Part of the workflow for building my website is the generation of a table in tab-separated column format (.tsv). The source data is found in four other .tsv files. I use an SQLite query to perform a 4-way join and write out the new table. For convenience, I wrote a script that encapsulates the query inside a Bash function. The example below illustrates this technique. repertoire() { pushd $CMM_SOURCES/_data sqlite3 <<EOS .headers on .mode tabs .import category.tsv category .import composition.tsv composition .import concert.tsv concert .import program.tsv program .once repertoire.tsv…
More dev tools this month
the category →
Open-source GTM skills for technical founders
Dev tools · 29d ago · gtmcofounder.com
OpenTrailPaper is open-source bike computer firmware for the LilyGO T5S3 4.7" E-Paper PRO. It supports offline maps, GPX routes, FIT recording and Bluetooth sensors.
Dev tools · 1d ago · opentrailpaper.com
Launched alongside, January 2022
the whole month →- LA
Life & fun · 2022 · looptap.vasanthv.com
- WP
History: Last year I had to replace the tire on my bike, and I was surprised how difficult it was to find a suitable new tire. There were a lot of numbers written on the casing, so I googled what they meant. In the end I was successful, but I didn't want to do the same work again for the next bike after I've forgotten the details. So I wrote this website. Technically, the web page is kept very simple, no frameworks, no templates, no website builder. It uses HTML5, CSS and JavaScript, and it privides a responsive layout for mobile usage. I'm happy to receive feedback. If you have tried the…
Life & fun · 2022 · fahrrad-tools.de
Startup Recipes▲630The ultimate MVP cheat sheet with 60+ startup formulas
Dev tools · 2022 · mvprecipes.com
