How to convert *sql.Rows to typed JSON in Golang

huangapple go评论149阅读模式
英文:

How to convert *sql.Rows to typed JSON in Golang

问题

基本上,我正在尝试在MySQL数据库上运行查询,将数据转换为JSON并发送回客户端。我尝试了几种方法,所有的“简单”方法都会将整个JSON作为字符串发送回来。我希望将其作为一个键(string)和[]float64值发送回来。这样我就有了与键关联的数据数组。此外,这需要有一个类型。到目前为止,我找到的最好的方法是将所有数据放入一个结构体中,对其进行编码,然后将其发送回ResponseWriter

我看到有几个关于从数据库生成JSON的问题,但我没有找到任何使用结构体方法的内容。我将下面的代码写成一个单独的函数来说明我的问题。这个函数非常有限,它只能处理两个字段,并且必须是float64类型。

因此,我的问题是:如何在将此JSON从查询响应中创建之前,为其设置正确的类型,并且是否有一种动态的方法(即,可以接受可变数量的列和未知类型)?

type DbDao struct{
  db *sql.DB
}

type JSONData struct {
  Values []float64
  Dates []string
}

func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {

  stmt, err := d.db.Prepare(sqlString)
  if err != nil {
    return err
  }
  defer stmt.Close()

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

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

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

    var tempDate string 
    var tempValue float64
    var myjson JSONData

    d, dok := values[0].([]byte)
    v, vok := values[1].(float64)

    if dok {
      tempDate = string(d)
      if err != nil {
        return err
      }
      myjson.Dates = append(myjson.Dates, tempDate)
    }
    
    if vok {      
      tempValue = v 
      myjson.Values = append(myjson.Values, tempValue)
      fmt.Println(v)
      fmt.Println(tempValue)

    }    
    
    err = json.NewEncoder(w).Encode(&myjson)
    if err != nil {
      return err
    }
  }

  return nil 
}

希望这能帮到你!

英文:

Essentially, I am trying to run a query on a MySQL database, get the data made converted into JSON and sent back to the client. I have tried several methods and all of the "easy" ones result in sending back all of the JSON as a string. I need this to be send back as a key (string) with []float64 value. This way I have an array of data associated with a key. Also, this needs to have a type. The best method I've found so far to accomplish this was to build put all of the data into a struct, encode it and send that back to the ResponseWriter.

I have seen several questions on making JSON from a database but I haven't found anything utilizing the struct method. I wrote the below code into a single function to illustrate my question. This is VERY limited in that it will only handle two fields and it MUST be a float64.

Therefore, my question is: How do I create this JSON from a query response that has the correct type before sending this back to the client and is there a way to do this dynamically (ie, can accept a variable number of columns and unknown types)?:

