您现在的位置是：首页 > 技术教程正文

Golang实践录：sqlite的使用

admin 阅读： 2024-03-19

后台-插件-广告管理-内容页头部广告（手机）

本文使用 Golang 对 sqlite3 数据库进行操作。

概述

Golang 操作数据库有统一的接口，当然也有xorm这样的库，笔者接触的项目不大，对sql自由组装有要求，同时也会将这些sql用于数据库客户端查询，因此偏向于使用原生的sql。

为方便起见，本文只针对sqlite进行连接、读写、事务的测试。理论上可以扩展到其它数据库的操作。

技术小结

引入的包有"database/sql"、_ "github.com/mattn/go-sqlite3"。
使用sql.Open打开数据库，对于sqlite3，不存在目标文件时，会自创并使用。
事务相关接口有：开始SQLDB.Begin()、提交tx.Commit()、回滚tx.Rollback()、结束SQLDB.Close()。

设计

为让测试代码接近业务逻辑，设计场景如下：

设2个数据表：一为版本号表，一为信息明细表。
版本号更新了（如通过http下载数据，数据中有版本号），才更新明细表。程序通过读取数据库表的版本号进行判断。
允许上述数据表为空或不存在，由于sqlite3是基于文件的，也允许sqlite文件不存在。
同时写上述2个数据表，同时成功了方认为成功，因此使用到事务机制。

源码分析

完整代码见文后，本节按实现功能列出要点。

连接数据库

func CreateSqlite3(dbname string, create bool) (sqldb *sql.DB, err error) { if create == false && !IsExist(dbname) { return nil, errors.New("open database failed: " + dbname + " not found") } sqldb, err = sql.Open("sqlite3", dbname) if err != nil { return nil, errors.New("open database failed: " + err.Error()) } err = sqldb.Ping() if err != nil { return nil, errors.New("connect database failed: " + err.Error()) } fmt.Println("connect to ", dbname, "ok") return }

读取版本号

读取版本号，如果不存在，则创建对应的表。

func readOrCreateDBTable(sqldb *sql.DB) (version, updateTime string) { needCreate := false sqlstr := fmt.Sprintf(`select version, updateTime from %v order by version desc limit 1`, tableVersion) fmt.Printf("run sql: [%v]\n", sqlstr) results, err := sqldb.Query(sqlstr) if err != nil { if strings.Contains(err.Error(), "no such table") { needCreate = true } else { fmt.Println("query error: ", err) return } } if !needCreate { for results.Next() { var item1, item2 sql.NullString err := results.Scan(&item1, &item2) if err != nil { fmt.Println("scan error: ", err) break } if !item1.Valid || !item2.Valid { continue } version = item1.String updateTime = item2.String } defer results.Close() } else { fmt.Println("not found table, will create it.") for _, item := range sqlarr { _, err := sqldb.Exec(item) if err != nil { fmt.Printf("Exec sql failed: [%v] [%v] \n", err, item) } } } return }

以事务方式入库

// 入库2个表，以事务方式 func insertDBBatch(gxList []InfoList_t, version string) (err error) { SQLDB, err := CreateSqlite3(dbServer, false) if err != nil { // fmt.Println(err.Error()) return err } var tx *sql.Tx tx, err = SQLDB.Begin() if err != nil { err = errors.New("begin sql error: " + err.Error()) return err } defer func() { if err != nil { err = errors.New("exec sql failed rollback: " + err.Error()) tx.Rollback() } else { err = nil tx.Commit() } // 延时一会，关闭 Sleep(1000) SQLDB.Close() }() err = insertDBVersion(tx, version) if err != nil { return } err = insertDBDetail(tx, gxList, version) if err != nil { return } return }

函数开始时，先调用SQLDB.Begin()开始事务，分别调用insertDBVersion和insertDBDetail入库，只有2者同时成功，才调用tx.Commit()提交事务，否则调用tx.Rollback()回滚。提交事务或回滚，通过Golang的defer机制实现，逻辑较清晰。

测试

测试日志如下：

go test -v -run TestSqlite 没有数据库文件 test of sqlte3... connect to foobar.db3 ok run sql: select version, updateTime from myVersion order by version desc limit 1 not found table, will create it. got db version [] update time [] connect to foobar.db3 ok insert db version [] at: [2023-12-02 10:42:18] insert result: --- PASS: TestSqlite (1.04s) PASS 已有数据但版本较新 test of sqlte3... connect to foobar.db3 ok run sql: [select version, updateTime from myVersion order by version desc limit 1] got db version [20231202] update time [2023-12-02T10:48:20Z] connect to foobar.db3 ok insert db version [20231203] at: [2023-12-02 10:48:47] insert result: --- PASS: TestSqlite (1.03s) PASS

附

完整代码

