Skip to content

Instantly share code, notes, and snippets.

@najeira
Created July 8, 2014 05:39
Show Gist options
  • Save najeira/451ebaeef0d9451d715b to your computer and use it in GitHub Desktop.
Save najeira/451ebaeef0d9451d715b to your computer and use it in GitHub Desktop.
Go MySQL example
package main
import (
"database/sql"
"errors"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
)
type Row map[string]sql.Scanner
type Rows struct {
*sql.Rows
columns []string
scanners []interface{}
row Row
}
type scanner struct {
rows *Rows
column string
}
func (s *scanner) Scan(src interface{}) error {
column, ok := s.rows.row[s.column]
if !ok {
return nil // ignore unknown columns
}
return column.Scan(src)
}
func NewRows(sqlRows *sql.Rows) (*Rows, error) {
columns, err := sqlRows.Columns()
if err != nil {
return nil, err
}
r := Rows{sqlRows, nil, nil, nil}
scanners := make([]interface{}, len(columns))
for i := range scanners {
scanners[i] = &scanner{rows: &r, column: columns[i]}
}
r.columns = columns
r.scanners = scanners
return &r, nil
}
func (r *Rows) Scan(row Row) error {
if row == nil {
return errors.New("row is nil")
}
r.row = row
err := r.Rows.Scan(r.scanners...)
r.row = nil
return err
}
func RowsToMap(sqlRows *sql.Rows, newRow func() Row) ([]Row, error) {
rows, err := NewRows(sqlRows)
if err != nil {
return nil, err
}
rets := make([]Row, 0)
for rows.Next() {
row := newRow()
err = rows.Scan(row)
if err != nil {
return nil, err
}
rets = append(rets, row)
}
return rets, nil
}
func main() {
// [username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
// user:password@tcp(localhost:5555)/dbname?tls=skip-verify&autocommit=true
db, err := sql.Open("mysql", "root:root@tcp(localhost:8889)/dbname?charset=utf8mb4")
if err != nil {
log.Fatal(err)
return
}
defer db.Close()
err = db.Ping()
if err != nil {
log.Fatal(err)
return
}
sqlRows, err := db.Query("SELECT * FROM `foo`")
if err != nil {
log.Fatal(err)
return
}
rows, err := RowsToMap(sqlRows, func() Row {
return Row{
"id": &sql.NullInt64{},
"memo": &sql.NullString{},
"created": &sql.NullInt64{},
}
})
if err != nil {
log.Fatal(err)
return
}
for _, row := range rows {
fmt.Println("{")
for k, v := range row {
fmt.Printf(" %v: %v,\n", k, v)
}
fmt.Println("}\n")
}
}
@najeira
Copy link
Author

najeira commented Jul 8, 2014

呼び出し元に map[string]sql.Scanner を生成させ、そこにデータを詰め込んでいく例。mapのスライスが得られる。

@najeira
Copy link
Author

najeira commented Jul 8, 2014

行ごとにカラム名を持っているのが、メモリの無駄ではある。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment