CODEWITHSUNDEEP
mysqlgo
koblas
koblas

Reputation: 27048

Dumping MySQL tables to JSON with Golang

Was putting together a quick dumper for MySQL to JSON in Go. However I find that everything that I retrieve from the database is a []byte array. Thus instead of native JSON integers or booleans, I'm getting everything encoded as strings.

Subset of the code:

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


func dumpTable(w io.Writer, table) {
    // ...

    rows, err := Query(db, fmt.Sprintf("SELECT * FROM %s", table))
    checkError(err)
    columns, err := rows.Columns()
    checkError(err)

    scanArgs := make([]interface{}, len(columns))
    values   := make([]interface{}, len(columns))

    for i := range values {
        scanArgs[i] = &values[i]
    }

    for rows.Next() {
        err = rows.Scan(scanArgs...)
        checkError(err)

        record := make(map[string]interface{})

        for i, col := range values {
            if col != nil {
                fmt.Printf("\n%s: type= %s\n", columns[i], reflect.TypeOf(col))

                switch t := col.(type) {
                default:
                    fmt.Printf("Unexpected type %T\n", t)
                case bool:
                    fmt.Printf("bool\n")
                    record[columns[i]] = col.(bool)
                case int:
                    fmt.Printf("int\n")
                    record[columns[i]] = col.(int)
                case int64:
                    fmt.Printf("int64\n")
                    record[columns[i]] = col.(int64)
                case float64:
                    fmt.Printf("float64\n")
                    record[columns[i]] = col.(float64)
                case string:
                    fmt.Printf("string\n")
                    record[columns[i]] = col.(string)
                case []byte:   // -- all cases go HERE!
                    fmt.Printf("[]byte\n")
                    record[columns[i]] = string(col.([]byte))
                case time.Time:
                    // record[columns[i]] = col.(string)
                }
            }
        }

        s, _ := json.Marshal(record)
        w.Write(s)
        io.WriteString(w, "\n")
    }
}

Upvotes: 22

Views: 28643

Answers (6)

turkenh
turkenh

Reputation: 2584

