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 } 

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

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