My suite of TSV helper tools for working with PostgreSQL/Redshift, MySQL, SQLite

mysql-nullify

#!/usr/bin/env perl

use strict;
use File::Basename qw(basename);

my $PROGRAM = basename($0);

if ($ARGV[0] =~ /^(?:-h|--help)$/) {
    print "Turn blanks from TSV input lines into \\N for mysqlimport consumption.\n";
    print "Usage examples:\n";
    print "  $PROGRAM FILE\n";
    print "  cat FILE | $PROGRAM\n";
    exit;
}

while (<>) {
    chomp;
    my @row = map { $_ eq "" ? "\\N" : $_ } split("\t", $_, -1);
    print join("\t", @row), "\n";
}

psqltsv

#!/usr/bin/env bash

# For psql to connect to a database, we assume user has the environment
# variables PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD set.

if [[ -z $PGHOST || -z $PGPORT || -z $PGDATABASE || -z $PGUSER || -z $PGPASSWORD ]]; then
    >&2 echo "psql2tsv: Please set PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD to connect to a db"
    exit 1
fi

# -A, --no-align
# -F, --field-separator=separator
# -P, --pset=assignment
# -q, --quiet                        # dont print some common stuff that's printed
psql \
  --no-align \
  --field-separator $'\t' \
  --pset footer=off \
  --quiet \
  "$@"

sqlite-now-mem

#!/usr/bin/env ruby

# The goal here is a script that can be called like this:
#
#     sqlite-now-mem users.tsv,users,id,login,email products.tsv,products,id,name
#
# For each comma-separated tuple passed are arguments, the input file
# will be loaded into the table, and any following values are the
# names of columns for which indexes will be created.
#
# The --file=FILE flag can also be specified. If FILE doesn't exist it
# will be created. If it does, data will be imported into it,
# including if when the table already exists, rows will be appended.
#
#     sqlite-now-mem --file=website.sqlite users.tsv,users,id,login,email products.tsv,products,id,name

require "optimist"

opts = Optimist::options do
  opt :file, "SQLite database filename. Optional. If not specified, db is in memory.", :type => :string
end

p opts

sqlite_cmds = [
  ".timer on",
  ".mode tab",
]

ARGV.each do |definition|
  file, table, *index_cols = definition.split(",")
  p [file, table, index_cols]
  sqlite_cmds << ".import #{file} #{table}"
  index_cols.each do |index_col|
    sqlite_cmds << "create index index_#{table}_#{index_col} on #{table} (#{index_col});"
  end
end

# final_command = "sqlite3 -header" + " " + sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] }.join(" ") + " " +  %{-cmd ".mode column"}

final_system_cmd_elements = [
  "sqlite3",
  "-interactive",
  "-header",
  *sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] },
  %{-cmd ".mode column"},
  *opts[:file],
]

puts final_system_cmd_elements.join(" \\\n    ")

system final_system_cmd_elements.join(" \\\n    ")

sqlitetsv

#!/usr/bin/env bash

sqlite3 -header -separator $'\t' "$@"