I also needed to dump database tables to json and here is how I achieved: (different than another answer in this topic, everything is not string, thanks to this answer: https://stackoverflow.com/a/17885636/4124416, I could get integer fields correctly) 

func getJSON(sqlString string) (string, error) {
    rows, err := db.Query(sqlString)
    if err != nil {
        return "", err
    }
    defer rows.Close()
    columns, err := rows.Columns()
    if err != nil {
        return "", err
    }
    count := len(columns)
    tableData := make([]map[string]interface{}, 0)
    values := make([]interface{}, count)
    valuePtrs := make([]interface{}, count)
    for rows.Next() {
        for i := 0; i < count; i++ {
          valuePtrs[i] = &values[i]
        }
        rows.Scan(valuePtrs...)
        entry := make(map[string]interface{})
        for i, col := range columns {
            var v interface{}
            val := values[i]
            b, ok := val.([]byte)
            if ok {
                v = string(b)
            } else {
                v = val
            }
            entry[col] = v
        }
        tableData = append(tableData, entry)
    }
    jsonData, err := json.Marshal(tableData)
    if err != nil {
        return "", err
    }
    fmt.Println(string(jsonData))
    return string(jsonData), nil 
}

Here is a sample output:

[{"ID":0,"Text":"Zero"},{"ID":1,"Text":"One"},{"ID":2,"Text":"Two"}]

Upvotes: 46

Dumi Jay
Dumi Jay

Reputation: 1245

Based on the answers here, this is the most efficient code I could come up with. Note that this is outputting each row as a separate JSON array to save key name repetition.

// OutputJSONMysqlRowsStream outputs rows as a JSON array stream to save ram & output size due to key name repetition
func OutputJSONMysqlRowsStream(writer http.ResponseWriter, rows *sql.Rows) {

    defer rows.Close()

    columns, err := rows.Columns()

    if err != nil {
        OutputJSONError(writer, "Failed to get column names")
        return
    }

    jsonColumns, err := json.Marshal(columns)

    if err != nil {
        OutputJSONError(writer, "Failed to encode json of column names")
        return
    }

    writer.Header().Set("Content-Type", "application/cal-json-stream; charset=utf-8")

    fmt.Fprintln(writer, "{\"status\": \"done\", \"data\":{ \"json_stream_fields\":"+string(jsonColumns)+"}}")

    columnCount := len(columns)
    rowDataHolder := make([]interface{}, columnCount)
    rowDataHolderPointers := make([]interface{}, columnCount)

    if err != nil {
        log.Println(err)
    }

    for rows.Next() {

        for i := 0; i < columnCount; i++ {
            rowDataHolderPointers[i] = &rowDataHolder[i]
        }

        err := rows.Scan(rowDataHolderPointers...)

        if err != nil {
            log.Println(err)
        } else {

            for i, value := range rowDataHolder {
                tempValue, ok := value.([]byte)

                if ok {
                    rowDataHolder[i] = string(tempValue)
                }
            }

            jsonEncoder := json.NewEncoder(writer)
            err = jsonEncoder.Encode(rowDataHolder)

            if err != nil {
                log.Println(err)
            }
        }
    }
}

Upvotes: 0

hygull
hygull

Reputation: 8740

I have a table named users inside practice_db database. I have mentioned the table structure with data in the following program that converts the users table into JSON format.

You can also check the source code at https://gist.github.com/hygull/1725442b0f121a5fc17b28e04796714d. 

/**
    {
        "created_on": "26 may 2017",
        "todos": [
            "go get github.com/go-sql-driver/mysql",
            "postman(optional)",
            "browser(optional)",    
        ],
        "aim": "Reading fname column into []string(slice of strings)"
    }
*/


/* 
    mysql> select * from users;
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    | id | fname     | lname    | uname    | email                         | contact      | profile_pic                                                                                     |
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    |  1 | Rishikesh | Agrawani | hygull   | [email protected] | 917353787704 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/user_group_users_accounts_contacts-512.png |
    |  2 | Sandeep   | E        | sandeep  | [email protected]       | 919739040038 | https://cdn4.iconfinder.com/data/icons/eldorado-user/40/user-512.png                            |
    |  3 | Darshan   | Sidar    | darshan  | [email protected]        | 917996917565 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/child_boy-512.png                          |
    |  4 | Surendra  | Prajapat | surendra | [email protected]     | 918385894407 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/account_male-512.png                       |
    |  5 | Mukesh    | Jakhar   | mukesh   | [email protected]     | 919772254140 | https://cdn2.iconfinder.com/data/icons/rcons-user/32/male-circle-512.png                        |
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    5 rows in set (0.00 sec)

    mysql> 
*/

package main

import "log"
import "net/http"
import "encoding/json"

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

func users(w http.ResponseWriter, r *http.Request) {
    // db, err := sql.Open("mysql", "<username>:<password>@tcp(127.0.0.1:<port>)/<dbname>?charset=utf8" )
    db, err := sql.Open("mysql", "hygull:admin@67@tcp(127.0.0.1:3306)/practice_db?charset=utf8")

    w.Header().Set("Content-Type", "application/json")

    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("select id, fname, lname, uname, email, contact, profile_pic from users")

    if err != nil {
        log.Fatal(err)
    }

    type User struct {
        Id int       `json:"id"`
        Fname string `json:"firstname"`
        Lname string `json:"lastname"`
        Uname string `json:"username"`
        Email string `json:"email"`
        Contact int `json:"contact"`
        ProfilePic string `json:"profile_pic"`
    }

    var users []User

    for rows.Next() {
        var id, contact int
        var fname string
        var lname string
        var uname, email, profile_pic string

        rows.Scan(&id ,&fname, &lname, &uname, &email, &contact, &profile_pic)
        users = append(users, User{id, fname, lname, uname, email, contact, &profile_pic })
    }

    usersBytes, _ := json.Marshal(&users)

    w.Write(usersBytes)
    db.Close()
}

func main() {
    http.HandleFunc("/users/", users)
    http.ListenAndServe(":8080", nil)
}

/* REQUSET

   http://127.0.0.1:8080/users/
*/

/* RESPONSE
[
  {
    "id": 1,
    "firstname": "Rishikesh",
    "lastname": "Agrawani",
    "username": "hygull",
    "email": "[email protected]",
    "contact": 917353787704,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/user_group_users_accounts_contacts-512.png"
  },
  {
    "id": 2,
    "firstname": "Sandeep",
    "lastname": "E",
    "username": "sandeep",
    "email": "[email protected]",
    "contact": 919739040038,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/eldorado-user/40/user-512.png"
  },
  {
    "id": 3,
    "firstname": "Darshan",
    "lastname": "Sidar",
    "username": "darshan",
    "email": "[email protected]",
    "contact": 917996917565,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/child_boy-512.png"
  },
  {
    "id": 4,
    "firstname": "Surendra",
    "lastname": "Prajapat",
    "username": "surendra",
    "email": "[email protected]",
    "contact": 918385894407,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/account_male-512.png"
  },
  {
    "id": 5,
    "firstname": "Mukesh",
    "lastname": "Jakhar",
    "username": "mukesh",
    "email": "[email protected]",
    "contact": 919772254140,
    "profile_pic": "https://cdn2.iconfinder.com/data/icons/rcons-user/32/male-circle-512.png"
  }
]
*/

Upvotes: 2

Vadim Homchik
Vadim Homchik

Reputation: 31

It is needed to use prepared statements to get the native types. MySQL has two protocols, one transmits everything as text, the other as the "real" type. And that binary protocol is only used when you use prepared statements. See https://github.com/go-sql-driver/mysql/issues/407

The function getJSON below is correct:

func getJSON(sqlString string) (string, error) {
  stmt, err := db.Prepare(sqlString)
  if err != nil {
    return "", err
  }
  defer stmt.Close()

  rows, err := stmt.Query()
  if err != nil {
    return "", err
  }
  defer rows.Close()

  columns, err := rows.Columns()
  if err != nil {
    return "", err
  }

  tableData := make([]map[string]interface{}, 0)

  count := len(columns)
  values := make([]interface{}, count)
  scanArgs := make([]interface{}, count)
  for i := range values {
    scanArgs[i] = &values[i]
  }

  for rows.Next() {
    err := rows.Scan(scanArgs...)
    if err != nil {
      return "", err
    }

    entry := make(map[string]interface{})
    for i, col := range columns {
      v := values[i]

      b, ok := v.([]byte)
      if (ok) {
        entry[col] = string(b)
      } else {
        entry[col] = v
      }
    }

    tableData = append(tableData, entry)
  }

  jsonData, err := json.Marshal(tableData)
  if err != nil {
    return "", err
  }

  return string(jsonData), nil 
}

Upvotes: 3

Andrew Yasinsky
Andrew Yasinsky

Reputation: 185

you can dump the table into json just fine, however everything will be string :(

q := "select * from table"
debug("SQL: %s", q)

rows, err := db.Query(q)
checkError(err)
defer rows.Close()

columns, err := rows.Columns()
checkError(err)

scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))

