Go实战--go语言操作sqlite数据库(The way to go)
生命不止,继续 go go go !!! 继续与大家分享,go语言的实战,今天介绍的是如何操作sqlite数据库。 何为sqlite3? 最主要的是,sqlite是一款轻型的数据库 官网: database/sql包 The sql package must be used in conjunction with a database driver 很清晰吧,需要我们自己提供一个database driver。当然,我们可以在github上找到相关的sqlite3的driver,稍后介绍。 下面介绍接个数据相关的操作: Open func Open(driverName,dataSourceName string) (*DB,error)
需要提供两个参数,一个driverName,一个数据库的名。 Prepare func (db *DB) Prepare(query string) (*Stmt,error)
Prepare creates a prepared statement for later queries or executions. Stmt func (*Stmt) Exec func (s *Stmt) Exec(args ...interface{}) (Result,error)
Exec executes a prepared statement with the given arguments and returns a Result summarizing the effect of the statement. Result type Result interface {
// LastInsertId returns the integer generated by the database
// in response to a command. Typically this will be from an
// "auto increment" column when inserting a new row. Not all
// databases support this feature,and the syntax of such
// statements varies.
LastInsertId() (int64,error)
// RowsAffected returns the number of rows affected by an
// update,insert,or delete. Not every database or database
// driver may support this.
RowsAffected() (int64,error)
}
Query func (s *Stmt) Query(args ...interface{}) (*Rows,error)
查询,返回的Rows是什么鬼? Rows func (rs *Rows) Next() bool
Next prepares the next result row for reading with the Scan method func (rs *Rows) Scan(dest ...interface{}) error
Scan copies the columns in the current row into the values pointed at by dest. 介绍少不多了,下面介绍一个sqlite3的Driver: mattn/go-sqlite3 执行: go get -u github.com/mattn/go-sqlite3
下面要开始我们的实战了!!!! 创建数据库,创建表 //打开数据库,如果不存在,则创建
db,err := sql.Open("sqlite3","./test.db")
checkErr(err)
//创建表
sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT,username VARCHAR(64) NULL,departname VARCHAR(64) NULL,created DATE NULL ); `
db.Exec(sql_table)
新建一个数据库叫test.db,并在这个数据库中建一个表,叫做userinfo。 插入数据 stmt,err := db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
checkErr(err)
res,err := stmt.Exec("wangshubo","国务院","2017-04-21")
checkErr(err)
显示Prepare,然后Exec. 接下来,就不再赘述了,我们需要一个基本的sql知识。 补充:import中_的作用 To import a package solely for its side-effects (initialization),use the blank identifier as explicit package name:
import _ "lib/math"
当导入一个包时,该包下的文件里所有init()函数都会被执行。 最后献上全部代码: package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//打开数据库,如果不存在,则创建
db,"./foo.db")
checkErr(err)
//创建表
sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT,created DATE NULL ); `
db.Exec(sql_table)
// insert
stmt,"2017-04-21")
checkErr(err)
id,err := res.LastInsertId()
checkErr(err)
fmt.Println(id)
// update
stmt,err = db.Prepare("update userinfo set username=? where uid=?")
checkErr(err)
res,err = stmt.Exec("wangshubo_new",id)
checkErr(err)
affect,err := res.RowsAffected()
checkErr(err)
fmt.Println(affect)
// query
rows,err := db.Query("SELECT * FROM userinfo")
checkErr(err)
var uid int
var username string
var department string
var created time.Time
for rows.Next() {
err = rows.Scan(&uid,&username,&department,&created)
checkErr(err)
fmt.Println(uid)
fmt.Println(username)
fmt.Println(department)
fmt.Println(created)
}
rows.Close()
// delete
stmt,err = db.Prepare("delete from userinfo where uid=?")
checkErr(err)
res,err = stmt.Exec(id)
checkErr(err)
affect,err = res.RowsAffected()
checkErr(err)
fmt.Println(affect)
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
} (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |