Server-Side Scripting/SQL Databases/Go MySQL

routes/lesson9.go

// This program creates and displays a temperature database
// with options to insert, update, and delete records.
//
// References:
//  https://golang.org/doc/
//  https://tutorialedge.net/golang/golang-mysql-tutorial/
//  https://pkg.go.dev/database/sql

package routes

import (
    "database/sql"
    "html/template"
    "log"
    "net/http"
    "path/filepath"
    "strconv"
    _ "github.com/go-sql-driver/mysql"
)

func Lesson9(response http.ResponseWriter, request *http.Request) {
	response.Header().Set("Content-Type", "text/html; charset=utf-8")

	type Data struct {
		Table  template.HTML
	}
	
	result := ""

    switch request.Method {
        case "GET":
            result = processGet9()
        case "POST":
            result = processPost9(request)
        default:
            result = "Unexpected request method: " + request.Method
    }

	data := Data{template.HTML(result)}
	path := filepath.Join("templates", "lesson9.html")
	parsed, _ := template.ParseFiles(path)
	parsed.Execute(response, data)
}

func processGet9() string {
    checkDatabase()
    checkTable()
    result := getData()
    return result
}

func processPost9(request *http.Request) string {
    country := request.FormValue("country")
    temperature := request.FormValue("temperature")

    if !countryExists(country) {
        insertCountry(country, temperature)
    } else if (temperature != "") {
        updateCountry(country, temperature)
    } else {
        deleteCountry(country)
    }

    result := getData()
    return result
}

func checkDatabase() {
    database := "INFORMATION_SCHEMA"
    query := `
        SELECT COUNT(*) AS Count 
        FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME = 'Temperature';`

    rows := mysqlQuery(database, query)
    var count int
    rows.Next()
    rows.Scan(&count)

    if count == 1 {
        return
    }

    database = ""
    query = "CREATE DATABASE Temperature"
    mysqlExec(database, query)
}

func checkTable() {
    database := "INFORMATION_SCHEMA"
    query := `
        SELECT COUNT(*) AS Count
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'Temperature'
        AND TABLE_NAME = 'Countries';`

    rows := mysqlQuery(database, query)
    var count int
    rows.Next()
    rows.Scan(&count)
    
    if count == 1 {
        return
    }

    database = "Temperature"
    query = `
        CREATE TABLE Countries(
        ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        Country VARCHAR(35) UNIQUE NOT NULL,
        Temperature REAL NOT NULL);`
    mysqlExec(database, query)
}

func getData() string {
    database := "Temperature"
    query := "SELECT ID, Country, Temperature FROM Countries;"
    rows := mysqlQuery(database, query)

    result := "<table><tr><th>ID</th>"
    result += "<th>Country</th>"
    result += "<th>Temperature</th></tr>"

    for rows.Next() {
        var id int
        var country string
        var temperature float64
        err := rows.Scan(&id, &country, &temperature)
        if err != nil {
            log.Fatal(err)
        }

        result += "<tr><td>" + strconv.Itoa(id) + "</td>"
        result += "<td>" + country + "</td>"
        result += "<td>"+ strconv.FormatFloat(temperature, 'f', 1, 64) + "</td></tr>"
    }

    result += "</table>"
    return result;
}

func countryExists(country string) bool {
    database := "Temperature"
    query := `
        SELECT EXISTS(
        SELECT * FROM Countries
        WHERE Country = ?) AS Count;`

    rows := mysqlQuery(database, query, country)
    var count int
    rows.Next()
    rows.Scan(&count)
    
    return count == 1
}

func insertCountry(country string, temperature string) {
    database := "Temperature"
    query := `
        INSERT INTO Countries (Country, Temperature)
        VALUES(?, ?);`
    mysqlExec(database, query, country, temperature);
}

func updateCountry(country string, temperature string) {
    database := "Temperature"
    query := `
        UPDATE Countries
        SET Temperature = ?
        WHERE Country = ?;`
    mysqlExec(database, query, temperature, country);
}

func deleteCountry(country string) {
    database := "Temperature"
    query := `
        DELETE FROM Countries
        WHERE Country = ?;`
    mysqlExec(database, query, country);
}

func mysqlQuery(database string, query string, parameters ...interface{}) *sql.Rows {
    db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/" + database)
    if err != nil {
		log.Fatal(err)
	}
    defer db.Close()

    var result *sql.Rows
    if parameters == nil {
        result, err = db.Query(query)
    } else {
        result, err = db.Query(query, parameters...)
    }
    if err != nil {
        log.Fatal(err)
    }

    return result
 }

func mysqlExec(database string, query string, parameters ...interface{}) sql.Result {
    db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/" + database)
    if err != nil {
		log.Fatal(err)
	}
    defer db.Close()

    var result sql.Result
    if parameters == nil {
        result, err = db.Exec(query)
    } else {
        result, err = db.Exec(query, parameters...)
    }
    if err != nil {
        log.Fatal(err)
    }

    return result
 }

templates/lesson9.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Lesson 9</title>
    <link rel="stylesheet" href="styles.css">
</head>

<body>
    <h1>Temperature Data Entry</h1>
    <p>Enter country and temperature. Enter again to update.</p>
    <p>Enter country without temperature to delete.</p>
    <form method="POST">
        <p><label for="country">Country:</label>
            <input type="text" id="country" name="country" required>
        </p>
        <p><label for="stop">Temperature:</label>
            <input type="text" id="temperature" name="temperature">
        </p>
        <p><input type="submit" name="submit" value="Submit"></p>
    </form>
    <hr>
    {{.Table}}
</body>
</html>

Try It

See Server-Side Scripting/Routes and Templates/Go to create a test environment.