package test import ( "database/sql" "errors" "fmt" "os" "strings" "testing" "time" "webdemo/pkg/com" _ "github.com/mattn/go-sqlite3" ) var ( // 数据库文件名及表名 dbServer string = "foobar.db3" tableVersion string = "myVersion" tableList string = "myList" ) // 信息表结构体可对于json风格数据传输解析 type InfoList_t struct { Id int `json:"-"` Version string `json:"-"` Name string `json:"-"` City string `json:"-"` UpdateTime string `json:"-"` } var sqlarr []string = []string{ // 版本号 `CREATE TABLE "myVersion" ( "version" VARCHAR(20) NOT NULL, "updateTime" datetime DEFAULT "", PRIMARY KEY ("version") );`, // 信息表 `CREATE TABLE "myList" ( "id" int NOT NULL, "version" VARCHAR(20) NOT NULL, "name" VARCHAR(20) NOT NULL, "city" VARCHAR(20) NOT NULL, "updateTime" datetime DEFAULT "", PRIMARY KEY ("id") );`, } func IsExist(path string) bool { _, err := os.Stat(path) return err == nil || os.IsExist(err) } func Sleep(ms int) { time.Sleep(time.Duration(ms) * time.Millisecond) } func CreateSqlite3(dbname string, create bool) (sqldb *sql.DB, err error) { if create == false && !IsExist(dbname) { return nil, errors.New("open database failed: " + dbname + " not found") } sqldb, err = sql.Open("sqlite3", dbname) if err != nil { return nil, errors.New("open database failed: " + err.Error()) } err = sqldb.Ping() if err != nil { return nil, errors.New("connect database failed: " + err.Error()) } fmt.Println("connect to ", dbname, "ok") return } func readOrCreateDBTable(sqldb *sql.DB) (version, updateTime string) { needCreate := false sqlstr := fmt.Sprintf(`select version, updateTime from %v order by version desc limit 1`, tableVersion) fmt.Printf("run sql: [%v]\n", sqlstr) results, err := sqldb.Query(sqlstr) if err != nil { if strings.Contains(err.Error(), "no such table") { needCreate = true } else { fmt.Println("query error: ", err) return } } if !needCreate { for results.Next() { var item1, item2 sql.NullString err := results.Scan(&item1, &item2) if err != nil { fmt.Println("scan error: ", err) break } if !item1.Valid || !item2.Valid { continue } version = item1.String updateTime = item2.String } defer results.Close() } else { fmt.Println("not found table, will create it.") for _, item := range sqlarr { _, err := sqldb.Exec(item) if err != nil { fmt.Printf("Exec sql failed: [%v] [%v] \n", err, item) } } } return } func insertDBDetail(tx *sql.Tx, gxList []InfoList_t, version string) (err error) { tablename := tableList sqlstr := fmt.Sprintf(`DELETE FROM %v`, tablename) stmt, err := tx.Prepare(sqlstr) if err != nil { err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error()) return } _, err = stmt.Exec() if err != nil { err = errors.New("delete " + tablename + "failed: " + err.Error()) return } sqlstr = fmt.Sprintf(`INSERT OR REPLACE INTO %v (id, version, name, city, updateTime) VALUES (?, ?, ?, ?, ?)`, tablename) stmt, _ = tx.Prepare(sqlstr) for _, item := range gxList { // item.Id = idx item.Version = version item.UpdateTime = com.GetNowDateTime("YYYY-MM-DD HH:mm:ss") _, err = stmt.Exec(item.Id, item.Version, item.Name, item.City, item.UpdateTime) if err != nil { err = errors.New("insert " + tablename + "failed: " + err.Error()) return } } return // debug 制作bug // TODO 制作锁住，制作语法错误 err = errors.New("database is locked") return } func insertDBVersion(tx *sql.Tx, version string) (err error) { tablename := tableVersion sqlstr := fmt.Sprintf(`DELETE FROM %v`, tablename) stmt, err := tx.Prepare(sqlstr) if err != nil { err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error()) return } _, err = stmt.Exec() if err != nil { err = errors.New("delete " + tablename + " failed: " + err.Error()) return } sqlstr = fmt.Sprintf(`INSERT OR REPLACE INTO %v (version, updateTime) VALUES (?, ?)`, tablename) stmt, err = tx.Prepare(sqlstr) if err != nil { err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error()) return } updateTime := com.GetNowDateTime("YYYY-MM-DD HH:mm:ss") fmt.Printf("insert db version [%v] at: [%v]\n", version, updateTime) _, err = stmt.Exec(version, updateTime) if err != nil { err = errors.New("insert " + tablename + "failed: " + err.Error()) return } return } // 入库2个表，以事务方式 func insertDBBatch(gxList []InfoList_t, version string) (err error) { SQLDB, err := CreateSqlite3(dbServer, false) if err != nil { // fmt.Println(err.Error()) return err } var tx *sql.Tx tx, err = SQLDB.Begin() if err != nil { err = errors.New("begin sql error: " + err.Error()) return err } defer func() { if err != nil { err = errors.New("exec sql failed rollback: " + err.Error()) tx.Rollback() } else { err = nil tx.Commit() } // 延时一会，关闭 Sleep(1000) SQLDB.Close() }() err = insertDBVersion(tx, version) if err != nil { return } err = insertDBDetail(tx, gxList, version) if err != nil { return } return } // func makeData() (gxList []InfoList_t) { var tmp InfoList_t tmp.Id = 100 tmp.Version = "100" tmp.Name = "latelee" tmp.City = "梧州" gxList = append(gxList, tmp) tmp = InfoList_t{} tmp.Id = 250 tmp.Version = "250" tmp.Name = "latelee" tmp.City = "岑溪" gxList = append(gxList, tmp) return } // 读取基础信息，尝试创建表 func readDBVersion() (version, datetime string) { SQLDB, err := CreateSqlite3(dbServer, true) if err != nil { fmt.Println(err.Error()) return } version, datetime = readOrCreateDBTable(SQLDB) SQLDB.Close() return } func TestSqlite(t *testing.T) { fmt.Println("test of sqlte3...") // 1 尝试获取数据表的版本号（可能为空） version, datetime := readDBVersion() fmt.Printf("got db version [%v] update time [%v]\n", version, datetime) // 2 模拟业务：自定义版本号，较新时，才入库 myVer := "20231202" if myVer > version { data := makeData() err := insertDBBatch(data, myVer) fmt.Println("insert result: ", err) } else { fmt.Println("db is newest, do nothing") } }

标签:

声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。

在线投稿：投稿站长QQ：1888636

后台-插件-广告管理-内容页尾部广告（手机）