ARCHIVE / 2018-09-29

Golang Json的回傳與畫出股市蠟燭圖

Published 2018-09-29

Programming & Software Engineering Finance & Blockchain

打造股票回測

Debug時間

---

這樣跳來跳去進度會不會超太快xd 剛剛的教學已經大致上完成一部分了，大家比較有問題的在要改直接改原始碼的話，就注意資料庫的ip和密碼和table表，還有在route的網址所對應的網址基本上應該是沒什麼難度 golang的時間轉timestamp

還有 golang回傳json的格式在有些網站的時候json格式並不會照你的方式走，不是你配合它就是它配合你(應該不可能xd

那麼接著早上繼續坐下去 - 架設伺服器回傳json - 畫折線圖

Highcharts

---

highcharts這次是我們主要用來顯示股票的插件這邊是改裝後的程式碼

html

<html>
<head>
    <script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
    <script src="https://code.highcharts.com/stock/highstock.js"></script>
    <script src="https://code.highcharts.com/stock/modules/drag-panes.js"></script>
    <script src="https://code.highcharts.com/stock/modules/exporting.js"></script>


<script type="text/javascript">

$.getJSON('http://localhost:9000/321', function (data) {

    // create the chart
    Highcharts.stockChart('container', {


        rangeSelector: {
            selected: 1
        },

        title: {
            text: 'AAPL Stock Price'
        },

        series: [{
            type: 'candlestick',
            name: 'AAPL Stock Price',
            data: data,
            dataGrouping: {
                units: [
                    [
                        'week', // unit name
                        [1] // allowed multiples
                    ], [
                        'month',
                        [1, 2, 3, 4, 6]
                    ]
                ]
            }
        }]
    });
});


</script>
</head>

<body>

    <div id="container" style="height: 400px; min-width: 310px"></div>


</body>
</html>

回傳Json

---

這邊的話還是依樣用查詢方式來做

func HttpFileHandler4(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Access-Control-Allow-Origin", "*")
    db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { //如果连接出错,e将不是nil的
        print("ERROR?")
        return
    }

    // 提醒一句, 运行到这里, 并不代表数据库连接是完全OK的, 因为发送第一条SQL才会校验密码 汗~!
    _, e2 := db.Query("select 1")
    if e2 == nil {
        println("DB OK")
        ttt,err := db.Exec("USE stock")
        if err != nil {
            print("ERROR?")
            return
        }
        fmt.Print(ttt)
        rows, e := db.Query("SELECT date,open,high,low,close,volume FROM `2015tw`;")
        if e != nil {
            fmt.Print("query error!!%v\n", e)

            return
        }
        fmt.Print(rows)
        if rows == nil {
            print("Rows is nil")   
            return
        }

        var first int
        first=0
        fmt.Fprintln(w,"[")
        for rows.Next() { //跟java的ResultSet一样,需要先next读取
            if first!=0{
                if rows.Next()!=false{
                    fmt.Fprintln(w,",")
                }
            }
            if first==0{
                first=1
            }


            stock := new(Stock)
            // rows貌似只支持Scan方法 继续汗~! 当然,可以通过GetColumns()来得到字段顺序

            row_err := rows.Scan(&stock.Date,&stock.Open,&stock.High,&stock.Low,&stock.Close,&stock.Volume)
            if row_err != nil {
                //print("Row error!!")
                fmt.Fprintln(w,"]")
                return
            }
            fmt.Fprint(w,"[")
            tm2, _ := time.Parse("2006-01-02 15:04:05", strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")

            //fmt.Println(fmt.Sprint((int64(tm2.Unix()))))

            //fmt.Print(int32(tm2.Unix()))
            //fmt.Println(strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")
            //fmt.Print(int32(tm2.Unix()))
            fmt.Fprint(w,fmt.Sprint((int64(tm2.Unix()*1000)))+",")比照
            fmt.Fprint(w,getFieldString(stock, "Open")+",")
            fmt.Fprint(w,getFieldString(stock, "High")+",")
            fmt.Fprint(w,getFieldString(stock, "Low")+",")
            fmt.Fprint(w,getFieldString(stock, "Close")+",")
            fmt.Fprint(w,getFieldString(stock, "Volume"))
            fmt.Fprintln(w,"]")

            // fmt.Fprintln(w,checkCount(&rows))
            // b, _ := json.Marshal(stock)
            // fmt.Println(string(b)) // 这里没有判断错误, 呵呵, 一般都不会有错吧
            //fmt.Fprint(w,string(b))
        }
        fmt.Fprintln(w,"]")

    }

}

整體程式碼

---

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    //"github.com/go-sql-driver/mysql"
    "github.com/gorilla/mux"
    "reflect"
    "time"
    "strings"
    //"strconv"
)
import _ "github.com/go-sql-driver/mysql"
var people []Person

type Person struct {
    ID      string   `json:"id,omitempty"`
    Name    string   `json:"name,omitempty"`
    Address *Address `json:"address,omitempty"`
}
type Stock struct {
    Date      string    `json:"date"`
    Open string `json:"open"`
    High   string `json:"high"`
    Low     string    `json:"low"`
    Close string `json:"close"`
    Volume   string `json:"volume"`
}
type Address struct {
    Country string `json:"country,omitempty"`
    City    string `json:"city,omitempty"`
}

func DefaultHandler(w http.ResponseWriter, r *http.Request) {
    fmt.Fprint(w, "Home")
    fmt.Fprint(w, "Home")
    fmt.Fprint(w, "Home")

}
// 定义一个结构体, 需要大写开头哦, 字段名也需要大写开头哦, 否则json模块会识别不了
// 结构体成员仅大写开头外界才能访问
type User struct {
    User      string    `json:"user"`
    Password string `json:"password"`
    Host   string `json:"host"`
}
func PeopleHandler(w http.ResponseWriter, r *http.Request) {

    w.Header().Set("Access-Control-Allow-Origin", "*")
    json.NewEncoder(w).Encode(people)

}

func PersonHandler(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Access-Control-Allow-Origin", "*")
    params := mux.Vars(r)
    for _, item := range people {
        if item.ID == params["id"] {
            json.NewEncoder(w).Encode(item)
        }
    }

}
func HttpFileHandler(response http.ResponseWriter, request *http.Request) {
    //fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
    http.ServeFile(response, request, "index.html")
}
func HttpFileHandler2(response http.ResponseWriter, request *http.Request) {
    //fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
    http.ServeFile(response, request, "details.html")
}
func HttpFileHandler3(response http.ResponseWriter, request *http.Request) {
    //fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
    http.ServeFile(response, request, "123.html")
}
func getFieldString(e *Stock, field string) string {
    r := reflect.ValueOf(e)
    f := reflect.Indirect(r).FieldByName(field)
    return f.String()
}

func checkCount(rows *sql.Rows) (count int) {
    for rows.Next() {
       err:= rows.Scan(&count)
       checkErr(err)
   }   
   return count
}

func checkErr(err error) {
   if err != nil {
       panic(err)
   }
}
func HttpFileHandler4(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Access-Control-Allow-Origin", "*")
    db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { //如果连接出错,e将不是nil的
        print("ERROR?")
        return
    }

    // 提醒一句, 运行到这里, 并不代表数据库连接是完全OK的, 因为发送第一条SQL才会校验密码 汗~!
    _, e2 := db.Query("select 1")
    if e2 == nil {
        println("DB OK")
        ttt,err := db.Exec("USE stock")
        if err != nil {
            print("ERROR?")
            return
        }
        fmt.Print(ttt)
        rows, e := db.Query("SELECT date,open,high,low,close,volume FROM `2015tw`;")
        if e != nil {
            fmt.Print("query error!!%v\n", e)

            return
        }
        fmt.Print(rows)
        if rows == nil {
            print("Rows is nil")   
            return
        }

        var first int
        first=0
        fmt.Fprintln(w,"[")
        for rows.Next() { //跟java的ResultSet一样,需要先next读取
            if first!=0{
                if rows.Next()!=false{
                    fmt.Fprintln(w,",")
                }
            }
            if first==0{
                first=1
            }


            stock := new(Stock)
            // rows貌似只支持Scan方法 继续汗~! 当然,可以通过GetColumns()来得到字段顺序

            row_err := rows.Scan(&stock.Date,&stock.Open,&stock.High,&stock.Low,&stock.Close,&stock.Volume)
            if row_err != nil {
                //print("Row error!!")
                fmt.Fprintln(w,"]")
                return
            }
            fmt.Fprint(w,"[")
            tm2, _ := time.Parse("2006-01-02 15:04:05", strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")

            //fmt.Println(fmt.Sprint((int64(tm2.Unix()))))

            //fmt.Print(int32(tm2.Unix()))
            //fmt.Println(strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")
            //fmt.Print(int32(tm2.Unix()))
            fmt.Fprint(w,fmt.Sprint((int64(tm2.Unix()*1000)))+",")
            fmt.Fprint(w,getFieldString(stock, "Open")+",")
            fmt.Fprint(w,getFieldString(stock, "High")+",")
            fmt.Fprint(w,getFieldString(stock, "Low")+",")
            fmt.Fprint(w,getFieldString(stock, "Close")+",")
            fmt.Fprint(w,getFieldString(stock, "Volume"))
            fmt.Fprintln(w,"]")

            // fmt.Fprintln(w,checkCount(&rows))
            // b, _ := json.Marshal(stock)
            // fmt.Println(string(b)) // 这里没有判断错误, 呵呵, 一般都不会有错吧
            //fmt.Fprint(w,string(b))
        }
        fmt.Fprintln(w,"]")

    }

}

func getFromdatabase() {

    db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { 
        print("ERROR?")
        return
    }

    _, e2 := db.Query("select 1")
    if e2 == nil {
        println("DB OK")
        rows, e := db.Query("select user,password,host from mysql.user")
        if e != nil {
            fmt.Print("query error!!%v\n", e)
            return
        }
        if rows == nil {
            print("Rows is nil")   
            return
        }
        for rows.Next() { 
            user := new(User)

            row_err := rows.Scan(&user.User,&user.Password, &user.Host)
            if row_err != nil {
                print("Row error!!")
                return
            }
            b, _ := json.Marshal(user)
            fmt.Println(string(b)) 
        }

    }

}
func getFromdatabase2() {

    db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { //如果连接出错,e将不是nil的
        print("ERROR?")
        return
    }

    // 提醒一句, 运行到这里, 并不代表数据库连接是完全OK的, 因为发送第一条SQL才会校验密码 汗~!
    _, e2 := db.Query("select 1")
    if e2 == nil {
        println("DB OK")
        ttt,err := db.Exec("USE stock")
        if err != nil {
            print("ERROR?")
            return
        }
        fmt.Print(ttt)
        rows, e := db.Query("SELECT date,open,high,low,close,volume FROM `2015tw`;")
        if e != nil {
            fmt.Print("query error!!%v\n", e)

            return
        }
        fmt.Print(rows)
        if rows == nil {
            print("Rows is nil")   
            return
        }

        for rows.Next() { //跟java的ResultSet一样,需要先next读取

            stock := new(Stock)
            // rows貌似只支持Scan方法 继续汗~! 当然,可以通过GetColumns()来得到字段顺序

            row_err := rows.Scan(&stock.Date,&stock.Open,&stock.High,&stock.Low,&stock.Close,&stock.Volume)
            if row_err != nil {
                print("Row error!!")
                return
            }
            b, _ := json.Marshal(stock)
            fmt.Println(string(b)) // 这里没有判断错误, 呵呵, 一般都不会有错吧
        }

    }

}
//插入demo
func insert() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")

    stmt, err := db.Prepare(`INSERT user (Host,user,password) values (?,?,?)`)
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec("tony", 20, 1)
    if err != nil {
        panic(err)
    }
    id, err := res.LastInsertId()
    if err != nil {
        panic(err)
    }
    fmt.Println(id)
}
func create(name string) {

    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if err != nil {
        panic(err)
    }
    defer db.Close()



    _,err = db.Exec("USE stock")
    if err != nil {
        panic(err)
    }
    var  query string

    query="CREATE TABLE "+name+"  (data varchar(32) ,open float, high float, low float ,close float , volume float)"

    fmt.Println(query)

    _,err = db.Exec(query)
    if err != nil {
        panic(err)
    }
 }
 func update() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if err != nil {
        panic(err)
    }
    stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec(21, 2, 1)
    if err != nil {
        panic(err)
    }
    num, err := res.RowsAffected()
    if err != nil {
        panic(err)
    }
    fmt.Println(num)
}

