【Go】データベース操作

Goでデータベースを操作（CREATE・SELECT・UPDATE・DELETE・SELECT）する記述をメモします。

CREATE文

func main() {
	//DB接続
	DbConnection, _ := sql.Open("sqlite3", "./example.sql")
	defer DbConnection.Close()

	//CREATE文
	createCmd := `CREATE TABLE IF NOT EXISTS person(
				name STRING,
				age INT
			)`
	_, err := DbConnection.Exec(createCmd)
	if err != nil {
		log.Fatalln(err)
	}
}

INSERT文

	insertCmd := "INSERT INTO person (name, age) VALUES (?, ?)"
	_, err = DbConnection.Exec(insertCmd, "John", 20)
	if err != nil {
		log.Fatalln(err)
	}

UPDATE文

	updateCmd := "UPDATE person SET age = ? WHERE name = ?"
	_, err = DbConnection.Exec(updateCmd, 30, "John")
	if err != nil {
		log.Fatalln(err)
	}

DELETE文

	deleteCmd := "DELETE FROM person WHERE name = ?"
	_, err = DbConnection.Exec(deleteCmd, "John")
	if err != nil {
		log.Fatalln(err)
	}

SELECT文(複数行)

var DbConnection *sql.DB

type Person struct {
	Name string
	Age  string
}

func main() {
	//DB接続
	DbConnection, _ := sql.Open("sqlite3", "./example.sql")
	defer DbConnection.Close()

	//この場合はテーブル名が固定の場合のSELECT文
	selectCmd := "SELECT * FROM person"

	//この場合はテーブル名を指定する場合のSELECT文
	tableName := "person"
	selectCmd := fmt.Sprintf("SELECT * FROM %s", tableName)
	
	//DBのデータを取得してStructに入れる
	rows, _ := DbConnection.Query(selectCmd)
	defer rows.Close()
	var people []Person
	for rows.Next() {
		var p Person
		err := rows.Scan(&p.Name, &p.Age)
		if err != nil {
			log.Fatalln(err)
		}
		people = append(people, p)
	}

	//Structから取り出して表示
	for _, p := range people {
		fmt.Println(p.Name, p.Age)
	}
}

SELECT文(一行のみ)

var DbConnection *sql.DB

type Person struct {
	Name string
	Age  string
}

func main() {
	//DB接続
	DbConnection, _ := sql.Open("sqlite3", "./example.sql")
	defer DbConnection.Close()

	//SELECT文
	oneSelectCmd := "SELECT * FROM person WHERE name = ?"
	row := DbConnection.QueryRow(oneSelectCmd, "John")
	var p Person
	err = row.Scan(&p.Name, &p.Age)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Fatalln("No row")
		} else {
			log.Fatalln(err)
		}
		log.Fatalln(err)
	}
	//１行表示
	fmt.Println(p.Name, p.Age)
}