postgresqlのデータベースから、golangで、データを何もかも取り込む

2022年11月28日

/*
C:\Users\ebata>psql -U postgres -h 192.168.0.23 -p 15432
Password for user postgres:
psql (13.4, server 12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 agent_db  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 yama_db   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(5 rows)

postgres=# \c agent_db
psql (13.4, server 12.5 (Debian 12.5-1.pgdg100+1))
You are now connected to database "agent_db" as user "postgres".
agent_db=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | user_list | table | postgres
 public | user_log  | table | postgres
(2 rows)


agent_db=# select * from user_list;
 id  | age |   type   | departure_name | departure_number | departure_lat | departure_lng |     arrival_name     | arrival_number | arrival_lat | arrival_lng
-----+-----+----------+----------------+------------------+---------------+---------------+----------------------+----------------+-------------+-------------
   0 |  51 | tourist  | 山口駅         |             9324 |   131.4815191 |     34.172788 | 藩庁門               |           1396 |    34.18463 |  131.471535
   1 |  59 | tourist  | 山口駅         |             9324 |   131.4815191 |     34.172788 | 五重塔               |           7136 |   34.190132 | 131.4734607

*/

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq" // ←これを追記
)

func main() {

	db, err := sql.Open("postgres",
		"user=postgres password=password host=192.168.0.23 port=15432 dbname=agent_db sslmode=disable")
	if err != nil {
		log.Fatal("OpenError: ", err)
	}
	defer db.Close()

	//rows, err := db.Query("select * from user_list")
	rows, err := db.Query("select id, age, type, departure_name, departure_number, departure_lat, departure_lng, arrival_name, arrival_number, arrival_lat, arrival_lng from user_list")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var id, age, departure_number, arrival_number int
	var type1, departure_name, arrival_name string
	var departure_lat, departure_lng, arrival_lat, arrival_lng float64

	for rows.Next() {
		//if err := rows.Scan(&dt); err != nil {
		if err := rows.Scan(&id, &age, &type1, &departure_name, &departure_number, &departure_lat, &departure_lng, &arrival_name, &arrival_number, &arrival_lat, &arrival_lng); err != nil {
			fmt.Println(err)
		}
		fmt.Println(id, age, type1, departure_name, departure_number, departure_lat, departure_lng, arrival_name, arrival_number, arrival_lat, arrival_lng)
	}
}

/*
出力結果

C:\Users\ebata\yamaguchi\src_try1\others>go run .
0 51 tourist 山口駅 9324 131.4815191 34.172788 藩庁門 1396 34.18463 131.471535
1 59 tourist 山口駅 9324 131.4815191 34.172788 五重塔 7136 34.190132 131.4734607
2 55 tourist 湯田温泉駅 7247 131.4606901 34.1597909 ザビエル記念聖堂 2570 34.1796352 131.4728818
3 60 tourist 湯田温泉駅 7247 131.4606901 34.1597909 ザビエル記念聖堂 2570 34.1796352 131.4728818
4 42 tourist 山口駅 9324 131.4815191 34.172788 五重塔 7136 34.190132 131.4734607

*/