golang操作sqlite，使用时要注意锁的问题

比如：查询出某10条数据，然后循环过程中对这10条数据依次进行更新，是不能成功。

        只有循环将处理的结果集存储成map，然后再循环map进行更新。sqlite锁机制

updatedata是我写的，其他是copy的。

其他问题：更新提示返回1，但是查看数据表，数据没有发生变化。

                 答：因为sqlite是本地数据库，拷贝了多份，查看错了

package DbModel

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"regexp"
	"strings"
)

const (
	dbDriverName = "sqlite3"
	dbName       = "./SpiderResult_op.db3"
)

type user struct {
	Username string
	Age      int
	Job      string
	Hobby    string
}

type Content struct {
	Id 		int
	Yc      int
	Yf		int
	Title      string
	Cnvd    string
	Cve      string
	Level    string
	Leak_type      string
	Company    string
	Threat_type      string
	Ctime    string
	Utime      string
	Leak_source    string
	Leak_desc      string
	Leak_notice    string
	Affected_entity      string
	Patch    string
	Refer_url      string
	Page_url    string
}

//func Updata() {
//	db, err := gorm.Open(sqlite.Open(dbName), &gorm.Config{})
//	if err != nil {
//		panic("failed to connect database")
//	}
//	var content Content
//	db.Order("id asc").Limit(3).Find(&content)
//	fmt.Println(content)
//}

func UpdateData() {
	db, err := sql.Open(dbDriverName, dbName)
	if checkErr(err) {
		return
	}
	//sql := `select * from Content order by id asc limit 10`
	sql := `select * from Content where id>=200000 order by id asc`
	stmt, err := db.Prepare(sql)
	if err != nil {
		fmt.Println(err)
	}
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	var res map[int]map[string]string
	res = make(map[int]map[string]string)

	for rows.Next() {
		var title, cnvd, cve, level, leak_type, company, threat_type, ctime, utime, leak_source, leak_desc,
		leak_notice, affected_entity, patch, refer_url, page_url string
		var id, yc, yf int

		rows.Scan(&id, &yc, &yf, &title, &cnvd, &cve, &level, &leak_type, &company, &threat_type, &ctime, &utime,
			&leak_source, &leak_desc, &leak_notice, &affected_entity, &patch, &refer_url, &page_url)

		var affected_entity_tmp string
		var patch_tmp string
		var refer_url_tmp string

		//fmt.Println(leak_desc)
		//fmt.Println(affected_entity)
		//fmt.Println(patch)
		//fmt.Println(refer_url)

		if strings.Contains(affected_entity, "ant-empty-description") && strings.Contains(affected_entity, "暂无数据") {
			affected_entity_tmp = "暂无数据"
		} else if strings.Contains(affected_entity, "</td><td>") {
			compileRegex := regexp.MustCompile("</td><td>(.*?)</td></tr>")
			matchArr := compileRegex.FindAllString(affected_entity,-1)
			for _,v := range matchArr {
				tmp  := strings.Replace(v, "</td><td>", "", -1)
				tmp = strings.Replace(tmp, "</td></tr>", "", -1)
				affected_entity_tmp = affected_entity_tmp + tmp + ";"
			}
		}
		//fmt.Println(affected_entity_tmp)

		if strings.Contains(patch, "class=\"default-prefix-link\"") {
			compileRegex := regexp.MustCompile("<a href=\"(.*?)\" class=\"default-prefix-link\">")
			matchArr := compileRegex.FindAllStringSubmatch(patch,-1)
			if len(matchArr) > 0 {
				patch_tmp = matchArr[0][1]
			} else {
				patch_tmp = ""
			}
		}
		//fmt.Println(patch_tmp)


		if strings.Contains(refer_url, "class=\"wordBreak_2tNvp\"") {
			compileRegex := regexp.MustCompile("class=\"wordBreak_2tNvp\">([\\s\\S]*?)</a></td>")
			matchArr := compileRegex.FindAllString(refer_url,-1)
			//fmt.Println(matchArr)
			for _,v := range matchArr {
				tmp  := strings.Replace(v, "class=\"wordBreak_2tNvp\">", "", -1)
				tmp = strings.Replace(tmp, "</a></td>", "", -1)
				tmp = strings.Replace(tmp, " ", "", -1)
				tmp = strings.Replace(tmp, "\n", "", -1)
				refer_url_tmp = refer_url_tmp + tmp + ";"
			}
		}
		//fmt.Println(refer_url_tmp)

		var res2 map[string]string
		res2 = make(map[string]string)

		if affected_entity_tmp != "" {
			res2["affected_entity"] = affected_entity_tmp
			res2["patch"] = patch_tmp
			res2["refer_url"] = refer_url_tmp

			res[id] = res2
		}

	}

	for k,v := range res {

		stmt, err := db.Prepare("update Content set affected_entity=?, patch=?, refer_url=?  where id=?")
		if err != nil {
			fmt.Println(err)
		}
		fmt.Println(k)
		fmt.Println(v["affected_entity"])
		fmt.Println(v["patch"])
		fmt.Println(v["refer_url"])


		res, err := stmt.Exec(v["affected_entity"], v["patch"], v["refer_url"], k)
		if err != nil {
			fmt.Println(err)
		}
		affect, err := res.RowsAffected()
		if err != nil {
			fmt.Println(err)
		}
		fmt.Println(affect)
	}
}

func main() {
	db, err := sql.Open(dbDriverName, dbName)
	if checkErr(err) {
		return
	}
	err = createTable(db)
	if checkErr(err) {
		return
	}
	// err = insertData(db, user{"zhangsan", 28, "engineer", "play football"})
	// if checkErr(err) {
	// 	return
	// }
	res, err := queryData(db, "zhangsan")
	if checkErr(err) {
		return
	}
	fmt.Println(len(res))
	for _, val := range res {
		fmt.Println(val)
	}
	r, err := delByID(db, 1)
	if checkErr(err) {
		return
	}
	if r {
		fmt.Println("delete row success")
	}
}

func createTable(db *sql.DB) error {
	sql := `create table if not exists "users" (
		"id" integer primary key autoincrement,
		"username" text not null,
		"age" integer not null,
		"job" text,
		"hobby" text
	)`
	_, err := db.Exec(sql)
	return err
}

func insertData(db *sql.DB, u user) error {
	sql := `insert into users (username, age, job, hobby) values(?,?,?,?)`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return err
	}
	_, err = stmt.Exec(u.Username, u.Age, u.Job, u.Hobby)
	return err
}

func queryData(db *sql.DB, name string) (l []user, e error) {
	sql := `select * from users`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return nil, err
	}
	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	var result = make([]user, 0)
	for rows.Next() {
		var username, job, hobby string
		var age, id int
		rows.Scan(&id, &username, &age, &job, &hobby)
		result = append(result, user{username, age, job, hobby})
	}
	return result, nil
}



func delByID(db *sql.DB, id int) (bool, error) {
	sql := `delete from users where id=?`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return false, err
	}
	res, err := stmt.Exec(id)
	if err != nil {
		return false, err
	}
	_, err = res.RowsAffected()
	if err != nil {
		return false, err
	}
	return true, nil
}

func checkErr(e error) bool {
	if e != nil {
		log.Fatal(e)
		return true
	}
	return false
}