Learning Go By Example: Part 2
In my previous post, I described the beginning of my journey learning Go. In this post I will dive a little deeper. I continue to find terrific Go resources including this terrific post. The author states that Go is boring but that it’s a good thing. I also love the Go by Example website.
As I mentioned previously, I volunteered to become my daughter’s unofficial TA for her introductory programming course. A recent assignment was to write a Python program that read in stock symbols and prices, save them, and then print them out. My initial Python solution was pretty simplistic:
def main():
portfolio = []
while True:
symbol = input("Enter stock symbol: ")
if symbol.lower() == "quit":
break
price = float(input("Enter stock price: "))
stock = {"symbol": symbol, "price": price}
portfolio.append(stock)
for stock in portfolio:
print(stock)
if __name__ == "__main__":
main()I ignored error processing and stored currency as simple double (gasp). It was good enough to get to get the job done. As I mentioned previously, I think in Python so this program serves as my pseudocode. I then wrote the equivalent code in Go:
package main
import (
"bufio"
"fmt"
"os"
"strconv"
"strings"
)
type Stock struct {
symbol string
price float64
}
func main() {
reader := bufio.NewReader(os.Stdin)
var portfolio []Stock
for {
var input string
fmt.Print("Enter a stock symbol: ")
input, _ = reader.ReadString('\n')
symbol := strings.TrimSpace(input)
if strings.EqualFold(symbol, "quit") {
break
}
fmt.Print("Enter a stock price: ")
input, _ = reader.ReadString('\n')
input = strings.TrimSpace(input)
price, _ := strconv.ParseFloat(input, 64)
stock := Stock{symbol: symbol, price: price}
portfolio = append(portfolio, stock)
}
for _, stock := range portfolio {
fmt.Printf("%s %.2f\n", stock.symbol, stock.price)
}
}It was a little more verbose but fairly similar. One difference was the use of a struct instead of a simple dictionary:
type Stock struct {
symbol string
price float64
}As I tested the program I ran into some weird issues which were related to converting a non-numeric price values into floats. I wasn’t catching errors so I decided to add a little error processing:
var input string
...for {
fmt.Print("Enter a stock price: ")
input, _ = reader.ReadString('\n')
input = strings.TrimSpace(input)
price, err := strconv.ParseFloat(input, 64)
if err == nil {
stock := Stock{symbol: symbol, price: price}
portfolio = append(portfolio, stock)
break
} fmt.Println(err)}
This isn’t very pretty (in my opinion). Go doesn’t have exceptions. Instead, it uses additional return values to pass error codes. Because of that, we can’t chain function calls like this:
price =
strconv.ParseFloat(strings.TrimSpace(reader.ReadString('\n')), 64)We wind up needing to be more verbose as can be seen above.
Refactoring the code — Part 1
One thing I wanted to add to the program was database persistence. For the sake of simplicity, I decided to use SQLite for this example. The first thing I had to do was to install a go-sqlite3 package like this:
go get github.com/mattn/go-sqlite3The changes to the code were fairly straightforward:
package main
import (
"bufio"
"database/sql"
"fmt"
"os"
"strconv"
"strings"
_ "github.com/mattn/go-sqlite3"
)
type Stock struct {
symbol string
price float64
}
func main() {
db, _ := sql.Open("sqlite3", "./portfolio.db")
statement, _ := db.Prepare(
"CREATE TABLE IF NOT EXISTS stocks (id INTEGER PRIMARY KEY, symbol TEXT, price REAL)")
statement.Exec()
reader := bufio.NewReader(os.Stdin)
for {
var input string
fmt.Print("Enter a stock symbol: ")
input, _ = reader.ReadString('\n')
symbol := strings.TrimSpace(input)
if strings.EqualFold(symbol, "quit") {
break
}
for {
fmt.Print("Enter a stock price: ")
input, _ = reader.ReadString('\n')
input = strings.TrimSpace(input)
price, err := strconv.ParseFloat(input, 64)
if err == nil {
statement, _ = db.Prepare(
"INSERT INTO stocks (symbol, price) VALUES (?, ?)")
statement.Exec(symbol, price)
break
}
fmt.Println(err)
}
} var symbol string
var price string
rows, _ := db.Query("SELECT symbol, price FROM stocks")
for rows.Next() {
rows.Scan(&symbol, &price)
fmt.Println(symbol + " " + price)
}
db.Close()
}
As seen in the code snippet above, we added a few packages to the import statement. The import for the Sqlite seems a little odd:
_ "github.com/mattn/go-sqlite3"The “_” defines this as a “blank import” which are commonly used to stop Go complaining about an impor. That’s because we won’t be using it directly but are importing it to initialize the Sqlite database driver and create any package-level variables. This is done behind the scenes for us during the import.
One thing that I haven’t mentioned is that I’m a big JetBrains fan and use their Toolbox App which includes all their IDE products. As a language junkie, this is a godsend for me. The GoLand IDE automatically inserts the appropriate packages into the import statement as you type in code.
As can be seen in the snippet below, creating a database is fairly simple:
db, _ := sql.Open("sqlite3", "./portfolio.db")
statement, _ := db.Prepare(
"CREATE TABLE IF NOT EXISTS stocks (id INTEGER PRIMARY KEY, symbol TEXT, price REAL)")
statement.Exec()Let me first say that we would unlikely want to create a database in production code. We would more likely have a separate set of database migration scripts. I’ve included it here to illustrate some points in this example. Instead of creating a database, we would include code to connect to an existing database.
Executing SQL database operations consists of two steps. The first is to prepare a SQL statement and the second is to execute that statement. Inserting a stock symbol and price into the database is similar:
statement, _ = db.Prepare(
"INSERT INTO stocks (symbol, price) VALUES (?, ?)")
statement.Exec(symbol, price)One difference in the snippet above is that the exec function is passed values in include in the prepared SQL statement. The “?” character is a placeholder for these values.
The code snippet below shows how to get values from the database:
var symbol string
var price string
rows, _ := db.Query("SELECT symbol, price FROM stocks")
for rows.Next() {
rows.Scan(&symbol, &price)
fmt.Println(symbol + " " + price)
}Refactoring the code — Part 2
We’re not quite done. There are a few things that we should clean up. First, we should move all the database operations into separate functions:
func main() {
db := connectToDatabase("./portfolio.db")
reader := bufio.NewReader(os.Stdin)
for {
var input string
fmt.Print("Enter a stock symbol: ")
input, _ = reader.ReadString('\n')
symbol := strings.TrimSpace(input)
if strings.EqualFold(symbol, "quit") {
break
}
for {
fmt.Print("Enter a stock price: ")
input, _ = reader.ReadString('\n')
input = strings.TrimSpace(input)
price, err := strconv.ParseFloat(input, 64)
stock := Stock{symbol, price}
if err == nil {
insertIntoDatabase(db, stock)
break
}
fmt.Println(err)
}
}
stocks := getFromDatabase(db)
for _, stock := range stocks {
fmt.Println(stock.symbol, stock.price)
}
}The three functions look like this:
func connectToDatabase(dbName string) *sql.DB {
db, _ := sql.Open("sqlite3", dbName)
statement, _ := db.Prepare(
"CREATE TABLE IF NOT EXISTS stocks (id INTEGER PRIMARY KEY, symbol TEXT, price REAL)")
statement.Exec()
return db
}
func insertIntoDatabase(db *sql.DB, stock Stock) {
statement, _ := db.Prepare(
"INSERT INTO stocks (symbol, price) VALUES (?, ?)")
statement.Exec(stock.symbol, stock.price)
}
func getFromDatabase(db *sql.DB) [] Stock {
var stocks[] Stock
var symbol string
var price float64
rows, _ := db.Query("SELECT symbol, price FROM stocks")
for rows.Next() {
rows.Scan(&symbol, &price)
stock := Stock{symbol, price}
stocks = append(stocks, stock)
}
rows.Close()
return stocks
}The connectToDatabase function returns a pointer to a database object. The functions insertToDatabase and getFromDatabaseare passed this pointer as a parameter. This code works but still needs some additional work. Let’s add some error checking:
func connectToDatabase(dbName string) *sql.DB {
db, _ := sql.Open("sqlite3", dbName)
statement, err := db.Prepare(
"CREATE TABLE IF NOT EXISTS stocks (id INTEGER PRIMARY KEY, symbol TEXT, price REAL)")
checkDbErr(err)
statement.Exec()
return db
}
func insertIntoDatabase(db *sql.DB, stock Stock) {
statement, err := db.Prepare(
"INSERT INTO stocks (symbol, price) VALUES (?, ?)")
checkDbErr(err)
statement.Exec(stock.symbol, stock.price)
}
func getFromDatabase(db *sql.DB) [] Stock {
var stocks[] Stock
var symbol string
var price float64
rows, err := db.Query("SELECT symbol, price FROM stocks")
checkDbErr(err)
for rows.Next() {
rows.Scan(&symbol, &price)
stock := Stock{symbol, price}
stocks = append(stocks, stock)
}
return stocks
}
func checkDbErr(err error) {
if err != nil {
panic(err)
}
}Instead of ignoring the error code returned from the calls to db.Prepare and db.Query, we now pass it on to our checkDbError function. If a non-nil value is passed, the function panic is called. It’s built-in function that stops the ordinary flow of the program and prints out a stack trace.
A quick note about SQL injection: use of db.Prepare prevents it! We didn’t use it in the getFromDatabase function because we aren’t using any externally derived parameters in the SELECT statement.
One more thing
We still have a problem that we have to fix. If we run the program and enter duplicate stock symbols we see this:
Enter a stock symbol: AAPL
Enter a stock price: 385.31
Enter a stock symbol: AAPL
Enter a stock price: 385.42
Enter a stock symbol: quit
AAPL 385.31
AAPL 385.42The program allows for duplicate stock values which is not what we want. To fix this we have to implement a database upsert operation. An upsert is a combination of an insert and an update operation. It inserts a row if it doesn’t already exist in the database but updates it if it does.
We first have to modify the connectToDatabse function to create a table index to define uniqueness:
func connectToDatabase(dbName string) *sql.DB {
db, _ := sql.Open("sqlite3", dbName)
statement, err := db.Prepare(
"CREATE TABLE IF NOT EXISTS stocks (id INTEGER PRIMARY KEY, symbol TEXT, price REAL)")
checkDbErr(err)
statement.Exec()
statement, err = db.Prepare(
"CREATE UNIQUE INDEX IF NOT EXISTS idx_stocks_symbol ON stocks (symbol)")
checkDbErr(err)
statement.Exec()
return db
}The index in the code snippet above is defined on the symbol field. This forces all records to have unique values for the this field. We also need to change how the insertIntoDatabase function is implemented:
func insertIntoDatabase(db *sql.DB, stock Stock) {
statement, err := db.Prepare(
"INSERT INTO stocks (symbol, price) VALUES (?, ?) ON CONFLICT(symbol) DO UPDATE SET price=?")
checkDbErr(err)
statement.Exec(stock.symbol, stock.price, stock.price)
}In the code snippet above we’ve modified the INSERT statement to include a ON CONFLICT clause. If a conflict is found on the symbol field, the price field is updated with the given price value. You can see that the Exec function is passed an additional parameter to include this value. The ON CONFLICT syntax is also supported by PostgreSQL. MySQL has a similar ON DUPLICATE KEY clause.
Summary
This was another fairly simple example in which we learned a few more of Go’s features and idioms. Stay tuned for additional posts.
