def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 存储过程 ############################ #
# 返回固定个数值
parmout = [tools.INT, tools.FLOAT]
results = db.callproc("select_test1", ["fad"], parmout)
print("parmout: ", parmout)
# 返回结果集, 可通过parmout[i].fetchall()获取
parmout = [tools.CURSOR]
results = db.callproc("select_test2", ["fad"], parmout)
print("results: ", parmout)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
print("return: ", results)
# storage结果打包成json
jsonData = tools.storage2Json(None)
return tools.response(0, '', jsonData)
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 结构化操作数据库 ############################ #
# 结构化查询select
entries1 = db.select("test",
what="*",
where="a='hehe'",
order="d desc",
limit=3)
# 结构化单行插入insert
entries2 = db.insert("test", a="sigal", b="n", c=0, d=10.1)
# 结构化多行插入multiple_insert
db.supports_multiple_insert = True
values = [{
"a": "muti1",
"b": "p",
"c": 6,
"d": 11.1
}, {
"a": "muti2",
"b": "q",
"c": 6,
"d": 11.2
}, {
"a": "muti3",
"b": "r",
"c": 6,
"d": 11.3
}]
entries3 = db.multiple_insert("test", values=values)
# 结构化更新update
entries4 = db.update("test", where="a='fad'", b='mn', c=2)
# 结构化删除delete
entries5 = db.delete("test", where="a='ferry'")
# ############################ 非结构化操作数据库, 可以执行复杂操作, 查询语句返回storage结果集, 其他返回影响行数 ############################ #
sql = """select m.a, m.b, m.c, m.d, n.f, n.g from test m, test2 n where m.a = n.e"""
entries6 = db.exec(sql)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# 结果打包成json
jsonData = tools.storage2Json(entries1)
return tools.response(0, '', jsonData)
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
cursor = db._db_cursor()
# ############################ 存储过程 ############################ #
declare = "declare @p1 INT declare @p2 DECIMAL(10,2) declare @ret INT"
exec = "exec @ret = select_test2 'fad',@p1 output,@p2 output"
select = "select @p1,@p2,@ret"
cursor.execute(f"%s %s %s" % (declare, exec, select))
result = cursor.fetchall() # 得到结果集
for i in result:
print(i)
while cursor.nextset():
result = cursor.fetchall()
for i in result:
print(i)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# storage结果打包成json
jsonData = tools.storage2Json(None)
return tools.response(0, '', jsonData)
def queryPayResult(access_token,requestId):
tools.querylog("queryPayResult：",'info')
sql = "select PaymentId from " + config.sql['assetsPlanPayTable'] + " where PaymentStatus = N'已执行'"
#print sql
datas = tools.database(sql)
i = 0
paymentIds = []
errortimes = 0
successtimes = 0
while i <len(datas):
paymentIds.append(datas[i][0])
i = i + 1
paymentIds = ','.join(paymentIds)
tools.querylog("paymentIds:"+paymentIds,'info')
#print paymentIds
re,data = openapi.getQueryPayResult(paymentIds,access_token,requestId)
tools.log(re,'info')
#测试用例1
#re = {u'msg': u'', u'data': u"{'status':'1','9E416A55-4221-40CE-BE8E-F988167F8367':'N'}", u'requestId': u'1511848724', u'ret': u'0'}
#data = {u'status': u'1',u'9E416A55-4221-40CE-BE8E-F988167F8367':u'N'}
#测试用例2
#re = {u"ret":u"13008(示例)",u"msg":u"access_token为空(示例)",u"data":u""}
#data = {u'status':u'',u'message':u''}
#测试用例3
#re = {u'msg': u'', u'data': u"{'status':'0'}", u'requestId': u'1511849902', u'ret': u'0'}
#data = {u'status': u'0'}
if(re["msg"] == ""):
if(data['status'] == '0'):
#未完成
errortimes = errortimes + 1
errorInfo = '未完成'
tools.querylog(errorInfo,'error')
elif(data['status'] == '1'):
successtimes =successtimes + 1
j = 0
#已完成，返回多条数据形如{status:'1','id1':'Y(成功)','id2':'F(失败)','id3':'R(退票)'}
while j < len(data)-1:
j = j + 1
if(data.values()[j] == 'Y'):
sql = "update " + config.sql['assetsPlanPayTable'] +" set PaymentStatus = N'成功' where PaymentId = '" + str(data.keys()[j]) + "'"
elif(data.values()[j] == 'N'):
sql = "update " + config.sql['assetsPlanPayTable'] +" set PaymentStatus = N'失败' where PaymentId = '" + str(data.keys()[j])+ "'"
elif(data.values()[j] == 'R'):
sql = "update " + config.sql['assetsPlanPayTable'] +" set PaymentStatus = N'失败' where PaymentId = '" + str(data.keys()[j])+ "'"
tools.databasesql(sql)
tools.querylog(data['message'] ,'info')
else:
errortimes = errortimes + 1
errorInfo = re['msg']
tools.querylog(errorInfo,'error')
tools.querylog(str(successtimes) + " success(es)," + str(errortimes) + " fail(s)",'info')
return re,data
def assetsAdjust(access_token,requestId,TaskId):
#任务开始，维护task表
TaskStartTime= "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
tools.taskStartUpdate(TaskStartTime,TaskId)
sql = 'exec ' + config.sql['assetsAdjustSP'] + ' ' + str(TaskId)
datas = tools.database(sql)
if datas == []:
tools.log('there is no data in ABSPresale!','error')
return
errortimes = 0
successtimes = 0
assetsBagNo = datas[0][0]
sourceSystem = datas[0][1]
if sourceSystem =='ACC':
sourceSystem = '2'
elif sourceSystem =='FA':
sourceSystem = '1'
adjustFlag = datas[0][2]
#数据库里adjustFlag 1是增，-1是减。而PH的接口1是增，2是减
if adjustFlag =='-1':
adjustFlag = '2'
adjustNumber = datas[0][3]
updatedBy = 'system'
re,data = openapi.getAssetsAdjust(assetsBagNo,sourceSystem,adjustFlag,adjustNumber,updatedBy,access_token,requestId)
tools.log(re,'info')
TaskEndTime = "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
if(re['msg'] == ""):
if(data['status'] == '0'):
successtimes =successtimes + 1
#任务成功，维护task表
tools.successUpdate(TaskStartTime,TaskEndTime,TaskId)
tools.log(data['message'],'info')
elif(data['status'] == '1'):
errortimes = errortimes + 1
errorInfo = data['message']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
tools.log(errorInfo,'error')
else:
errortimes = errortimes + 1
errorInfo = re['msg']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
tools.log(errorInfo,'error')
tools.log(str(successtimes) + " success(es)," + str(errortimes) + " fail(s)",'info')
return re,data
def run(request, param):
state, dbinfo = tools.getDBConf("DB")
if not state:
return tools.response(-1, dbinfo)
db = tools.database(**dbinfo)
db.printing = True
# 执行方式
settings.LOCK.acquire()
try:
# ############################ 结构化操作数据库 ############################ #
# 结构化查询select
entries1 = db.select("test", what="a,c,d", where="a='fad'", order="d desc", limit=2)
# 结构化单行插入insert
entries2 = db.insert("test", a="sigal", b="n", c=0, d=10.1)
# print("entries2: ", entries2)
# 结构化多行插入multiple_insert
db.supports_multiple_insert = True
values = [{"A": "muti1", "B": "p", "C": 6, "D": 11.1}, {"A": "muti2", "B": "q", "C": 6, "D": 11.2},
{"A": "muti3", "B": "r", "C": 6, "D": 11.3}]
entries3 = db.multiple_insert("test", values=values)
# 结构化更新update
entries4 = db.update("test", where="A='fad'", B='mn', C=2)
# 结构化删除delete
entries5 = db.delete("test", where="A='ferry'")
# ############################ 非结构化操作数据库, 可以执行复杂操作, 查询语句返回storage结果集, 其他返回影响行数 ############################ #
sql = """select m.A, m.B, m.C, m.D, n.F, n.G from test m, test1 n where m.A = n.E """
sql = """INSERT INTO test(a, b, c, d) VALUES ('abc', 'n', 0, 10.1)"""
entries6 = db.exec(sql)
except Exception as e:
print('执行sql failed! [%s]' % str(e))
settings.LOCK.release()
raise
settings.LOCK.release()
# 结果打包成json
jsonData = tools.storage2Json(entries1)
return tools.response(0, '', jsonData)
def assetsSold(access_token,requestId,TaskId):
#任务开始，维护task表
TaskStartTime= "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
tools.taskStartUpdate(TaskStartTime,TaskId)
sql = 'select '+ config.sql['assetsSold'] + ' from ' + config.sql['assetsSoldTable'] + ' where TaskId = ' + str(TaskId)
datas = tools.database(sql)
errortimes = 0
successtimes = 0
if datas == []:
tools.log('there is no data in ABSAssetBag!','error')
return
#只有一条数据，只取第一条
data = datas[0]
config.assetsSold['ASSETS_BAG_NO'] = data[0]
config.assetsSold['PRODUCT_TYPE'] = data[1]
config.assetsSold['DATA_SOURCE'] = data[2]
config.assetsSold['FINISH_BY'] = data[3]
config.assetsSold['FINISH_DATE'] = data[4]
re,data = openapi.getAssetsSold(config.assetsSold['ASSETS_BAG_NO'],config.assetsSold['PRODUCT_TYPE'],config.assetsSold['DATA_SOURCE'],config.assetsSold['FINISH_BY'],config.assetsSold['FINISH_DATE'],access_token,requestId)
tools.log(re,'info')
TaskEndTime = "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
if(re['msg'] == ""):
if(data['status'] == '0'):
successtimes =successtimes + 1
#任务成功，维护task表
tools.successUpdate(TaskStartTime,TaskEndTime,TaskId)
tools.log(data['message'] ,'info')
elif(data['status'] == '1'):
errortimes = errortimes + 1
errorInfo = data['message']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
tools.log(errorInfo ,'error')
else:
errortimes = errortimes + 1
errorInfo = data['msg']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
tools.log(errorInfo,'error')
tools.log(str(successtimes) + "success(es)," + str(errortimes) + "fail(s)" ,'info')
return re,data
Created on Wed Nov 29 13:35:12 ****
>>>>>>> 6d016066d07e0a58c7a778636273b2e278395dab:task.py
@author: Gn
"""
import openapi
import time
import tools
import config
import main
def task(access_token,requestId):
<<<<<<< HEAD:Script/task.py
sql = 'select TaskId,InterfaceType from ' + config.DatabaseInfo['DatabaseInterfaceTable'] + ' where Direction = 1 and ( TaskState = 0 or TaskState = 3 )'
datas = tools.database(sql)
tools.log("TaskIds : " + str(datas),'info')
=======
>>>>>>> 6d016066d07e0a58c7a778636273b2e278395dab:task.py
# Task表交互机制=============================================================================
#从Task表里筛选出状态为0或3，方向为1的任务的TaskId和InterfaceType，根据不同的InterfaceType使用checkapi字典映射调用api方法
sql = 'select TaskId,InterfaceType from ' + config.DatabaseInfo['DatabaseInterfaceTable'] + ' where Direction = 1 and ( TaskState = 0 or TaskState = 3 )'
datas = tools.database(sql)
tools.log("TaskIds : " + str(datas),'info')
#InterfaceType写对应接口的数据库表名，默认值为General,用于调试和特殊任务
checkapi = {'ABSAssetBag':main.assetsSold
,'ABSTrustPaymentOrder':main.assetsPlanPay
,'ABSPresale':main.assetsAdjust
,'':main.passTask}
checkapiJson = {'ABSAssetBag':'assetsSold'
* along with chatlogs. If not, see <http://www.gnu.org/licenses/>.
'''
import collections
import string
import tools
import nltk
import json
import sys
import re
if len(sys.argv) < 3:
print('./wordprofile.py database maxlen src+')
sys.exit(1)
db = tools.database(sys.argv[1])
maxlen = int(sys.argv[2])
if len(sys.argv) > 3:
query = ' OR '.join(['src LIKE ?' for i in range(len(sys.argv)-3)])
args = tuple(sys.argv[3:])
else:
query = 'src != ?'
args = ('*',)
for i in db.get_time_iter(query,args):
thin = ' '.join([x for x in i.msg.split(' ') if len(x) <= maxlen])
print(' '.join([word+'/'+tag for (word,tag) in nltk.pos_tag(nltk.word_tokenize(thin))]))
def assetsPlanPay(access_token,requestId,TaskId):
#任务开始，维护task表
TaskStartTime= "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
tools.taskStartUpdate(TaskStartTime,TaskId)
sql = 'select ' + config.sql['assetsPlanPay'] + ' from ' + config.sql['assetsPlanPayTable'] + ' where TaskId = ' + TaskId
print sql
datas = tools.database(sql);
print datas
assetsPlanPayre = []
#此处循环是当一个TaskId查出多条数据时，对每条数据调用该api
i = 0
while i < len(datas):
i = i+1
data = datas[i-1]
errortimes = 0
successtimes = 0
paymentId=data[0]
trustCode=data[1]
paymentAmount=data[2]
payDate=data[3]
receiverAccount=data[4]
isRegulated=data[5]
if isRegulated== "True":
isRegulated = 'Y'
elif isRegulated== "False":
isRegulated = 'N'
paymentAccount=data[6]
sourceSystem=data[7]
if sourceSystem== "ACC":
sourceSystem = '2'
elif sourceSystem== "FA":
sourceSystem = '1'
productType=data[8]
if productType == 'None':
productType = ""
updatedBy='system'
paymentId = paymentId.replace("-","")
params = {"access_token":access_token,"requestId":requestId,"paymentId":paymentId, "trustCode":trustCode,"paymentAmount":paymentAmount,"payDate":payDate,"receiverAccount":receiverAccount,"isRegulated":isRegulated,"paymentAccount":paymentAccount,"sourceSystem":sourceSystem,"productType":productType,"updatedBy":updatedBy}
#re,data = openapi.getAssetsPlanPay(access_token,requestId,paymentId, trustCode,paymentAmount,payDate,receiverAccount,isRegulated,paymentAccount,sourceSystem,productType,updatedBy)
re,data = openapi.getAssetsPlanPay(params)
tools.log(re,'info')
assetsPlanPayre.append(re)
TaskEndTime = "\'" + time.strftime("%Y-%m-%d %X",time.localtime()) + "\'"
if(re['msg'] == ""):
#此处返回的status不确定，普惠还没定下来
# =============================================================================
# #任务成功，维护task表
# tools.successUpdate(TaskStartTime,TaskEndTime,TaskId)
# #任务成功，维护兑付表
# sql = 'update dbo.ABSTrustPaymentOrder set PaymentStatus = N"' + data['status'] +'" where TaskId = ' + str(TaskId)
# tools.databasesql(sql)
# =============================================================================
if(data['status'] == '0'):
successtimes =successtimes + 1
#任务成功，维护task表
tools.successUpdate(TaskStartTime,TaskEndTime,TaskId)
#任务成功，维护兑付表
sql = "update dbo.ABSTrustPaymentOrder set PaymentStatus = N'成功' where TaskId = " + str(TaskId)
tools.databasesql(sql)
tools.log(data['message'] ,'info')
elif(data['status'] == '1'):
errortimes = errortimes + 1
errorInfo = data['message']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
#任务失败，维护兑付表
sql = "update dbo.ABSTrustPaymentOrder set PaymentStatus = N'失败' where TaskId = " + str(TaskId)
tools.databasesql(sql)
tools.log(errorInfo ,'error')
else:
errortimes = errortimes + 1
errorInfo = re['msg']
#任务失败，维护task表
tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId)
#任务失败，维护兑付表
sql = "update dbo.ABSTrustPaymentOrder set PaymentStatus = N'失败' where TaskId = " + str(TaskId)
tools.databasesql(sql)
tools.log(errorInfo ,'error')
tools.log(str(successtimes) + "success(es)," + str(errortimes) + "fail(s)" ,'info')
return re,data
def DoSend(DataETLEntity):
DataSourceUrl = DataETLEntity.DataSourceUrl
UserName = DataETLEntity.UserName
UserPassword = DataETLEntity.UserPassword
Charset = DataETLEntity.Charset
DataSourceType = DataETLEntity.DataSourceType
DataSourceName = DataETLEntity.DataSourceName
DataSourceTableName = DataETLEntity.DataSourceTableName
DataSourceDescription = DataETLEntity.DataSourceDescription
DataGetSQL = DataETLEntity.DataGetSQL
DestinationHttpType = DataETLEntity.DestinationHttpType
DestinationUrl = DataETLEntity.DestinationUrl
TaskTableName = DataETLEntity.TaskTableName
DataSQLDict = DataETLEntity.DataSQLDict
SendToken = DataETLEntity.SendToken
GetTokenUrl = DataETLEntity.GetTokenUrl
keyColumn = DataETLEntity.keyColumn
TaskSql = DataETLEntity.TaskSql
conn = pymssql.connect(DataSourceUrl, UserName,
base64.b64decode(UserPassword).decode("UTF-8"),
DataSourceName, Charset)
requestId = str(int(time.time()))
try:
TaskStartTime = "\'" + time.strftime("%Y-%m-%d %X",
time.localtime()) + "\'"
if TaskSql == 'Null':
print("this no taskID")
else:
#获取xml中TaskSql标签的数据，用于查找待处理的task
TaskIds = tools.database(conn, TaskSql)[0]
for i in range(len(TaskIds)):
TaskId = TaskIds[i][0]
#查询数据
DataGetSQLByTaskId = DataGetSQL + " and TaskId = " + str(
TaskId)
tools.log(DataGetSQLByTaskId, 'info')
datas, cols = tools.database(conn, DataGetSQLByTaskId)
#字段名称遍历
colslist = []
for i in range(len(cols)):
colslist.append(cols[i][0])
tools.log(datas, 'info')
# 此处循环是当一个TaskId查出多条数据时，对每条数据调用该api
fail_list = []
errortimes = 0
successtimes = 0
if len(datas) == 0:
errortimes = errortimes + 1
tools.log(
'there is no data in ' + DataSourceTableName +
' where TaskId = ' + str(TaskId), 'error')
errorInfo = 'there is no data in ' + DataSourceTableName + ' where TaskId = ' + str(
TaskId)
tools.noDataUpdate(TaskStartTime, errorInfo, TaskId, conn,
TaskTableName)
tools.log(
str(successtimes) +
"success(es), + str(errortimes) + fail(s)", 'info')
else:
for index, data in enumerate(datas):
params = {}
for index1, attr in enumerate(data):
params[colslist[index1]] = attr
if SendToken == "True":
access_token = tools.getToken(GetTokenUrl)
params['access_token'] = access_token
dataId = params[keyColumn]
tools.log(DestinationUrl, 'info')
headers = {
'content-type': 'application/json',
'charset': 'UTF-8'
}
r = None
if DestinationHttpType == 'GET':
r = requests.get(DestinationUrl,
headers=headers,
params=params)
elif DestinationHttpType == 'POST':
print('TODO')
re, data = tools.strtojson(r.text)
tools.log(re, 'info')
TaskEndTime = "\'" + time.strftime(
"%Y-%m-%d %X", time.localtime()) + "\'"
StatusStr = 'status' #考虑放入xml
xmlSql = ''
if (re['msg'] == ""):
if (len(data) == 0):
errortimes = errortimes + 1
errorInfo = "错误返回示例"
tools.log(errorInfo, 'error')
elif (data[StatusStr] == '0'):
successtimes = successtimes + 1
# 任务成功，维护task表
# tools.successUpdate(TaskStartTime,TaskEndTime,TaskId,conn)
#任务成功，维护order表
for key in DataSQLDict:
if key == '0':
xmlSql = DataSQLDict[key]["SQL"]
UpdateSql = xmlSql + " and " + keyColumn + " = " + str(
dataId)
tools.databasesql(conn, UpdateSql)
tools.log(data['message'], 'info')
elif (data[StatusStr] == '1'):
errortimes = errortimes + 1
errorInfo = data['message']
# 任务失败，维护task表
# tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId,conn)
# 任务失败
for key in DataSQLDict:
if key == '1':
xmlSql = DataSQLDict[key]["SQL"]
UpdateSql = xmlSql + " and " + keyColumn + " = " + str(
dataId)
tools.databasesql(conn, UpdateSql)
tools.log(errorInfo, 'error')
else:
errortimes = errortimes + 1
errorInfo = re['msg']
# 任务失败，维护task表
# tools.failUpdate(TaskStartTime,TaskEndTime,errorInfo,TaskId,conn)
tools.log(errorInfo, 'error')
#for key in DataSQLDict:
#thisSQL = DataSQLDict[key]["SQL"]
#thisParamsMap = DataSQLDict[key]["Params"]
##如果没有属性直接执行sql
#if len(thisParamsMap) == 0:
#tools.databasesql(conn, thisSQL)
#else:
#tools.databasesql(conn, thisSQL, thisParamsMap)
TaskEndTime = "\'" + time.strftime("%Y-%m-%d %X",
time.localtime()) + "\'"
if (successtimes == len(datas)):
tools.successUpdate(TaskStartTime, TaskEndTime, TaskId,
conn, TaskTableName)
else:
errorInfo = "N"
tools.failUpdate(TaskStartTime, TaskEndTime, errorInfo,
TaskId, conn, TaskTableName)
tools.log(
str(successtimes) + "success(es), " + str(errortimes) +
"fail(s)", 'info')
except Exception as e:
tools.log('Exception error', 'error')
tools.log(e, 'error')
return
finally:
conn.close()
