如何在不替换Golang中前一行的情况下将最后一个sql行添加到列表中 [英] How to append last sql row to a list without replacing previous rows in Golang

问题描述

此代码提供了AFAIK正确的JSON输出[{}，{}]，但是每行都被附加并替换了所有先前的行，因此结果仅显示最后一行的副本.

This code delivers AFAIK correct JSON output [{},{}], but each row is appended and replaces all previous rows, so the result shows only copies of the last row.

var rows *sql.Rows
rows, err = db.Query(query)
cols, _ := rows.Columns()
colnames, _ := rows.Columns()
vals := make([]interface{}, len(cols))

for i, _ := range cols {
   vals[i] = &cols[i]
}

m := make(map[string]interface{})
 
for i, val := range vals {
  m[colnames[i]] = val
}
    
list := make([]map[string]interface{}, 0)
for rows.Next() {
err = rows.Scan(vals...)
   list = append(list, m)
}
json, _ := json.Marshal(list)
fmt.Fprintf(w,"%s\n", json)

这是幕后循环遍历行的情况:

This is what happens behind the scenes looping through the rows:

循环1:{"ID":"1"，"NAME":"John}

loop 1: {"ID":"1","NAME": "John }

循环2:{"ID":"2"，"NAME":"Jane Doe"} {"ID":"2"，"NAME":"Jane Doe"}

loop 2: {"ID":"2","NAME": "Jane Doe"}{"ID":"2","NAME": "Jane Doe"}

循环3:{"ID":"3"，"NAME":"Donald Duck"} {"ID":"3"，"NAME":"Donald Duck"} {"ID":"3"，"NAME":"Donald Duck"}

loop 3: {"ID":"3","NAME": "Donald Duck"}{"ID":"3","NAME": "Donald Duck"}{"ID":"3","NAME": "Donald Duck"}

rows.Scan获取正确的值，但附加并替换所有先前的值.

The rows.Scan fetches the correct values, but it appends AND replaces all previous values.

最终的输出是这个

[{"ID":"3"，"NAME":"Donald Duck"}，{"ID":"3"，"NAME":"Donald Duck"}，{"ID":"3"，"NAME":"Donald Duck"}]

[{"ID":"3","NAME": "Donald Duck"},{"ID":"3","NAME": "Donald Duck"},{"ID":"3","NAME": "Donald Duck"}]

但是应该是这样:

[{"ID":"1"，"NAME":"John Doe"}}，{"ID":"2"，"NAME":"Jane Doe"}，{"ID":"3"，"NAME":"Donald Duck"}]

[{"ID":"1","NAME": "John Doe"},{"ID":"2","NAME": "Jane Doe"},{"ID":"3","NAME": "Donald Duck"}]

我在做什么错了?

您可能对此表示反对，但请解释原因.我仍然是Golang的新手，想学习.

You may downvote this, but please explain why. I am still a newbie on Golang and want to learn.

推荐答案

我修复了该问题，并用注释解释了您做错了什么:

I fixed it and explained with comments what you did wrong:

// 1. Query
var rows *sql.Rows
rows, err = db.Query(query)
cols, _ := rows.Columns()

// 2. Iterate
list := make([]map[string]interface{}, 0)
for rows.Next() {
    vals := make([]interface{}, len(cols))
    for i, _ := range cols {
        // Previously you assigned vals[i] a pointer to a column name cols[i].
        // This meant that everytime you did rows.Scan(vals),
        // rows.Scan would see pointers to cols and modify them
        // Since cols are the same for all rows, they shouldn't be modified.

        // Here we assign a pointer to an empty string to vals[i],
        // so rows.Scan can fill it.
        var s string
        vals[i] = &s

        // This is effectively like saying:
        // var string1, string2 string
        // rows.Scan(&string1, &string2)
        // Except the above only scans two string columns
        // and we allow as many string columns as the query returned us — len(cols).
    }

    err = rows.Scan(vals...)

    // Don't forget to check errors.
    if err != nil {
        log.Fatal(err)
    }

    // Make a new map before appending it.
    // Remember maps aren't copied by value, so if we declared
    // the map m outside of the rows.Next() loop, we would be appending
    // and modifying the same map for each row, so all rows in list would look the same.
    m := make(map[string]interface{})
    for i, val := range vals {
        m[cols[i]] = val
    }
    list = append(list, m)
}

// 3. Print.
b, _ := json.MarshalIndent(list, "", "\t")
fmt.Printf("%s\n", b)

别担心，当我还是初学者时，这也很难理解.

Don't worry, this was hard for me to understand when I was a beginner as well.

现在，很有趣了:

var list []map[string]interface{}
rows, err := db.Queryx(query)
for rows.Next() {
    row := make(map[string]interface{})
    err = rows.MapScan(row)
    if err != nil {
      log.Fatal(err)
    }
    list = append(list, row)
}

b, _ := json.MarshalIndent(list, "", "\t")
fmt.Printf("%s\n", b)

这与上面的代码相同，但使用的是 sqlx .简单一点，不是吗?

This does the same as the code above it, but with sqlx. A bit simpler, no?

sqlx是 database/sql 之上的扩展，具有直接将行扫描到映射和结构的方法，因此您不必手动进行此操作.

sqlx is an extension on top of database/sql with methods to scan rows directly to maps and structs, so you don't have to do that manually.

我认为您的模型作为结构看起来更好:

I think your model looks nicer as a struct:

type Person struct {
    ID int
    Name string
}

var people []Person
rows, err := db.Queryx(query)
for rows.Next() {
    var p Person
    err = rows.StructScan(&p)
    if err != nil {
        log.Fatal(err)
    }
    people = append(people, p)
}

你不觉得吗?

这篇关于如何在不替换Golang中前一行的情况下将最后一个sql行添加到列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！