//删除数据
func remove() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if err != nil {
        panic(err)
    }
    stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec(1)
    if err != nil {
        panic(err)
    }
    num, err := res.RowsAffected()
    if err != nil {
        panic(err)
    }
    fmt.Println(num)
}
func main() {
    route := mux.NewRouter()
    people = append(people, Person{ID: "1", Name: "sojib", Address: &Address{Country: "bangladesh", City: "Dhaka"}})
    people = append(people, Person{ID: "2", Name: "hasan", Address: &Address{Country: "china", City: "C"}})
    people = append(people, Person{ID: "3", Name: "kamal", Address: &Address{Country: "japan", City: "J"}})
    route.HandleFunc("/", DefaultHandler).Methods("Get")
    route.HandleFunc("/people", PeopleHandler).Methods("Get")
    route.HandleFunc("/people/{id}", PersonHandler).Methods("Get")
    route.HandleFunc("/index", HttpFileHandler)
    route.HandleFunc("/details", HttpFileHandler2)
    route.HandleFunc("/123", HttpFileHandler3)
    route.HandleFunc("/321", HttpFileHandler4)
    //route.Handle("/details", http.FileServer(http.Dir("/mnt/c/golangtmp")))
    //getFromdatabase2()
    //create("2015tw")
    //create("qwdwqd")
    log.Fatal(http.ListenAndServe(":9000", route))
}