class MyTest(unittest.TestCase):
def tearDown(self):
print('当前用例执行后清理...')
sql = 'delete from vir_host'
params = ()
self.dbutil.update(sql,params)
def setUp(self):
print('当前用例执行前初始化...')
self.dbutil = DBUtil('localhost','root','','ops_db')
@classmethod
def tearDownClass(self):
print('所有用例执行后的清理...')
@classmethod
def setUpClass(self):
print('所有用例执行前的初始...')
def test_update(self):
sql = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
params = ('127.0.0.1',u'测试','test','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')
self.dbutil.update(sql,params)
sql = 'select ip,purpose,type,state,root_pass,depart,owner,email,phone,memo from vir_host where ip = %s'
params = ('127.0.0.1')
result = self.dbutil.query(sql,params)
#print(result)
self.assertEqual('test',result[0][2])
def test_batchUpdate(self):
sql1 = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
params1 = ('127.0.0.1',u'测试1','test1','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')
sql2 = 'insert into vir_host(ip,purpose,type,state,root_pass,depart,owner,email,phone,memo) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
params2 = ('127.0.0.2',u'测试2','test2','active','123kissme',u'工业互联网','song','*****@*****.**','18638217761','haha')
sql_params = []
sql_param = {}
sql_param['sql'] = sql1
sql_param['params'] = (params1)
sql_params.append(sql_param)
sql_param2 ={}
sql_param2['sql'] = sql2
sql_param2['params'] = (params2)
sql_params.append(sql_param2)
self.dbutil.batchUpdate(sql_params)
sql = 'select count(1) from vir_host '
params = ()
result = self.dbutil.query(sql,params)
self.assertEqual(2,result[0][0])
parser.add_argument('-whereValue', help = 'used in update')
parser.add_argument('-column', help = 'used in delete arg')
parser.add_argument('-value', help = 'used in delete arg')
parser.add_argument('-columnList', help = 'used in query arg (use wildcard in quotes)', nargs = '*');
parser.add_argument('-name', help = 'used in insert arg')
parser.add_argument('-age', type = int, help = 'used in insert arg')
parser.add_argument('-club', help = 'used in insert arg')
args = parser.parse_args()
if args.path:
p = args.path
db = DBUtil(p)
if args.insert:
db.insert(args.name, args.age, args.club)
if args.delete:
db.delete(args.column, args.value)
if args.query:
print(args.columnList)
for i in db.query(args.columnList):
print(i)
if args.update:
db.update(args.setColumn, args.setValue, args.whereColumn, args.whereValue)
class UserInfoService(object):
'''
提供对数据库操作的接口
1.save(data)
2.save_flag(data)
3.save_read_record(data)
4.update(data)
5.update_flag(data)
6.update_all_flag(data)
7.get_all()
8.get_all_user_read()
9.get_all_user_flag(read_flag, share_flag)
12.get_register()
'''
def __init__(self):
'''初始化数据库信息'''
path = 'data/userinfo.db'
table = 'userinfo'
self.db = DBUtil(path, table)
def create_token_table(self):
''' create token table'''
userflag_sql = '''CREATE TABLE IF NOT EXISTS `tokens` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`member_id` int(11) NOT NULL,
`token` varchar(64) NOT NULL,
FOREIGN KEY(`member_id`) REFERENCES `userinfo`(`member_id`)
)'''
conn = self.db.get_conn()
self.db.create_table(conn, userflag_sql)
def save(self, data):
'''
保存一条用户信息
data = [(member_id (int), telephone (string),
balance(string), invite_code(string), coin(int),
teacher_id(int), device_code(string))]
'''
save_sql = '''INSERT INTO userinfo
(member_id, telephone, balance, coin, invite_code, teacher_id, device_code
)
VALUES
(?, ?, ?, ?, ?, ?, ?)
'''
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def save_token(self, data):
'''
保存一条用户标志
args: data = [(member_id(int), token(str))]
'''
save_sql = '''INSERT INTO tokens
(member_id, token) VALUES (?, ?)
'''
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def init_all_user_token(self):
self.create_token_table()
users = self.get_all()
for user in users:
data = [(user[0], "token")]
self.save_token(data)
print("insert one")
def save_flag(self, data):
'''
保存一条用户标志
args: data = [(member_id(int),)]
'''
save_sql = '''INSERT INTO userflag
(member_id) VALUES (?)
'''
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def save_read_record(self, data):
'''
保存一条用户阅读信息
arg: data = [(member_id(int), read_time(string), read_count(int))]
'''
save_sql = '''INSERT INTO userread
(member_id, read_time, read_count)
VALUES
(?, ?, ?)
'''
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def update(self, data):
'''
更新一条用户信息
data = [balance(string), coin(int), (member_id(int)]
'''
update_sql = '''UPDATE userinfo
SET balance=?, coin=?
WHERE member_id=?
'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
def update_user_info(self, member_id, device_code, token):
'''
update one user info
args: member_id, device_code, token
'''
update_sql = '''UPDATE userinfo
set device_code=? WHERE member_id=?'''
data = [(device_code, member_id)]
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
self.update_token([(token, member_id)])
def update_token(self, data):
'''
保存一条用户标志
args: data = [(token(str), member_id(int))]
'''
update_sql = '''UPDATE tokens
set token=? WHERE member_id=?'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
def update_flag(self, data):
'''
更新一条flag信息
data = [read_flag(int{0,1,2}), share_flag(int {0, 1})), (member_id(int)]
'''
update_sql = '''UPDATE userflag
SET read_flag=?, share_flag=?
WHERE member_id=?
'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
def update_all_flag(self):
'''
更新所有read_flag信息
'''
update_sql = ''' UPDATE userflag
SET read_flag=?
'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(0, )])
def delete(self, tel):
users = self.get_user_mobile(tel)
for user in users:
# update_sql = ''' DELETE FROM tokens WHERE member_id=?'''
# conn = self.db.get_conn()
# self.db.update(conn, update_sql, [(user[0],)])
update_sql = ''' DELETE FROM userflag WHERE member_id=?'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(user[0], )])
update_sql = ''' DELETE FROM userread WHERE member_id=?'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(user[0], )])
update_sql = ''' DELETE FROM userinfo WHERE member_id=?'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(user[0], )])
def get_all(self):
'''
查询所有用户信息
'''
sql = '''SELECT * FROM userinfo'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_all_tokens(self):
'''
查询所有用户信息
'''
update_sql = ''' DELETE FROM tokens WHERE id<?'''
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(67, )])
sql = '''SELECT * FROM tokens'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_all_user_read(self):
'''
查询所有read_record
'''
sql = '''SELECT * FROM userread'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_all_user_flag(self, read_flag, share_flag=1):
'''
查询所有user_flag
args: read_flag(0, 1, 2)
share_flag(0, 1)
'''
sql = '''SELECT * FROM userflag WHERE read_flag=? AND share_flag=?'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql, (read_flag, share_flag))
return res
def get_one(self, member_id):
'''
查询一条用户信息
arg: member_id (int)
'''
sql = '''SELECT * FROM userinfo WHERE member_id=?'''
conn = self.db.get_conn()
res = self.db.fetchone(conn, sql, uid)
if len(res) > 0:
return res[0]
else:
return None
def get_user_mobile(self, telephone):
'''
查询一条用户信息
arg: telephone (string)
'''
sql = '''SELECT * FROM userinfo WHERE telephone=?'''
conn = self.db.get_conn()
res = self.db.fetchone(conn, sql, telephone)
if len(res) > 0:
return res
else:
return []
def get_token(self, member_id):
sql = '''SELECT * FROM tokens WHERE member_id=?'''
conn = self.db.get_conn()
res = self.db.fetchone(conn, sql, member_id)
if len(res) > 0:
return res
else:
return []
def get_register(self):
'''
查询注册用户
'''
sql = ''' SELECT * FROM userinfo ui, userflag uf
WHERE ui.member_id = uf.member_id AND uf.share_flag=0
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
if len(res) > 0:
return res
else:
return []
def get_one_user(self):
'''
查询一条flag=0的用户信息
'''
sql = '''SELECT *
FROM userinfo ui, userflag uf
WHERE ui.member_id = uf.member_id AND uf.read_flag = 0 AND uf.share_flag=1
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
if len(res) > 0:
return res[random.randint(0, len(res) - 1)]
else:
return None
def get_all_already_read_user(self):
'''
查询已完成的用户
'''
sql = '''SELECT *
FROM userinfo ui, userflag uf
WHERE ui.member_id = uf.member_id AND uf.read_flag = 2 AND uf.share_flag=1
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_all_not_read_user(self):
'''
查询未完成的用户
'''
sql = '''SELECT *
FROM userinfo ui, userflag uf
WHERE ui.member_id = uf.member_id AND uf.read_flag = 0 AND uf.share_flag=1
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_all_reading_user(self):
'''
查询正在完成的用户
'''
sql = '''SELECT *
FROM userinfo ui, userflag uf
WHERE ui.member_id = uf.member_id AND uf.read_flag = 1 AND uf.share_flag=1
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql)
return res
def get_user_read_count(self, member_id):
'''
查询用户的阅读量
'''
time_ms = int(time.time())
time_local = time.localtime(time_ms)
time_format = "%Y-%m-%d"
time_str = time.strftime(time_format, time_local)
time_local = time.strptime(time_str, time_format)
time_ms = int(time.mktime(time_local))
#print("今日00:00对应毫秒:{}".format(time_ms))
sql = '''SELECT sum(read_count) FROM userread
WHERE member_id = ? AND read_time > ?
'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql, (member_id, time_ms))
if res[0][0]:
return res[0][0]
else:
return 0
def get_user_coin(self, coin):
'''
查询一定coin的用户
'''
sql = '''SELECT * FROM userinfo
WHERE coin >= ?'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql, (score, ))
return res
def get_user_balance(self, balance):
'''
查询一定balance的用户
'''
sql = '''SELECT * FROM userinfo
WHERE balance == ?'''
conn = self.db.get_conn()
res = self.db.fetchall(conn, sql, (balance, ))
return res
def get_time_str(self, time_ms, time_format="%Y-%m-%d"):
'''
毫秒-->指定格式
'''
time_local = time.localtime(time_ms)
time_str = time.strftime(time_format, time_local)
return time_str
class UserInfoService(object):
"""
UserInfo Service
"""
def __init__(self, init_flag=False):
"""
init
"""
path = 'data/userinfo.db'
self.query_result = ""
self.db = DBUtil(path, init_flag=init_flag)
def save(self, data):
"""
save one userinfo record
data = [( (uid (int), name (string), mobile (string),
father (string), balance (string), coin (int),
device_code (string), token (string), os (string)
brand (string), mac (string), android_id (string) )]
"""
save_sql = """INSERT INTO userinfo
(uid , username, mobile, father, balance, coin, device_code, token, os, brand, mac, android_id)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def save_flag(self, data):
"""
save one userflag record
args: data = [(uid(int),)]
"""
save_sql = """INSERT INTO userflag
(uid) VALUES (?)
"""
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def save_read_record(self, data):
"""
save one read record
arg: data = [(uid(int), read_time(string), read_count(int))]
"""
save_sql = """INSERT INTO userread
(uid, read_time, read_count)
VALUES
(?, ?, ?)
"""
conn = self.db.get_conn()
self.db.save(conn, save_sql, data)
def update(self, data):
"""
update one userinfo record
data = [
(balance (string), coin (int), token (string), device_code(string), uid (int))
]
"""
update_sql = """UPDATE userinfo
SET balance=?, coin=?, token=?, device_code=?
WHERE uid=?
"""
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
def update_read_flag(self, data):
"""
update one userflag record
data = [(read_flag(int{0,1,2}), (uid(int))]
"""
update_sql = """UPDATE userflag
SET read_flag=?
WHERE uid=?
"""
conn = self.db.get_conn()
self.db.update(conn, update_sql, data)
def clear_all_flag(self):
"""
clear all userflag record
"""
update_sql = """ UPDATE userflag
SET read_flag=?
"""
conn = self.db.get_conn()
self.db.update(conn, update_sql, [(0, )])
def get_all(self):
"""
query all userinfo records
"""
sql = """SELECT * FROM userinfo"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
return self.query_result
def get_all_user_read(self):
"""
query all userread
"""
sql = """SELECT * FROM userread"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
return self.query_result
def get_all_user_flag(self, read_flag):
"""
query all userinfo by read_flag
args: read_flag(0, 1, 2)
return: userinfo list
"""
sql = """SELECT * FROM userflag WHERE read_flag=?"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (read_flag, ))
return self.query_result
def get_one(self, uid):
"""
query one userinfo record by uid
arg: uid (int)
return one userinfo or None
"""
sql = """SELECT * FROM userinfo WHERE uid=?"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (uid, ))
if len(self.query_result) > 0:
return self.query_result[0]
else:
return None
def get_user_mobile(self, mobile):
"""
query one userinfo record by mobile
arg: mobile (string)
:return one userinfo or None
"""
sql = """SELECT * FROM userinfo WHERE mobile=?"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (mobile, ))
if len(self.query_result) > 0:
return self.query_result[0]
else:
return None
def get_one_user(self):
"""
query one userinfo record where read_flag=0
:return one userinfo or None
"""
sql = """SELECT * FROM userinfo ui, userflag uf
WHERE ui.uid = uf.uid AND uf.read_flag=0"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
if len(self.query_result) > 0:
return self.query_result[random.randint(0,
len(self.query_result) -
1)]
else:
return None
def get_all_already_read_user(self):
"""
query userinfo records where read_flag=1
:return userinfo list
"""
sql = """SELECT *
FROM userinfo ui, userflag uf
WHERE ui.uid = uf.uid AND uf.read_flag=2"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
return self.query_result
def get_all_not_read_user(self):
"""
query userinfo records where read_flag=0
:return userinfo list
"""
sql = """SELECT * FROM userinfo ui, userflag uf
WHERE ui.uid = uf.uid AND uf.read_flag=0"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
return self.query_result
def get_all_reading_user(self):
"""
query userinfo records where read_flag=1
:return userinfo list
"""
sql = """SELECT * FROM userinfo ui, userflag uf
WHERE ui.uid = uf.uid AND uf.read_flag = 1"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql)
return self.query_result
def get_user_read_count(self, uid):
"""
query total read count by uid
:return read_count (int)
"""
time_ms = int(time.time())
time_local = time.localtime(time_ms)
time_format = "%Y-%m-%d"
time_str = time.strftime(time_format, time_local)
time_local = time.strptime(time_str, time_format)
time_ms = int(time.mktime(time_local))
sql = """SELECT sum(read_count) FROM userread
WHERE uid = ? AND read_time > ?
"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (uid, time_ms))
if self.query_result[0][0]:
return self.query_result[0][0]
else:
return 0
def get_user_coin(self, coin):
"""
query userinfo records where coin >= coin
"""
sql = """SELECT * FROM userinfo
WHERE coin >= ?"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (coin, ))
return self.query_result
def get_user_balance(self, balance):
"""
query userinfo records where balance >= balance
:return userinfo list
"""
sql = """SELECT * FROM userinfo
WHERE balance >= ?"""
conn = self.db.get_conn()
self.query_result = self.db.fetchall(conn, sql, (balance, ))
return self.query_result
def get_time_str(self, time_ms, time_format="%Y-%m-%d"):
"""
transfer the time_ms to some format
:return format_time string
"""
time_local = time.localtime(time_ms)
time_str = time.strftime(time_format, time_local)
return time_str