for i := range values {
    scanArgs[i] = &values[i]
}

for rows.Next() {
    err = rows.Scan(scanArgs...)
    checkError(err)

    record := make(map[string]interface{})

    for i, col := range values {
        if col != nil {
            record[columns[i]] = fmt.Sprintf("%s", string(col.([]byte)))
        }
    }

    s, _ := json.Marshal(record)
    fmt.Printf("%s\n", s)
}

Upvotes: -1

Arne
Arne

Reputation: 1900

There's not much you can do because the driver - database/sql interaction is pretty much a one way street and the driver can't help you with anything when the data is handed over to database/sql.

You could try your luck with http://godoc.org/github.com/arnehormann/sqlinternals/mysqlinternals

  • Query the database
  • Retrieve the Column slice with cols, err := mysqlinternals.Columns(rows)
  • Create a new var values := make([]interface{}, len(cols)) and iterate over cols
  • Get the closest matching Go type per column with refType, err := cols[i].ReflectGoType()
  • Create type placeholders with values[i] = reflect.Zero(refType).Interface()
  • rows.Next() and err = rows.Scan(values...). Don't recreate values, copy and reuse it.

I guess this will still be pretty slow, but you should be able to get somewhere with it. If you encounter problems, please file an issue - I'll get to it as soon as I can.

Upvotes: 2

Related Questions