def update(self, account, logininfo):
"""
更新用户等登录信息
"""
sql = "SELECT LoginAccount FROM TBL_PR_APP_AUTHINFO WHERE AppName == '{}'" .format(self.app_name)
account_list = []
login_account = ''
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
while oSmt.executeStep():
login_account = oSmt.getColumn(0)
if not login_account.isNull():
login = login_account.getText("").strip()
account_list.append(login)
if not account_list:
sql = '''
INSERT INTO "TBL_PR_APP_AUTHINFO" (AppName, LoginAccount, AuthLogin) VALUES ('{}', '{}', '{}')
'''.format(self.app_name, account, logininfo)
self._conn.execute(sql)
update_sql = '''
UPDATE "TBL_PR_APP_AUTHINFO" SET AuthLogin = '******' WHERE AppName = '{}' AND LoginAccount = '{}'
'''.format(logininfo, self.app_name, account)
self._conn.execute(update_sql)
def update(self, uid, subappname, status, other_param='', remarks=''):
"""
更新某个子应用的进度状态和备注
:param uid: 账户唯一ID
:param subappname: 子应用名
:param status: 状态（0 该项目未开始, 1 该项目正常完成, 2 该项目取证异常）
:param other_param: 其他参数
:param remarks: 备注
:return: 无
"""
sql_select = """
select Status from TBL_PRCD_APP_CATCHSTATUS_INFO where Uid = '{}' and SubAppName = '{}' LIMIT 1
""".format(uid, subappname)
o_smt = pyMyDatabase.SQLiteStatement(self._conn, sql_select)
if not o_smt.executeStep():
sql = """
insert into "TBL_PRCD_APP_CATCHSTATUS_INFO"
("Uid", "SubAppName", "Status", "OtherParam", "Remarks")
values ('{}', '{}', '{}', '{}', '{}')
""".format(uid, subappname, status, other_param, remarks)
else:
sql = """
UPDATE TBL_PRCD_APP_CATCHSTATUS_INFO SET Status = '{}', OtherParam = '{}', Remarks = '{}'
WHERE SubAppName = '{}' and Uid = '{}'
""".format(status, other_param, remarks, subappname, uid)
self._sql_execute_try(sql)
def get_downloadinfo(self, uid, subappname):
"""
获取某取证项的others_param
:param uid: 唯一ID
:param subappname: 子取证项
:return: type:list
"""
sql_select = """
select Gid, DownloadPath, OtherParam
from TBL_PRCD_APP_DOWNLOADINFO where Uid = '{}' and SubAppName = '{}'
""".format(uid, subappname)
o_smt = pyMyDatabase.SQLiteStatement(self._conn, sql_select)
result = []
while o_smt.executeStep():
gid, download_path, other_param = o_smt.getColumn(
0), o_smt.getColumn(1), o_smt.getColumn(2)
gid = gid.getText("") if not gid.isNull() else None
download_path = download_path.getText(
"") if not download_path.isNull() else None
other_param = other_param.getText(
"") if not other_param.isNull() else None
result.append({
'gid': gid,
'download_path': download_path,
'other_param': other_param
})
return result
def init_progress(self, uid):
"""
初始化进度，开始之前，初始化之前已经进行的进度
:param uid: 用户唯一ID
:return: 无
"""
sql = """select SubAppName, Status, OtherParam, Remarks
from TBL_PRCD_APP_CATCHSTATUS_INFO where Uid = '{}'
""".format(uid)
o_smt = pyMyDatabase.SQLiteStatement(self._conn, sql)
task_progress = []
while o_smt.executeStep():
subappname, status = o_smt.getColumn(0), o_smt.getColumn(1)
other_param, remarks = o_smt.getColumn(2), o_smt.getColumn(3)
subappname = subappname.getText(
"") if not subappname.isNull() else None
status = status.getText("") if not status.isNull() else None
other_param = other_param.getText(
"") if not other_param.isNull() else None
remarks = remarks.getText("") if not remarks.isNull() else None
if subappname:
task_progress.append({
'subappname':
subappname,
'status':
status,
'remarks':
remarks,
'other_param':
json.loads(other_param) if other_param else []
})
self.pending = {uid: task_progress}
def get(self):
if not self._conn.tableExists('TBL_PRCD_APP_SELECTION_INFO'):
return None
sql = '''
SELECT ParamList FROM TBL_PRCD_APP_SELECTION_INFO WHERE AppName = '{}' and LoginAccount = '{}' LIMIT 1
'''.format(self.app_name, self.account)
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
if oSmt.executeStep():
paramlist = oSmt.getColumn(0)
return paramlist.getText("") if not paramlist.isNull() else None
return None
def get_param_list(self, uid, subappname):
sql_select = """select OtherParam
from TBL_PRCD_APP_DOWNLOADINFO where Uid = '{}' and SubAppName = '{}'
""".format(uid, subappname)
o_smt = pyMyDatabase.SQLiteStatement(self._conn, sql_select)
result = []
while o_smt.executeStep():
other_param = o_smt.getColumn(0)
other_param = other_param.getText(
"") if not other_param.isNull() else None
result.append(other_param)
return result
def select(self):
info_list = []
if not self._conn.tableExists(self.table_name):
self.log.info('table {} is not exist'.format(self.table_name))
return info_list
sql = '''
SELECT LoginAccount,AuthToken,AuthLogin FROM {} WHERE AppName == '{}' and Other == '1'
'''.format(self.table_name, self.app_name)
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
while oSmt.executeStep():
login_account = self._get_column_value(oSmt.getColumn(0))
auth_token = self._get_column_value(oSmt.getColumn(1))
auth_login = self._get_column_value(oSmt.getColumn(2))
info_list.append((login_account, auth_token, auth_login))
return info_list
def select_hb_id(self):
if not self.sqlconn.tableExists('TBL_PRCD_WECHAT_BILL_RECORDS'):
log.error('table "TBL_PRCD_WECHAT_BILL_RECORDS" not exist')
return
sql = "select out_trade_no from TBL_PRCD_WECHAT_BILL_RECORDS where classify_type = '2' and (bill_type = '6' or '1')"
smt = pyMyDatabase.SQLiteStatement(self.sqlconn, sql)
result = []
while smt.executeStep():
out_trade_no = smt.getColumn(0)
out_trade_no = out_trade_no.getText(
"") if not out_trade_no.isNull() else None
result.append(out_trade_no)
return result
def sql_select(self, sql):
try:
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
if oSmt.executeStep():
paramlist = oSmt.getColumn(0)
if not paramlist.isNull():
paramlist = paramlist.getText("")
else:
paramlist = None
return paramlist
return None
except Exception as e:
self.log.exception('sql select error. sql:{}'.format(sql),
exc_info=e)
return None
def update(self, percent, desc=""):
sql = '''
SELECT Percent FROM TBL_PRCD_APP_CATCHEPROGRESS_INFO WHERE AppName = '{}' AND LoginAccount = '{}'
'''.format(self.app_name, self.login_account)
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
if oSmt.executeStep():
# 避免进度回退
percent_old = oSmt.getColumn(0)
percent_old = int(percent_old.getText("")) if not percent_old.isNull() else 0
if percent < percent_old:
percent = percent_old
sql = '''
UPDATE TBL_PRCD_APP_CATCHEPROGRESS_INFO SET Percent = {}, Description = '{}'
WHERE AppName = '{}' AND LoginAccount = '{}'
'''.format(percent, desc, self.app_name, self.login_account)
self.excute_sql(sql)
def get(self):
if not self._conn.tableExists('TBL_PRCD_APP_SELECTION_INFO'):
return '', ''
sql = '''
SELECT ParamList, Remarks FROM TBL_PRCD_APP_SELECTION_INFO WHERE AppName = '{}' and LoginAccount = '{}' LIMIT 1
'''.format(self.app_name, self.account)
paramliststr = ''
interval = ''
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
if oSmt.executeStep():
paramliststr = oSmt.getColumn(0)
paramliststr = paramliststr.getText("") if not paramliststr.isNull() else None
interval = oSmt.getColumn(1)
interval = interval.getText("") if not interval.isNull() else None
return paramliststr, interval
def __init__(self, sqlite_path, app_name, login_account):
"""
初始化函数
:param sqlite_path: sqlite数据库路径
:param app_name: 应用名称
"""
self.app_name = app_name
self.login_account = login_account
self._conn = pyMyDatabase.SQLiteDatabase(sqlite_path, True)
if not self._conn.tableExists('TBL_PRCD_APP_CATCHEPROGRESS_INFO'):
self.excute_sql(TBCREATE_SQL)
sql = '''
SELECT * FROM TBL_PRCD_APP_CATCHEPROGRESS_INFO WHERE AppName == '{}' AND LoginAccount='{}'
'''.format(self.app_name, self.login_account)
oSmt = pyMyDatabase.SQLiteStatement(self._conn, sql)
if not oSmt.executeStep():
sql = TBINSERT_SQL.format(self.app_name, self.login_account)
self.excute_sql(sql)
def select_appnames(self):
"""
获取TBL_APPURL_MANAGER中需要进行取证的APP集合
:return:
"""
app_list = []
if not self._conn_cmf.tableExists("TBL_PRCD_APP_SELECTION_INFO"):
self.log.info('table TBL_PRCD_APP_SELECTION_INFO is not exist')
return app_list
sql = "select DISTINCT LoginAccount from TBL_PRCD_APP_SELECTION_INFO WHERE AppName == 'AppURLs' ORDER BY Id"
oSmt = pyMyDatabase.SQLiteStatement(self._conn_cmf, sql)
while oSmt.executeStep():
appname = self._get_column_value(oSmt.getColumn(0))
app_list.append(appname)
return app_list
def select_exception_urls(self, appname):
"""
筛选出异常的URL
:return:
"""
url_set = set()
if not self._conn_amf.tableExists("TBL_PRCD_URL_EXCEPTIONS_INFO"):
self.log.info('table TBL_PRCD_URL_EXCEPTIONS_INFO is not exist.')
return url_set
sql = "select AppName,URL from TBL_PRCD_URL_EXCEPTIONS_INFO WHERE AppName=='{}'".format(appname)
oSmt = pyMyDatabase.SQLiteStatement(self._conn_amf, sql)
while oSmt.executeStep():
appname = self._get_column_value(oSmt.getColumn(0))
url = self._get_column_value(oSmt.getColumn(1))
url_set.add((appname, url))
return url_set
def select_all_urls(self, appname):
"""
获取特定app下需要进行取证的URL
:return:
"""
# url_list = []
url_set = set()
if not self._conn_apps.tableExists("TBL_APPURL_MANAGER"):
self.log.info('table TBL_APPURL_MANAGER is not exist.')
return url_set
sql = "select sAppName,sUrls from TBL_APPURL_MANAGER WHERE sAppName=='{}'".format(appname)
oSmt = pyMyDatabase.SQLiteStatement(self._conn_apps, sql)
while oSmt.executeStep():
appname = self._get_column_value(oSmt.getColumn(0))
url = self._get_column_value(oSmt.getColumn(1))
# url_list.append((appname, url))
url_set.add((appname, url))
return url_set
def select_done_urls(self, appname):
"""
筛选出已经正常下载的资源集合
:return:
"""
# url_list = []
url_set = set()
if not self._conn_amf.tableExists("TBL_PRCD_URL_FILE_INFO"):
self.log.info('table TBL_PRCD_URL_FILE_INFO is not exist.')
return url_set
sql = "select AppName,URL from TBL_PRCD_URL_FILE_INFO WHERE AppName=='{}'".format(appname)
oSmt = pyMyDatabase.SQLiteStatement(self._conn_amf, sql)
while oSmt.executeStep():
appname = self._get_column_value(oSmt.getColumn(0))
url = self._get_column_value(oSmt.getColumn(1))
# url_list.append((appname, url))
url_set.add((appname, url))
return url_set
