删除SQLite3部分内容

删除含有陌生人信息的ID

一个客户的需求
要求删除人脸识别记录里面陌生人的信息
当时的系统无法实现这个功能 他们做法就是telnet登入后台 然后删除整个数据库….
大概看了一下数据库的结构 写了个简单的删除方法

删除含有陌生人ID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
// /home/data/sqlite/RecordDB2.db
func DeleteDBinfo() {
	fmt.Println("delete DB info ")
	var dbFile string
	arch := runtime.GOOS

	if arch == "windows" {
		dbFile = "E:\\SQLiteStudio\\data\\RecordDB2.db"
		fmt.Println(arch + " " + dbFile)
	} else {
		dbFile = "/home/data/sqlite/RecordDB2.db"
		fmt.Println(arch + " " + dbFile)
	}
	//dbFile = "/mnt/e/SQLiteStudio/data/RecordDB2.db" //wsl

	db, err := sql.Open("sqlite3", dbFile)
	if err != nil {
		fmt.Println(err)
	}

	rows, err := db.Query("select * from RecordDB2")
	if err != nil {
		fmt.Println("dbQuery:", err)
	}

	var (
		RecordID int

		PersoIndex    int
		PersonID      string
		RecordPicture string

		RecordTime  int
		RecordType  int
		RecordPass  int
		Temperature float64
		RecordData  string
	)

	var dataMap = make(map[int]RecordDataStruct) //RecordData字段
	var imgMap = make(map[int]string)            //RecordPicture字段 图片路径
	for rows.Next() {
		err = rows.Scan(&RecordID, &PersoIndex, &PersonID, &RecordPicture, &RecordTime, &RecordType, &RecordPass, &Temperature, &RecordData)
		if err != nil {
			log.Fatal(err)
		}
		//----------
		var RecordDataobj RecordDataStruct
		jsonData := []byte(RecordData)
		err = json.Unmarshal(jsonData, &RecordDataobj)
		if err != nil {
			fmt.Println("### Unmarshal: ", err)
		}
		dataMap[RecordID] = RecordDataobj

		imgMap[RecordID] = RecordPicture
	}

	//------ 删除陌生人的数据库ID ------
	deleteName := "陌生人"
	for id, obj := range dataMap {
		if strings.EqualFold(deleteName, obj.PersonName) {
			//执行删除
			stmt, err := db.Prepare("delete from RecordDB2 where RecordID=?")
			if err != nil {
				log.Fatal("### Delete:", err)
			}
			_, err = stmt.Exec(id)
			if err != nil {
				log.Fatal("### Exec:", err)
			} else {
				log.Printf("delete id:%v %v %v\n", RecordID, RecordTime, RecordData)
			}
		}
	}

	//------删除id对应的图片------ 删除图片之后 web页面总是弹框 没找到弹框原因在哪儿
	// for _, v := range imgMap {
	// 	//执行删除图片
	// 	err := os.Remove(v)
	// 	if err != nil {
	// 		fmt.Println("file remove Error!")
	// 		fmt.Printf("%s\n", err)
	// 	} else {
	// 		fmt.Println("file remove OK!")
	// 	}
	// }

	//
	rows.Close()
	db.Close()

}

删除t1时间点之前的人员信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
package person

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"path"
	"runtime"
	"strings"
	"time"

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

// PersonDB表的字段
type PersonDBStruct struct {
	PersonIndex   int64     `json:"person_index"`
	PersonID      string    `json:"person_id"`
	PersonName    string    `json:"person_name"`
	PersonPicture string    `json:"person_picture"`
	CreateTime    time.Time `json:"create_time"`
}

// PersonDB2.db 返回值 k:PersonIndex v:PersonDBStruct结构体指针
func NewPersonDB() map[int64]*PersonDBStruct {
	var dbFile string
	arch := runtime.GOOS
	if arch == "windows" {
		dbFile = "E:\\SQLiteStudio\\data\\persondb-test\\PersonDB2.db"
		fmt.Println(arch + " " + dbFile)
	} else {
		dbFile = "/home/data/sqlite/PersonDB2.db"
		fmt.Println(arch + " " + dbFile)
	}

	db, err := sql.Open("sqlite3", dbFile)
	if err != nil {
		fmt.Println(err)
	}

	rows, err := db.Query("select  PersonIndex, PersonID , PersonName , PersonPicture , CreateTime  from PersonDB")
	if err != nil {
		fmt.Println("dbQuery:", err)
	}

	var (
		personIndex   int64
		personID      string
		PersonName    string
		personPicture string
		CreateTime    int64
	)

	objMap := make(map[int64]*PersonDBStruct) //k:PersonIndex v:结构体
	for rows.Next() {
		err = rows.Scan(&personIndex, &personID, &PersonName, &personPicture, &CreateTime)
		if err != nil {
			log.Fatal(err)
		}

		//-----时间处理-----
		//1970-01-01 08:00:00 见接口文档
		coreEpoch := time.Date(1970, time.January, 1, 8, 0, 0, 0, time.UTC)
		timestamp := coreEpoch.Add(time.Duration(CreateTime * int64(time.Second))) //s

		//-----图片路径处理-----
		picturePath, pictureFile := path.Split(personPicture)
		picturePath = strings.Trim(picturePath, "//") //去掉“//”
		personPicture = path.Join(picturePath, pictureFile)
		personPicture = "/" + personPicture //绝对路径

		//-----structMap-----
		objMap[personIndex] = &PersonDBStruct{
			PersonIndex:   personIndex,
			PersonID:      personID,
			PersonName:    PersonName,
			PersonPicture: personPicture,
			CreateTime:    timestamp,
		}

	}
	//
	rows.Close()
	db.Close()

	return objMap
}

//删除t1时间点之前的所有人员信息  精确到日
func DeletePersonInfo(t1 time.Time, objMap map[int64]*PersonDBStruct) {
	fmt.Printf("删除 %v时间之前的数据\n", t1)
	// dbFile := "E:\\SQLiteStudio\\data\\persondb-test\\PersonDB2.db"

	var dbFile string
	arch := runtime.GOOS
	if arch == "windows" {
		dbFile = "E:\\SQLiteStudio\\data\\persondb-test\\PersonDB2.db"
		fmt.Println(arch + " " + dbFile)
	} else {
		dbFile = "/home/data/sqlite/PersonDB2.db"
		fmt.Println(arch + " " + dbFile)
	}

	db, err := sql.Open("sqlite3", dbFile)
	if err != nil {
		fmt.Println(err)
	}
	for pid, obj := range objMap {
		t := obj.CreateTime
		t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, time.Local) //精确到日
		if t.Before(t1) {
			stmt, err := db.Prepare("DELETE FROM PersonDB where PersonID=?")
			if err != nil {
				log.Fatal("### Delete:", err)
			} else {
				log.Printf("delete %s from PersonDB\n", obj.PersonID)
			}

			if pid == (obj.PersonIndex) {
				//删除数据库内容
				_, err = stmt.Exec(obj.PersonID)
				if err != nil {
					log.Fatal("### Exec:", err)
				}
				//删除图片
				err = os.Remove(obj.PersonPicture)
				if err != nil {
					log.Println("### delete img", err)
				} else {
					fmt.Printf("remove %s OK\n", obj.PersonPicture)
				}
			}

		}
	}
}

源码地址

https://github.com/Aquarian-Age/sqlite3

时间处理

go-sqlite3-issuecomment-733474773

推荐文章