read "SELECT *" columns into []string in go

Question

I want to write a Go program to dump rows from a database table into a csv file using SELECT *.

Go provides the excellent sql and csv apis, but csv expects arrays of strings and the Scan method in Rows "fills" fields according to their types. As I don't know the table before, I have no idea how many columns there are and what their types are.

It's my first program in Go, so I'm struggling a little.

How do I best read the columns from a Rows instance into a []string - and is that the "right" way?

Thanks!

UPDATE

I'm still struggling with the parameters. This is my code, for now I'm using panic instead of returning an error, but I'm going to change that later. In my test, I'm passing the query result and os.Stdout.

func dumpTable(rows *sql.Rows, out io.Writer) error {
    colNames, err := rows.Columns()
    if err != nil {
        panic(err)
    }
    if rows.Next() {
        writer := csv.NewWriter(out)
        writer.Comma = '\t'
        cols := make([]string, len(colNames))
        processRow := func() {
            err := rows.Scan(cols...)
            if err != nil {
                panic(err)
            }
            writer.Write(cols)
        }
        processRow()
        for rows.Next() {
            processRow()
        }
        writer.Flush()
    }
    return nil
}

For this, I get cannot use cols (type []string) as type []interface {} in function argument (at the writer.Write(cols) line.

I then tested

    readCols := make([]interface{}, len(colNames))
    writeCols := make([]string, len(colNames))
    processRow := func() {
        err := rows.Scan(readCols...)
        if err != nil {
            panic(err)
        }
        // ... CONVERSION?
        writer.Write(writeCols)
    }

which lead to panic: sql: Scan error on column index 0: destination not a pointer.

UPDATE 2

I independently arrived at ANisus' solution. This is the code I'm using now.

func dumpTable(rows *sql.Rows, out io.Writer) error {
    colNames, err := rows.Columns()
    if err != nil {
        panic(err)
    }
    writer := csv.NewWriter(out)
    writer.Comma = '\t'
    readCols := make([]interface{}, len(colNames))
    writeCols := make([]string, len(colNames))
    for i, _ := range writeCols {
        readCols[i] = &writeCols[i]
    }
    for rows.Next() {
        err := rows.Scan(readCols...)
        if err != nil {
            panic(err)
        }
        writer.Write(writeCols)
    }
    if err = rows.Err(); err != nil {
        panic(err)
    }
    writer.Flush()
    return nil
}

Solution 1

In order to directly Scan the values into a []string, you must create an []interface{} slice pointing to each string in your string slice.

Here you have a working example for MySQL (just change the sql.Open-command to match your settings):

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "database/sql"
)

func main() {
    db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/test?charset=utf8")
    defer db.Close()

    if err != nil {
        fmt.Println("Failed to connect", err)
        return
    }

    rows, err := db.Query(`SELECT 'one' col1, 'two' col2, 3 col3, NULL col4`)
    if err != nil {
        fmt.Println("Failed to run query", err)
        return
    }

    cols, err := rows.Columns()
    if err != nil {
        fmt.Println("Failed to get columns", err)
        return
    }

    // Result is your slice string.
    rawResult := make([][]byte, len(cols))
    result := make([]string, len(cols))

    dest := make([]interface{}, len(cols)) // A temporary interface{} slice
    for i, _ := range rawResult {
        dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
    }

    for rows.Next() {
        err = rows.Scan(dest...)
        if err != nil {
            fmt.Println("Failed to scan row", err)
            return
        }

        for i, raw := range rawResult {
            if raw == nil {
                result[i] = "\\N"
            } else {
                result[i] = string(raw)
            }
        }

        fmt.Printf("%#v\n", result)
    }
}

Solution 2

to get the Number of Columns (and also the names) just use the Columns() Function

http://golang.org/pkg/database/sql/#Rows.Columns

and as csv can only be a strings, just use a []byte type as dest type for Scanner. according to docu:

If an argument has type *[]byte, Scan saves in that argument a copy of the corresponding data. The copy is owned by the caller and can be modified and held indefinitely.

the data will not be transformed into its real type. and from this []byte you can then convert it to string.

if your are sure your tables only use base types (string, []byte, nil, int(s), float(s), bool) you can directly pass string as dest

but if you use other types like arrays, enums, or so on, then the data cant be transformed to string. but this also depends how the driver handles this types. (some months ago as example, the postgres driver was not able to handle arrays, so he returned always []byte where i needed to transform it by my own)

Comments

[CommonSenseCode]

alternative answer: stackoverflow.com/a/17266044/4031815

[Arne]

Thanks, for now this targets MySQL, so it's base types only.

[weberc2]

Make sure to accept this answer if it works for you (and if there are no better answers)

[Arne]

I will, but I'm still struggling with the conversion from []interface{} in Scan to []string in Write. Passing []string into Scan didn't compile - I just didn't edit my answer yet. I did upvote, though.

[fmt.Println.MKO]

you need to get the amount of columns first, and then pass that amount of string to Scan

[Arne]

Hey, great! I just arrived there myself and wanted to post it in an edit. Thanks! That's going to be my answer and I'll upvote it, but I still have a question: I want this to be fast and I'd like to escape string values and convert nil to \N. How do I best include the conversions in my code?

[ANisus]

My solution is far from water tight. It requires the values to be able to be stored in a string. E.g. if you get a NULL value from the db, you will have an error because string can't be nil. I have updated the answer using []byte and checking for nil values.

[Julien Schmidt]

You might consider to use sql.RawBytes instead of []byte

[jason]

I have a table with varchar, int(11), and double columns. When trying the code in this answer I get an error: sql: Scan error on column index 1: unsupported driver -> Scan pair: int64 -> *[]uint8. Changing [][]byte to []sql.RawBytes produces essentially the same error: ...Scan pair: int64 -> *sql.RawBytes. Does the @ANisus comment "able to be stored in a string" mean that all columns in the table must be text columns (varchar, text, etc...)?

[Daniel J. Pritchett]

ANisus - do you mind if I use this as a starting point for a toy golang query library? I'm MIT licensing the overall thing. (github.com/dpritchett/go-analyst)

[ANisus]

@DanielJ.Pritchett I don't mind at all! Consider it public domain.

[Qian Chen]

Thank you so much. That's exactly what I'm looking for. Golang's sql API is really awkwardly designed.

[KuN]

Don't think this fits the original question. You are just querying and scanning one known column to []string.