Server-Side Scripting/SQL Databases/Go MySQL
Appearance
routes/lesson9.go
[edit | edit source]// 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
[edit | edit source]<!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
[edit | edit source]See Server-Side Scripting/Routes and Templates/Go to create a test environment.