{ "Values":[12.54, 76.98, 34.90], "Dates": ["2017-02-03", "2017-02-04:, "2017-02-05"]}

<pre><code>

type DbDao struct{
db *sql.DB
}
type JSONData struct {
Values []float64
Dates []string
}
func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {
stmt, err := d.db.Prepare(sqlString)
if err != nil {
return err
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
return err
}
defer rows.Close()
values := make([]interface{}, 2)
scanArgs := make([]interface{}, 2)
for i := range values {
scanArgs[i] = &amp;values[i]
}
for rows.Next() {
err := rows.Scan(scanArgs...)
if err != nil {
return err
}
var tempDate string 
var tempValue float64
var myjson JSONData
d, dok := values[0].([]byte)
v, vok := values[1].(float64)
if dok {
tempDate = string(d)
if err != nil {
return err
}
myjson.Dates = append(myjson.Dates, tempDate)
}
if vok {      
tempValue = v 
myjson.Values = append(myjson.Values, tempValue)
fmt.Println(v)
fmt.Println(tempValue)
}    
err = json.NewEncoder(w).Encode(&amp;myjson)
if err != nil {
return err
}
}
return nil 
}

</code></pre>

答案1

得分: 15

这是我能想到的最好的实现方法,可以使其具有动态性。而且与我的原始代码相比,这个实现方法要短得多。由于我经常看到这种类型的问题,希望这对其他人有所帮助。我也愿意接受其他更好的实现这个功能的答案:

func (d *DbDao) makeStructJSON(queryText string, w http.ResponseWriter) error {

    // 返回 rows *sql.Rows
    rows, err := d.db.Query(queryText)
    if err != nil {
        return err
    }
    columns, err := rows.Columns()
    if err != nil {
        return err
    }

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

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

    for rows.Next() {
        err := rows.Scan(scanArgs...)
        if err != nil {
            return err
        }
        for i, v := range values {

            x := v.([]byte)

            // 注意:来自 Go 博客:JSON and GO - 2011年1月25日:
            // json 包使用 map[string]interface{} 和 []interface{} 类型来存储任意的 JSON 对象和数组;它可以将任何有效的 JSON 数据解组为普通的 interface{} 值。默认的具体 Go 类型为:
            //
            // bool 用于 JSON 布尔值,
            // float64 用于 JSON 数字,
            // string 用于 JSON 字符串,
            // nil 用于 JSON null。

            if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
                masterData[columns[i]] = append(masterData[columns[i]], nx)
            } else if b, ok := strconv.ParseBool(string(x)); ok == nil {
                masterData[columns[i]] = append(masterData[columns[i]], b)
            } else if "string" == fmt.Sprintf("%T", string(x)) {
                masterData[columns[i]] = append(masterData[columns[i]], string(x))
            } else {
                fmt.Printf("Failed on if for type %T of %v\n", x, x)
            }

        }
    }

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

    err = json.NewEncoder(w).Encode(masterData)

    if err != nil {
        return err
    }
    return err
}
英文:

This is the best implementation that I was able to come up with that would make it dynamic. It is also significantly shorter than my original. As I've seen this type of question quite a bit, I hope this helps others. I am open to other answers that have a better implementation of this:

func (d *DbDao) makeStructJSON(queryText string, w http.ResponseWriter) error {
// returns rows *sql.Rows
rows, err := d.db.Query(queryText)
if err != nil {
return err
}
columns, err := rows.Columns()
if err != nil {
return err
}
count := len(columns)
values := make([]interface{}, count)
scanArgs := make([]interface{}, count)
for i := range values {
scanArgs[i] = &amp;values[i]
}
masterData := make(map[string][]interface{})
for rows.Next() {
err := rows.Scan(scanArgs...)
if err != nil {
return err
}
for i, v := range values {
x := v.([]byte)
//NOTE: FROM THE GO BLOG: JSON and GO - 25 Jan 2011:
// The json package uses map[string]interface{} and []interface{} values to store arbitrary JSON objects and arrays; it will happily unmarshal any valid JSON blob into a plain interface{} value. The default concrete Go types are:
//
// bool for JSON booleans,
// float64 for JSON numbers,
// string for JSON strings, and
// nil for JSON null.
if nx, ok := strconv.ParseFloat(string(x), 64); ok == nil {
masterData[columns[i]] = append(masterData[columns[i]], nx)
} else if b, ok := strconv.ParseBool(string(x)); ok == nil {
masterData[columns[i]] = append(masterData[columns[i]], b)
} else if &quot;string&quot; == fmt.Sprintf(&quot;%T&quot;, string(x)) {
masterData[columns[i]] = append(masterData[columns[i]], string(x))
} else {
fmt.Printf(&quot;Failed on if for type %T of %v\n&quot;, x, x)
}
}
}
w.Header().Set(&quot;Content-Type&quot;, &quot;application/json&quot;)
err = json.NewEncoder(w).Encode(masterData)
if err != nil {
return err
}
return err
}

答案2

得分: 12

这是一种更好的方法(在Postgres中进行了测试)。不需要使用反射/反射:

columnTypes, err := rows.ColumnTypes()

if err != nil {
    return err
}

count := len(columnTypes)
finalRows := []interface{}{}

for rows.Next() {

    scanArgs := make([]interface{}, count)

    for i, v := range columnTypes {

        switch v.DatabaseTypeName() {
        case "VARCHAR", "TEXT", "UUID", "TIMESTAMP":
            scanArgs[i] = new(sql.NullString)
            break;
        case "BOOL":
            scanArgs[i] = new(sql.NullBool)
            break;
        case "INT4":
            scanArgs[i] = new(sql.NullInt64)
            break;
        default:
            scanArgs[i] = new(sql.NullString)
        }
    }

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

    if err != nil {
        return err
    }

    masterData := map[string]interface{}{}

    for i, v := range columnTypes {

        if z, ok := (scanArgs[i]).(*sql.NullBool); ok  {
            masterData[v.Name()] = z.Bool
            continue;
        }

        if z, ok := (scanArgs[i]).(*sql.NullString); ok  {
            masterData[v.Name()] = z.String
            continue;
        }

        if z, ok := (scanArgs[i]).(*sql.NullInt64); ok  {
            masterData[v.Name()] = z.Int64
            continue;
        }

        if z, ok := (scanArgs[i]).(*sql.NullFloat64); ok  {
            masterData[v.Name()] = z.Float64
            continue;
        }

        if z, ok := (scanArgs[i]).(*sql.NullInt32); ok  {
            masterData[v.Name()] = z.Int32
            continue;
        }

        masterData[v.Name()] = scanArgs[i]
    }

    finalRows = append(finalRows, masterData)
}


z, err := json.Marshal(finalRows)
英文:

This is a much better way to do it (Tested with Postgres). No reflect/reflection needed:

    columnTypes, err := rows.ColumnTypes()
if err != nil {
return err
}
count := len(columnTypes)
finalRows := []interface{}{};
for rows.Next() {
scanArgs := make([]interface{}, count)
for i, v := range columnTypes {
switch v.DatabaseTypeName() {
case &quot;VARCHAR&quot;, &quot;TEXT&quot;, &quot;UUID&quot;, &quot;TIMESTAMP&quot;:
scanArgs[i] = new(sql.NullString)
break;
case &quot;BOOL&quot;:
scanArgs[i] = new(sql.NullBool)
break;
case &quot;INT4&quot;:
scanArgs[i] = new(sql.NullInt64)
break;
default:
scanArgs[i] = new(sql.NullString)
}
}
err := rows.Scan(scanArgs...)
if err != nil {
return err
}
masterData := map[string]interface{}{}
for i, v := range columnTypes {
if z, ok := (scanArgs[i]).(*sql.NullBool); ok  {
masterData[v.Name()] = z.Bool
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullString); ok  {
masterData[v.Name()] = z.String
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullInt64); ok  {
masterData[v.Name()] = z.Int64
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullFloat64); ok  {
masterData[v.Name()] = z.Float64
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullInt32); ok  {
masterData[v.Name()] = z.Int32
continue;
}
masterData[v.Name()] = scanArgs[i]
}
finalRows = append(finalRows, masterData)
}
z, err := json.Marshal(finalRows)

答案3

得分: 10

以下是一个更好的解决方案,使用反射来处理。它可以正确处理类型(例如,字符串值true不会错误地转换为布尔类型等)。

它还可以处理可能为空的类型(仅在MySQL中进行了测试-您可能需要根据其他驱动程序进行修改)。

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"reflect"

	"github.com/go-sql-driver/mysql"
)

