Quite often, I’ll find myself wanting to query and manipulate MySQL data entirely on the command line. I could be building up a pipeline or working on a task that I’m going to eventually automate but haven’t quite gotten to yet. Whenver I have to do something like that, I have a small pile of scripts I’ve written over time that help out:

• skiphead: Skip the first line of output, used to skip over headers in a query response
• skipuntil: Skip all lines until we see one matching a pattern, used to resume partial tasks
• commaify: Take a list of single values on the command line and turn them into a comma separated list (for use in IN clauses)
• csv2json: a previously posted script for converting csv/tab delimited output to json
• jq: not my script, but used to take the output of csv2json and query it further in ways that would be complicated to do with SQL

Admitedly, the first two of those are one liners and I could easily remember them, but the advantage of a single command that does it is tab completion. sk<tab>, arrow to select which one I want, and off we go. I could put them as an alias, but I don’t always use the same shell (mostly fish, but sometimes Bash or Zsh).

read more...

Today at work, I had to process a bunch of CSV data. Realizing that I don’t have any particularly nice tools to work with streaming CSV data (although I did write about querying CSV files with SQL), I decided to write one:

$ cat users.csv

"user_id","name","email","password"
"1","Luke Skywalker","[email protected]","$2b$12$XQ1zDvl5PLS6g.K64H27xewPQMnkELa3LvzFSyay8p9kz0XXHVOFq"
"2","Han Solo","[email protected]","$2b$12$eKJGP.tt9u77PeXgMMFmlOyFWSuRZBUZLvmzuLlrum3vWPoRYgr92"

$ cat users.csv | csv2json | jq '.'

{
  "password": "$2b$12$XQ1zDvl5PLS6g.K64H27xewPQMnkELa3LvzFSyay8p9kz0XXHVOFq",
  "name": "Luke Skywalker",
  "user_id": "1",
  "email": "[email protected]"
}
{
  "password": "$2b$12$eKJGP.tt9u77PeXgMMFmlOyFWSuRZBUZLvmzuLlrum3vWPoRYgr92",
  "name": "Han Solo",
  "user_id": "2",
  "email": "[email protected]"
}

read more...

Yesterday’s post from Programming Praxis asks us to write a function that will read content formatted as comma-separated values and output the result as an HTML table. Their solution uses the text file database library that they posted about (which is a neat concept, you should check it out), but I think I’m going to work out the solution more directly.

read more...

Some time ago, I had a bunch of CSV files that I needed to extract some data from. They were all organized into tables with related columns between them all that made me think of a relational database–and it’s really easy to query relational databases, just use SQL. So what did I do? I wrote a script that will let me query CSV files as if they were a relational database.

read more...