Go语言SQLite3：正确查询数据与检查表存在性的实践指南

package main

import (
    "fmt"
    "os"
    "time"

    "code.google.com/p/go-sqlite/go1/sqlite3"
)

func main() {
    dbname := "sqlite.db"
    // 延迟是为了在程序退出前能观察到输出，实际应用中可根据需要调整或移除
    defer time.Sleep(500 * time.Millisecond) 
    fmt.Printf("hello, world\n")

    // 确保每次运行时数据库文件都是新的，便于测试
    os.Remove(dbname)

    fe := FileExists(dbname)
    fmt.Printf("FileExists(dbname) returned: %t\n", fe)

    db, err := sqlite3.Open(dbname)
    if err != nil {
        fmt.Printf("failed to open database, error: %s\n", err.Error())
        return
    }
    defer db.Close() // 确保数据库连接关闭

    fmt.Printf("database ok\n")

    if !fe { // 如果文件不存在，则创建表并插入数据
        fmt.Printf("creating testtable...\n")
        err = db.Exec("CREATE TABLE testtable (id INTEGER PRIMARY KEY AUTOINCREMENT, text VARCHAR(200));")
        if err != nil {
            fmt.Printf("error creating table: %s\n", err.Error())
            return
        }
        fmt.Printf("success!\n")

        fmt.Printf("inserting something...\n")
        insertSql := `INSERT INTO testtable(text) VALUES("This is some random text to test it");`
        err = db.Exec(insertSql)
        if err != nil {
            fmt.Printf("Error while Inserting: %s\n", err.Error())
            return
        }
    }

    fmt.Printf("checking testtable...\n")
    // 准备查询语句
    CheckTableStmt, err := db.Prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='testtable';")
    if err != nil {
        fmt.Printf("failed to prepare statement, error: %s\n", err.Error())
        return
    }
    defer CheckTableStmt.Close() // 确保语句关闭

    // *** 关键：使用 Query() 而不是 Exec() 来获取结果集 ***
    rows, err := CheckTableStmt.Query()
    if err != nil {
        fmt.Printf("failed to query table, error: %s\n", err.Error())
        return
    }
    defer rows.Close() // 确保结果集关闭

    var tablename string
    foundTable := false

    // 遍历结果集
    for rows.Next() {
        err = rows.Scan(&tablename)
        if err != nil {
            fmt.Printf("Failed to scan variable, error: %s\n", err.Error())
            return
        }
        if tablename == "testtable" {
            foundTable = true
            break // 找到即退出
        }
    }

    // 检查遍历过程中是否有其他错误
    if err = rows.Err(); err != nil {
        fmt.Printf("Error during rows iteration: %s\n", err.Error())
        return
    }

    if foundTable {
        fmt.Printf("Table detected\n")
    } else {
        fmt.Printf("No table detected\n")
    }
}

// FileExists 辅助函数，检查文件是否存在
func FileExists(fn string) bool {
    _, err := os.Stat(fn)
    return !os.IsNotExist(err) // 更简洁的检查方式
}