mysql.go

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
	"strings"
)

const DbName = "go_test_db"

func handleError(err error) {
	if err != nil {
		panic(err)
	}
}

func connection() *sql.DB {
	db, err := sql.Open("mysql", "root:@/?charset=utf8")
	handleError(err)
	return db
}

func showDatabases(db *sql.DB) {
	rows, err := db.Query("SHOW DATABASES;")
	handleError(err)

	for rows.Next() {
		var name string
		err = rows.Scan(&name)
		handleError(err)
		fmt.Println(name)
	}
}

func dropDatabase(db *sql.DB, name string) {
	_, err := db.Exec("DROP DATABASE IF EXISTS " + name + ";")
	handleError(err)
}

func createDatabase(db *sql.DB, name string) {
	_, err := db.Exec("CREATE DATABASE " + name + " CHARACTER SET utf8;")
	handleError(err)
}

func useDatabase(db *sql.DB, name string) {
	_, err := db.Exec("USE " + name + ";")
	handleError(err)
}

func dropTable(db *sql.DB, name string) {
	_, err := db.Exec("DROP TABLE IF EXISTS " + name + ";")
	handleError(err)
}

func createTable(db *sql.DB, name string, fields map[string]string) {
	var f string
	for k, v := range fields {
		f += (k + " " + v + ",")
	}

	f = strings.TrimRight(f, ",") // remove trailing comma

	_, err := db.Exec("CREATE TABLE " + name + " ( " + f + " )")
	handleError(err)
}

func showTables(db *sql.DB) {
	rows, err := db.Query("SHOW TABLES;")
	handleError(err)

	for rows.Next() {
		var name string
		err = rows.Scan(&name)
		handleError(err)
		fmt.Println(name)
	}
}

func insert(db *sql.DB, sql string) {
	_, err := db.Exec(sql)
	handleError(err)
}

func query(db *sql.DB, sql string) *sql.Rows {
	rows, err := db.Query(sql)
	handleError(err)
	return rows
}

func main() {
	db := connection()
	defer db.Close()

	/* showDatabases(db) */

	dropDatabase(db, DbName)
	createDatabase(db, DbName)
	useDatabase(db, DbName)

	dropTable(db, "cars")
	createTable(db, "cars", map[string]string{
		"make":  "varchar(255)",
		"model": "varchar(255)",
		"color": "varchar(255)",
	})

	showTables(db)

	insert(db, "INSERT INTO cars (make, model, color) VALUES ('Acura', 'TSX', 'Black');")
	insert(db, "INSERT INTO cars (make, model, color) VALUES ('Audi', 'A6', 'Red');")
	insert(db, "INSERT INTO cars (make, model, color) VALUES ('Lexus', 'IS', 'Silver');")

	rows := query(db, "SELECT make, model, color FROM cars;")

	for rows.Next() {
		var mk, model, color string
		err := rows.Scan(&mk, &model, &color)
		handleError(err)
		fmt.Printf("%v, %v, %v\n", mk, model, color)
	}
}