// MySQL驱动程序返回的附加扫描类型。我没有查看PostgreSQL的情况。

type jsonNullInt64 struct {
	sql.NullInt64
}

func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
	if !v.Valid {
		return json.Marshal(nil)
	}
	return json.Marshal(v.Int64)
}

type jsonNullFloat64 struct {
	sql.NullFloat64
}

func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
	if !v.Valid {
		return json.Marshal(nil)
	}
	return json.Marshal(v.Float64)
}

type jsonNullTime struct {
	mysql.NullTime
}

func (v jsonNullTime) MarshalJSON() ([]byte, error) {
	if !v.Valid {
		return json.Marshal(nil)
	}
	return json.Marshal(v.Time)
}

var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
var nullTimeType = reflect.TypeOf(mysql.NullTime{})

// SQLToJSON将SQL结果转换为漂亮的JSON格式。它还可以很好地处理可能为空的值。参见https://stackoverflow.com/a/52572145/265521
func SQLToJSON(rows *sql.Rows) ([]byte, error) {
	columns, err := rows.Columns()
	if err != nil {
		return nil, fmt.Errorf("Column error: %v", err)
	}

	tt, err := rows.ColumnTypes()
	if err != nil {
		return nil, fmt.Errorf("Column type error: %v", err)
	}

	types := make([]reflect.Type, len(tt))
	for i, tp := range tt {
		st := tp.ScanType()
		if st == nil {
			return nil, fmt.Errorf("Scantype is null for column: %v", err)
		}
		switch st {
		case nullInt64Type:
			types[i] = jsonNullInt64Type
		case nullFloat64Type:
			types[i] = jsonNullFloat64Type
		case nullTimeType:
			types[i] = jsonNullTimeType
		default:
			types[i] = st
		}
	}

	values := make([]interface{}, len(tt))
	data := make(map[string][]interface{})

	for rows.Next() {
		for i := range values {
			values[i] = reflect.New(types[i]).Interface()
		}
		err = rows.Scan(values...)
		if err != nil {
			return nil, fmt.Errorf("Failed to scan values: %v", err)
		}
		for i, v := range values {
			data[columns[i]] = append(data[columns[i]], v)
		}
	}

	return json.Marshal(data)
}

