GO: Return map from SQL query

Question

I am querying a mysql database in a GO function and want to return key value pairs in a map but can't quite figure out how to accomplish this. So far I have this function:

func GetData(callIds []string) map[string]Records {
    //db insert
    db, err := sql.Open("mysql", mySql)
    if err != nil {
        fmt.Printf(err.Error())
    }
    defer db.Close()
    //db query
    var foo string
    err = db.QueryRow("select foo from bardata where callId = %v", 1).Scan(&foo)
    if err != nil {
        fmt.Printf(err.Error())
    }
    fmt.Println(foo)

    return nil

I want to return a map with the key being callId and value being foo for each row returned from the query.

You have your function returning a map[string]Records but the thing you're fetching from the DB is a string, not a Records, whatever that is. What's supposed to happen there? — hobbs
– hobbs, Commented Jan 2, 2016 at 18:37

hobbs · Accepted Answer · 2016-01-02 18:44:12Z

First, you need to build up your query. As it is, you're not even using your function input. Since we have a variable number of arguments, we need to do a little work to construct the right number of placeholders:

query := `select callid, foo from bardata where callid in (` +
    strings.Repeat(`?,`, len(callIds) - 1) + `?)`

then, execute with the values passed in:

rows, err := db.Query(query, callIds...)
if err != nil {
    // handle it
}
defer rows.Close()

then collect the results:

ret := map[string]string{}
for rows.Next() {
    var callid, foo string
    err = rows.Scan(&callid, &foo)
    if err != nil {
        // handle it
    }
    ret[callid] = foo
}

return ret

Caveats:

This will cause a placeholder mismatch error if callIds is an empty slice. If that's possible, then you need to detect it and handle it separately (maybe by returning an error or an empty map — querying the DB shouldn't be necessary).
This returns a map[string]string where the values are whatever "foo" is. In your question you have the function returning a map[string]Records but there's no information about what a Records might be or how to fetch one.
You might want to handle sql.ErrNoRows differently from other errors.

user11297642 · Accepted Answer · 2019-04-02 07:11:17Z

0

package main

import (
    "fmt"
    "github.com/bobby96333/goSqlHelper"
)

func main(){
    fmt.Println("hello")
    conn,err :=goSqlHelper.MysqlOpen("user:password@tcp(127.0.0.1:3306)/dbname")
    checkErr(err)
    row,err := conn.QueryRow("select * from table where col1 = ? and  col2 = ?","123","abc")
    checkErr(err)
    if *row==nil {
        fmt.Println("no found row")
    }else{
        fmt.Printf("%+v",row)
    }
}

func checkErr(err error){
    if err!=nil {
        panic(err)
    }
}

output:

&map[col1:abc col2:123]

answered Apr 2, 2019 at 7:11

user11297642

112 bronze badges

1 Comment

barbsan Over a year ago

Add some explanation to improve quality of your answer

Collectives™ on Stack Overflow

GO: Return map from SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related