Zgo - go-sqlite3

 

package main

import (
    "database/sql"
    "fmt"
    "os"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // Connect or create a SQLite database
    db, err := sql.Open("sqlite3", "test.db")
    if err != nil {
        fmt.Println("Error connecting:", err)
        return
    }
    defer db.Close()

    var version string
    err = db.QueryRow("SELECT SQLITE_VERSION()").Scan(&version)
    if err != nil {
        fmt.Println("Version:", err)
        return
    }

    fmt.Println("SQLite3 version:", version)
    os.Remove("test.db")
}

 

zzh@ZZHPC:/zdata/Github/ztest$ go run main.go
SQLite3 version: 3.45.1

 

package main

import (
    "database/sql"
    "fmt"
    "os"
    "strconv"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

var dbname = "zzh.db"

func insertData(db *sql.DB, description string) error {
    currentTime := time.Now().Format(time.RFC1123)
    stmt, err := db.Prepare("INSERT INTO book VALUES(NULL, ?, ?);")
    if err != nil {
        return err
    }

    _, err = stmt.Exec(currentTime, description)
    if err != nil {
        return err
    }

    return nil
}

func selectData(db *sql.DB, n int) (err error) {
    rows, err := db.Query("SELECT * FROM book WHERE id > ? ", n)
    if err != nil {
        return
    }
    defer func() {
        err = rows.Close()
        if err != nil {
            return
        }
    }()

    for rows.Next() {
        var id int
        var dt string
        var description string

        err = rows.Scan(&id, &dt, &description)
        if err != nil {
            return
        }

        date, err := time.Parse(time.RFC1123, dt)
        if err != nil {
            return err
        }
        fmt.Printf("%d %s %s\n", id, date, description)
    }

    return nil
}

func main() {
    // Delete database file
    os.Remove(dbname)

    // Connect and create the SQLite database
    db, err := sql.Open("sqlite3", dbname)
    if err != nil {
        fmt.Println("Error connecting:", err)
        os.Exit(1)
    }
    defer db.Close()

    // Create a table
    const create string = `
    CREATE TABLE IF NOT EXISTS book (
        id INTEGER NOT NULL PRIMARY KEY,
        time TEXT NOT NULL,
        description TEXT
    );`

    _, err = db.Exec(create)
    if err != nil {
        fmt.Println("Create table:", err)
        os.Exit(1)
    }

    // Insert 10 rows to the book table
    for i := 1; i < 11; i++ {
        description := "Description-" + strconv.Itoa(i)
        err = insertData(db, description)
        if err != nil {
            fmt.Println("Insert data:", err)
        }
    }

    // Select multiple rows
    err = selectData(db , 5)
    if err != nil {
        fmt.Println("Select 5:", err)
        os.Exit(1)
    }

    time.Sleep(time.Second)
    // Update data
    currentTime := time.Now().Format(time.RFC1123)
    _, err = db.Exec("UPDATE book SET time = ? WHERE id > ?", currentTime, 7)
    if err != nil {
        fmt.Println("Update:", err)
        os.Exit(1)
    }

    // Select multiple rows
    err = selectData(db, 8)
    if err != nil {
        fmt.Println("Select 8:", err)
        os.Exit(1)
    }

    // Delete data
    stmt, err := db.Prepare("DELETE FROM book WHERE id = ?")
    if err != nil {
        fmt.Println("Delete (Prepare):", err)
        os.Exit(1)
    }
    _, err = stmt.Exec(8)
    if err != nil {
        fmt.Println("Delete (Exec):", err)
        os.Exit(1)
    }

    // Select multiple rows
    err = selectData(db, 7)
    if err != nil {
        fmt.Println("Select 7:", err)
        os.Exit(1)
    }

    // Count rows in table
    count := -100
    err = db.QueryRow("SELECT count(*) AS count FROM book").Scan(&count)
    if err != nil {
        fmt.Println("Select count:", err)
        os.Exit(1)
    }
    fmt.Println("count(*):", count)
}

 

zzh@ZZHPC:/zdata/Github/ztest$ go run main.go
6 2024-06-11 20:44:06 +0800 CST Description-6
7 2024-06-11 20:44:06 +0800 CST Description-7
8 2024-06-11 20:44:06 +0800 CST Description-8
9 2024-06-11 20:44:06 +0800 CST Description-9
10 2024-06-11 20:44:06 +0800 CST Description-10
9 2024-06-11 20:44:07 +0800 CST Description-9
10 2024-06-11 20:44:07 +0800 CST Description-10
9 2024-06-11 20:44:07 +0800 CST Description-9
10 2024-06-11 20:44:07 +0800 CST Description-10
count(*): 9