希望对你有帮助!

英文:

Here is a better solution, using reflection. It handles types correctly (e.g. a string value of true won't erroneously be turned into a bool and so on.

It also handles possibly-null types (only tested with MySQL - you will probably need to modify it for other drivers).

package main
import (
&quot;database/sql&quot;
&quot;encoding/json&quot;
&quot;fmt&quot;
&quot;reflect&quot;
&quot;github.com/go-sql-driver/mysql&quot;
)
// Additional scan types returned by the MySQL driver. I haven&#39;t looked at
// what PostgreSQL does.
type jsonNullInt64 struct {
sql.NullInt64
}
func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
if !v.Valid {
return json.Marshal(nil)
}
return json.Marshal(v.Int64)
}
type jsonNullFloat64 struct {
sql.NullFloat64
}
func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
if !v.Valid {
return json.Marshal(nil)
}
return json.Marshal(v.Float64)
}
type jsonNullTime struct {
mysql.NullTime
}
func (v jsonNullTime) MarshalJSON() ([]byte, error) {
if !v.Valid {
return json.Marshal(nil)
}
return json.Marshal(v.Time)
}
var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
var nullTimeType = reflect.TypeOf(mysql.NullTime{})
// SQLToJSON takes an SQL result and converts it to a nice JSON form. It also
// handles possibly-null values nicely. See https://stackoverflow.com/a/52572145/265521
func SQLToJSON(rows *sql.Rows) ([]byte, error) {
columns, err := rows.Columns()
if err != nil {
return nil, fmt.Errorf(&quot;Column error: %v&quot;, err)
}
tt, err := rows.ColumnTypes()
if err != nil {
return nil, fmt.Errorf(&quot;Column type error: %v&quot;, err)
}
types := make([]reflect.Type, len(tt))
for i, tp := range tt {
st := tp.ScanType()
if st == nil {
return nil, fmt.Errorf(&quot;Scantype is null for column: %v&quot;, err)
}
switch st {
case nullInt64Type:
types[i] = jsonNullInt64Type
case nullFloat64Type:
types[i] = jsonNullFloat64Type
case nullTimeType:
types[i] = jsonNullTimeType
default:
types[i] = st
}
}
values := make([]interface{}, len(tt))
data := make(map[string][]interface{})
for rows.Next() {
for i := range values {
values[i] = reflect.New(types[i]).Interface()
}
err = rows.Scan(values...)
if err != nil {
return nil, fmt.Errorf(&quot;Failed to scan values: %v&quot;, err)
}
for i, v := range values {
data[columns[i]] = append(data[columns[i]], v)
}
}
return json.Marshal(data)
}

答案4

得分: 0

我认为你最好的选择是使用Golang中的json库。

import "encoding/json"

type JSONData struct {
  Values []float64 `json:"Values"`
  Dates []string `json:"Dates"`
}

我认为没有一种好的动态方法来实现这个,因为Golang没有办法将数据库列名和输出的JSON匹配起来。另外,我通常通过直接将类型发送到数据库库来编写数据库查询代码。

var tempDate string 
var tempValue float64
err := rows.Scan(&tempDate, &tempValue)
if err != nil {
       return err
}

如果你真的想自动完成这个操作,你可以研究一下Golang的代码生成。

英文:

I think the best option you have is to use json library from golang

import &quot;encoding/json&quot;
type JSONData struct {
Values []float64 `json:&quot;Values&quot;`
Dates []string `json:&quot;Dates&quot;`
}

I don't think there is a good way to do this dynamically, since golang has no way of matching up the database column name and the output'd json Also as a side note I usually write the db querying code by sending the type directly to the db library

var tempDate string 
var tempValue float64
err := rows.Scan(&amp;tempDate, &amp;tempValue)
if err != nil {
return err
}

If you really want to do this automatically you can look into golang code generation.

huangapple
  • 本文由 发表于 2017年3月14日 06:19:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/42774467.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定