/
Database.py
145 lines (119 loc) · 4.1 KB
/
Database.py
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
138
139
140
141
142
143
144
145
import sqlite3
from Crypto.Random import get_random_bytes
import Cryptor
from Common import *
# 连接数据库 Sqlite3
conn = sqlite3.connect(db_path)
cur = conn.cursor()
# 账户数据表
cur.execute('''
CREATE TABLE IF NOT EXISTS account
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
accountname TEXT NOT NULL ,
username TEXT NOT NULL ,
password BLOB NOT NULL ,
usedby TEXT DEFAULT '' ,
datetime TEXT NOT NULL
)''')
# 应用设置表
cur.execute('''
CREATE TABLE IF NOT EXISTS app_config
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL ,
password BLOB NOT NULL ,
pkey BLOB NOT NULL
)''')
conn.commit()
# 创建应用账户
def create_app_account(username, password):
c = conn.cursor()
used_username = c.execute('SELECT username From app_config WHERE username = ?', (username,)).fetchone()
if used_username:
return False
else:
# 随机生成128位密钥,用于密码加密和解密,与账户绑定
pk = get_random_bytes(16)
Cryptor.init(pk)
epw = Cryptor.encode(password)
c.execute(
'INSERT INTO app_config (username, password, pkey) VALUES (?, ?, ?)',
(username, epw, pk))
conn.commit()
login(username, password)
return True
# 是否已创建应用账户
def exist_app_account():
c = conn.cursor()
app_config = c.execute('SELECT * From app_config').fetchone()
return app_config is None
# 验证应用登陆信息
def login(username, password):
c = conn.cursor()
u, p, pk = c.execute('SELECT username, password, pkey FROM app_config WHERE username=?',
(username,)).fetchone()
Cryptor.init(pk)
p = Cryptor.decode(p)
if u == username and p == password:
return True
else:
return False
# 添加数据
def insert_account(accountname, username, password, usedby, datetime):
c = conn.cursor()
res = c.execute('SELECT accountname FROM account WHERE accountname=? and username=? and usedby=?',
(accountname, username, usedby)).fetchone()
if not res:
c.execute('INSERT INTO account (accountname, username, password, usedby, datetime) VALUES (?, ?, ?, ?, ?)',
(accountname, username, password, usedby, datetime))
conn.commit()
return True
else:
return False
# 检索所有数据
def select_all_account():
c = conn.cursor()
return c.execute('SELECT * FROM account').fetchall()
# 检索某账户名下所有数据
def select_account_by_account_name(account_name):
c = conn.cursor()
return c.execute('SELECT * FROM account WHERE accountname=?', (account_name,)).fetchall()
# 通过id检索数据
def select_account_by_id(data_id):
c = conn.cursor()
an, un, pw, ub = c.execute('SELECT accountname, username, password, usedby FROM account WHERE id=?',
(data_id,)).fetchone()
pw = Cryptor.decode(pw)
return an, un, pw, ub
# 更新id为data_id的数据
def update_account(data_id, an, un, pw, ub, date):
c = conn.cursor()
pw = Cryptor.encode(pw)
c.execute('UPDATE account '
'SET accountname = ?, username = ?, password = ?, usedby=?, datetime=? '
'WHERE id = ?',
(an, un, pw, ub, date, data_id))
conn.commit()
# 删除data_id的数据
def delete_account_by_id(account_id):
c = conn.cursor()
c.execute('DELETE FROM account WHERE id = ?', (account_id,))
conn.commit()
# 重置数据
def restore_accounts(accounts):
c = conn.cursor()
c.execute('DROP TABLE account')
cur.execute('''
CREATE TABLE IF NOT EXISTS account
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
accountname TEXT NOT NULL ,
username TEXT NOT NULL ,
password TEXT NOT NULL ,
usedby TEXT DEFAULT '' ,
datetime TEXT NOT NULL
)''')
for account in accounts:
insert_account(account[ACCOUNT_NAME], account[USERNAME], account[PASSWORD], account[USED_BY],
account[DATE])