やりたいこと
- SELECT文で取得したクエリをまるごとJSONにしたい
- sqlパッケージのscanで一行ずつ処理しているようで、ループの中で配列を作る必要がある（？）
- 配列からjsonパッケージで変換する
- 参考のコードはMSのサイトにのっているやつ
コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
package main import ( "database/sql" "encoding/json" "fmt" _ "github.com/lib/pq" ) const ( // Initialize connection constants. HOST = "127.0.0.1" DATABASE = "testdb" USER = "root" PASSWORD = "password" ) func checkError(err error) { if err != nil { panic(err) } } func main() { // Initialize connection string. var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, USER, PASSWORD, DATABASE) // Initialize connection object. db, err := sql.Open("postgres", connectionString) checkError(err) err = db.Ping() checkError(err) fmt.Println("Successfully created connection to database") // Read rows from table. var id int var name string var quantity int sql_statement := "SELECT * from inventory;" rows, err := db.Query(sql_statement) checkError(err) defer rows.Close() // 構造を定義 type data struct { ID int Name string Quantity int } // 配列の型を宣言 var fruit []data for rows.Next() { switch err := rows.Scan(&id, &name, &quantity); err { case sql.ErrNoRows: fmt.Println("No rows were returned") case nil: fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity) // 一行毎に配列を追加 fruit = append(fruit,data{ ID: id, Name: name, Quantity: quantity, }) default: checkError(err) } } fruitjson, _ := json.Marshal(fruit) fmt.Println(string(fruitjson)) } |
結果
|
1 2 3 4 5 6 7 |
yuta:~/postgre $ go run main.go Successfully created connection to database Data row = (1, banana, 150) Data row = (2, orange, 154) Data row = (3, apple, 100) Data row = (4, test, 100) [{"ID":1,"Name":"banana","Quantity":150},{"ID":2,"Name":"orange","Quantity":154},{"ID":3,"Name":"apple","Quantity":100},{"ID":4,"Name":"test","Quantity":100}] |
参考
https://docs.microsoft.com/ja-jp/azure/postgresql/connect-go