Create a Map in Golang from database Rows
Look at using sqlx, which can do this a little more easily than the standard database/sql library:
places := []Place{}
err := db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
if err != nil {
fmt.Printf(err)
return
}
You could obviously replace []Place{}
with a []map[string]interface{}
, but where possible it is better to use a struct if you know the structure of your database. You won't need to undertake any type assertions as you might on an interface{}
.
You can create a struct that maintains the map key to the position of the []interface{} slice. By doing this, you do not need to create a predefined struct. For example:
IDOrder: 0
IsClose: 1
IsConfirm: 2
IDUser: 3
Then, you can use it like this:
// create a fieldbinding object.
var fArr []string
fb := fieldbinding.NewFieldBinding()
if fArr, err = rs.Columns(); err != nil {
return nil, err
}
fb.PutFields(fArr)
//
outArr := []interface{}{}
for rs.Next() {
if err := rs.Scan(fb.GetFieldPtrArr()...); err != nil {
return nil, err
}
fmt.Printf("Row: %v, %v, %v, %s\n", fb.Get("IDOrder"), fb.Get("IsConfirm"), fb.Get("IDUser"), fb.Get("Created"))
outArr = append(outArr, fb.GetFieldArr())
}
Sample output:
Row: 1, 1, 1, 2016-07-15 10:39:37 +0000 UTC
Row: 2, 1, 11, 2016-07-15 10:42:04 +0000 UTC
Row: 3, 1, 10, 2016-07-15 10:46:20 +0000 UTC
SampleQuery: [{"Created":"2016-07-15T10:39:37Z","IDOrder":1,"IDUser":1,"IsClose":0,"IsConfirm":1},{"Created":"2016-07-15T10:42:04Z","IDOrder":2,"IDUser":11,"IsClose":0,"IsConfirm":1},{"Created":"2016-07-15T10:46:20Z","IDOrder":3,"IDUser":10,"IsClose":0,"IsConfirm":1}]
Please see the full example below or at fieldbinding:
main.go
package main
import (
"bytes"
"database/sql"
"encoding/json"
"fmt"
)
import (
_ "github.com/go-sql-driver/mysql"
"github.com/junhsieh/goexamples/fieldbinding/fieldbinding"
)
var (
db *sql.DB
)
// Table definition
// CREATE TABLE `salorder` (
// `IDOrder` int(10) unsigned NOT NULL AUTO_INCREMENT,
// `IsClose` tinyint(4) NOT NULL,
// `IsConfirm` tinyint(4) NOT NULL,
// `IDUser` int(11) NOT NULL,
// `Created` datetime NOT NULL,
// `Changed` datetime NOT NULL,
// PRIMARY KEY (`IDOrder`),
// KEY `IsClose` (`IsClose`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
func main() {
var err error
// starting database server
db, err = sql.Open("mysql", "Username:Password@tcp(Host:Port)/DBName?parseTime=true")
if err != nil {
panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
defer db.Close()
// SampleQuery
if v, err := SampleQuery(); err != nil {
fmt.Printf("%s\n", err.Error())
} else {
var b bytes.Buffer
if err := json.NewEncoder(&b).Encode(v); err != nil {
fmt.Printf("SampleQuery: %v\n", err.Error())
}
fmt.Printf("SampleQuery: %v\n", b.String())
}
}
func SampleQuery() ([]interface{}, error) {
param := []interface{}{}
param = append(param, 1)
sql := "SELECT "
sql += " SalOrder.IDOrder "
sql += ", SalOrder.IsClose "
sql += ", SalOrder.IsConfirm "
sql += ", SalOrder.IDUser "
sql += ", SalOrder.Created "
sql += "FROM SalOrder "
sql += "WHERE "
sql += "IsConfirm = ? "
sql += "ORDER BY SalOrder.IDOrder ASC "
rs, err := db.Query(sql, param...)
if err != nil {
return nil, err
}
defer rs.Close()
// create a fieldbinding object.
var fArr []string
fb := fieldbinding.NewFieldBinding()
if fArr, err = rs.Columns(); err != nil {
return nil, err
}
fb.PutFields(fArr)
//
outArr := []interface{}{}
for rs.Next() {
if err := rs.Scan(fb.GetFieldPtrArr()...); err != nil {
return nil, err
}
fmt.Printf("Row: %v, %v, %v, %s\n", fb.Get("IDOrder"), fb.Get("IsConfirm"), fb.Get("IDUser"), fb.Get("Created"))
outArr = append(outArr, fb.GetFieldArr())
}
if err := rs.Err(); err != nil {
return nil, err
}
return outArr, nil
}
fieldbinding package:
package fieldbinding
import (
"sync"
)
// NewFieldBinding ...
func NewFieldBinding() *FieldBinding {
return &FieldBinding{}
}
// FieldBinding is deisgned for SQL rows.Scan() query.
type FieldBinding struct {
sync.RWMutex // embedded. see http://golang.org/ref/spec#Struct_types
FieldArr []interface{}
FieldPtrArr []interface{}
FieldCount int64
MapFieldToID map[string]int64
}
func (fb *FieldBinding) put(k string, v int64) {
fb.Lock()
defer fb.Unlock()
fb.MapFieldToID[k] = v
}
// Get ...
func (fb *FieldBinding) Get(k string) interface{} {
fb.RLock()
defer fb.RUnlock()
// TODO: check map key exist and fb.FieldArr boundary.
return fb.FieldArr[fb.MapFieldToID[k]]
}
// PutFields ...
func (fb *FieldBinding) PutFields(fArr []string) {
fCount := len(fArr)
fb.FieldArr = make([]interface{}, fCount)
fb.FieldPtrArr = make([]interface{}, fCount)
fb.MapFieldToID = make(map[string]int64, fCount)
for k, v := range fArr {
fb.FieldPtrArr[k] = &fb.FieldArr[k]
fb.put(v, int64(k))
}
}
// GetFieldPtrArr ...
func (fb *FieldBinding) GetFieldPtrArr() []interface{} {
return fb.FieldPtrArr
}
// GetFieldArr ...
func (fb *FieldBinding) GetFieldArr() map[string]interface{} {
m := make(map[string]interface{}, fb.FieldCount)
for k, v := range fb.MapFieldToID {
m[k] = fb.FieldArr[v]
}
return m
}
I haven't used it (yet), but I believe the "common" way to do what you are asking (more or less) is to use gorp.