dongwopu8210
2013-11-15 00:55 阅读 318

使用Golang将MySQL表转储为JSON

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("
%s: type= %s
", columns[i], reflect.TypeOf(col))

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

        s, _ := json.Marshal(record)
        w.Write(s)
        io.WriteString(w, "
")
    }
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

6条回答 默认 最新

  • 已采纳
    doutui2883 doutui2883 2015-03-20 10:15

    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"}]
    
    点赞 评论 复制链接分享
  • donkey111111 donkey111111 2014-01-27 16:46

    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.

    点赞 评论 复制链接分享
  • duancui19840401 duancui19840401 2014-05-20 18:04

    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
    ", s)
    }
    
    点赞 评论 复制链接分享
  • duanmu2013 duanmu2013 2016-01-19 09:20

    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 
    }
    
    点赞 评论 复制链接分享
  • duaj39673 duaj39673 2017-05-27 05:26

    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   | rishikesh0014051992@gmail.com | 917353787704 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/user_group_users_accounts_contacts-512.png |
        |  2 | Sandeep   | E        | sandeep  | sandeepeswar8@gmail.com       | 919739040038 | https://cdn4.iconfinder.com/data/icons/eldorado-user/40/user-512.png                            |
        |  3 | Darshan   | Sidar    | darshan  | sidardarshan@gmail.com        | 917996917565 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/child_boy-512.png                          |
        |  4 | Surendra  | Prajapat | surendra | surendrakgadwal@gmail.com     | 918385894407 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/account_male-512.png                       |
        |  5 | Mukesh    | Jakhar   | mukesh   | mjakhar.kjakhar@gmail.com     | 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": "rishikesh0014051992@gmail.com",
        "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": "sandeepeswar8@gmail.com",
        "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": "sidardarshan@gmail.com",
        "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": "surendrakgadwal@gmail.com",
        "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": "mjakhar.kjakhar@gmail.com",
        "contact": 919772254140,
        "profile_pic": "https://cdn2.iconfinder.com/data/icons/rcons-user/32/male-circle-512.png"
      }
    ]
    */
    
    点赞 评论 复制链接分享
  • dtwvr26066 dtwvr26066 2019-05-08 07:28

    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)
                }
            }
        }
    }
    
    点赞 评论 复制链接分享

相关推荐