sql2csv

README

sql2csv

A library designed to let you easily turn any arbitrary sql.Rows result from a query into a CSV file with a minimum of fuss. Remember to handle your errors and close your rows (not demonstrated in every example).

Usage

Importing the package

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql" // or the driver of your choice
    "github.com/datatug/sql2csv"
)

Dumping a query to a file

// we're assuming you've setup your sql.DB etc elsewhere
rows, _ := db.Query("SELECT * FROM users WHERE something=72")

err := sql2csv.WriteFile("~/important_user_report.csv", rows)
if err != nil {
    panic(err)
}

Return a query as a CSV download on the world wide web

http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query("SELECT * FROM users WHERE something=72")
    if err != nil {
        http.Error(w, err, http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    w.Header().Set("Content-type", "text/csv")
    w.Header().Set("Content-Disposition", "attachment; filename=\"report.csv\"")

    sql2csv.Write(w, rows)
})
http.ListenAndServe(":8080", nil)

Write and WriteFile should do cover the common cases by the power of Sensible Defaults™ but if you need more flexibility you can get an instance of a Converter and fiddle with a few settings.

rows, _ := db.Query("SELECT * FROM users WHERE something=72")

csvConverter := sql2csv.New(rows)

csvConverter.TimeFormat = time.RFC822
csvConverter.Headers = append(rows.Columns(), "extra_column_one", "extra_column_two")

csvConverter.SetRowPreProcessor(func (columns []string) (bool, []string) {
    // exclude admins from report
    // NOTE: this is a dumb example because "where role != 'admin'" is better
    // but every now and then you need to exclude stuff because of calculations
    // that are a pain in sql and this is a contrived README
    if columns[3] == "admin" {
      return false, []string{}
    }

    extra_column_one = generateSomethingHypotheticalFromColumn(columns[2])
    extra_column_two = lookupSomeApiThingForColumn(columns[4])

    return append(columns, extra_column_one, extra_column_two)
})

csvConverter.WriteFile("~/important_user_report.csv")

For more details on what else you can do to the Converter see the sql2csv godocs

License

© John Barton but under MIT (see LICENSE) except for fakedb_test.go which I lifted from the Go standard library and is under BSD and I am unsure what that means legally.

Documentation

Overview

sql2csv is a package to make it dead easy to turn arbitrary database query results (in the form of database/sql Rows) into CSV output.

Source and README at https://github.com/datatug/sql2csv

Index

• func Write(writer io.Writer, rows *sql.Rows) error
• func WriteFile(csvFileName string, rows *sql.Rows) error
• func WriteString(rows *sql.Rows) (string, error)
• type Converter
  • func NewConverter(rows *sql.Rows) *Converter
  • func (c *Converter) SetRowPostProcessor(processor CsvRowPostProcessorFunc)
  • func (c Converter) String() string
  • func (c Converter) Write(writer io.Writer) error
  • func (c Converter) WriteFile(csvFileName string) error
  • func (c Converter) WriteString() (string, error)
• type CsvRowPostProcessorFunc

Functions

func Write

func Write(writer io.Writer, rows *sql.Rows) error

Write will write a CSV file to the writer passed in (with headers) based on whatever is in the sql.Rows you pass in.

func WriteFile

func WriteFile(csvFileName string, rows *sql.Rows) error

WriteFile will write a CSV file to the file name specified (with headers) based on whatever is in the sql.Rows you pass in. It calls WriteCsvToWriter under the hood.

func WriteString

func WriteString(rows *sql.Rows) (string, error)

WriteString will return a string of the CSV. Don't use this unless you've got a small data set or a lot of memory

Types

type Converter

type Converter struct {
	Headers      []string // Column headers to use (default is rows.Columns())
	WriteHeaders bool     // Flag to output headers in your CSV (default is true)
	TimeFormat   string   // Format string for any time.Time values (default is time's default)
	Delimiter    rune     // Delimiter to use in your CSV (default is comma)
	// contains filtered or unexported fields
}

Converter does the actual work of converting the rows to CSV. There are a few settings you can override if you want to do some fancy stuff to your CSV.

func NewConverter

func NewConverter(rows *sql.Rows) *Converter

NewConverter will return a Converter which will write your CSV however you like but will allow you to set a bunch of non-default behaviour like overriding headers or injecting a pre-processing step into your conversion

func (*Converter) SetRowPostProcessor

func (c *Converter) SetRowPostProcessor(processor CsvRowPostProcessorFunc)

SetRowPostProcessor lets you specify a CsvRowPostProcessorFunc for this conversion

func (Converter) String

func (c Converter) String() string

String returns the CSV as a string in an fmt package friendly way

func (Converter) Write

func (c Converter) Write(writer io.Writer) error

Write writes the CSV to the Writer provided

func (Converter) WriteFile

func (c Converter) WriteFile(csvFileName string) error

WriteFile writes the CSV to the filename specified, return an error if problem

func (Converter) WriteString

func (c Converter) WriteString() (string, error)

WriteString returns the CSV as a string and an error if something goes wrong

type CsvRowPostProcessorFunc

type CsvRowPostProcessorFunc func(row []string, columnTypes []*sql.ColumnType) (outputRow bool, processedRow []string)

CsvRowPostProcessorFunc is a function type for postprocessing your CSV. It takes the columns after they've been munged into strings but before they've been passed into the CSV writer.

Return an outputRow of false if you want the row skipped otherwise return the processed Row slice as you want it written to the CSV.