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

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)


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)
    }
}

Tags:

Sql

Csv

Go