请注意10,11,24行的代码，是本条博客的精华，逻辑并不难，就是有些小语法问题比较纠结，记录一下。
|
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
|
import json
import sqlite3
import re
import argparse
def Get(db_file):
conn = sqlite3.connect(db_file)
cur = conn.cursor()
print("5555555")
value1=(60)# this is must be ()
cur.execute("select * from exception where AGV_ID=(%s)" %(value1))
#cursor.execute("insert into exception values('%s', '%s','%s' ) " %(start_time ,ID ,infomation))
result= cur.fetchall()
print("result:",result)
for i in result:
print(i)
print("******************************888")
def get_agv_id(db_file):
try:
conn = sqlite3.connect(db_file)
cur = conn.cursor()
cur.execute("select * from exception where AGV_ID=51")
#print( cur.fetchall())
result= cur.fetchall()
for i in result:
print(i)
except sqlite3.Error,e:
print(e)
if __name__ == '__main__':
parser = argparse.ArgumentParser(description='check the information of db')
#parser.add_argument('-h', '--help', help='Statistics for abnormal information')
parser.add_argument('-n', '--name', help=' the db of name ')
args = vars(parser.parse_args())
db_name = args['name']
print("db_name:",db_name)
conn = sqlite3.connect('db_name')
cursor = conn.cursor()
Get('fitkits.db')
get_agv_id('fitkits.db')
conn.commit()
conn.close()
print('DONE!')
print("666")
|
补充：python + sqlite3 基本操作
连接数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import sqlite3
# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")
# 创建游标
cursor = conn.cursor()
...
# 关闭游标
cursor.close()
# 提交事物
conn.commit()
# 关闭连接
conn.close()
|
创建表
|
1
2
3
4
5
6
7
8
9
10
11
|
...
# 创建游标
cursor = conn.cursor()
# 创建表
sql = 'CREATE TABLE Student(id integer PRIMARY KEY autoincrement, Name varchar(30), Age integer)'
cursor.execute(sql)
# 提交事物
conn.commit()
...
|
插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
...
# 创建游标
cursor = conn.cursor()
# 插入数据
sql = "INSERT INTO Student(Name, Age) VALUES(\'love\', 22)"
cursor.execute(sql)
# 插入数据 2
data = ('love2', 2221) # or ['love2', 2221]
sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"
cursor.execute(sql, data)
# 提交事物
conn.commit()
...
|
查询数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
...
# 创建游标
cursor = conn.cursor()
# 查询数据
sql = "select * from Student"
values = cursor.execute(sql)
for i in values:
print(i)
# 查询数据 2
sql = "select * from Student where id=?"
values = cursor.execute(sql, (1,))
for i in values:
print('id:', i[0])
print('name:', i[1])
print('age:', i[2])
# 提交事物
conn.commit()
...
|
其他操作
自增字段起始位置
|
1
2
3
4
|
# 设置起始值为1
update sqlite_sequence SET seq = 0 where name = '表名';
# 设置全部表起始值为默认值
delete from sqlite_sequence where name='TableName'; --注意表名区分大小写
|
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/weixin_42528089/article/details/94395107