def loginvoyager(self):
tmpsql = '''SELECT * from voyager.um_user where USERNAME='******' and `PASSWORD`='{password}'
'''.format(username=self.username, password=self.password)
# 先写到测试环境
# r=db.selectsql('testvoyager',tmpsql)
r = db.selectsql('devvoyager', tmpsql)
return len(r)
def getadvertiser_deduction(day, orderid):
day = day.replace('-', '')
sql = ''' SELECT create_time,amount,case charge_status when '1' THEN '投放' ELSE '暂停' END 投放状态 from voyager.advertiser_balance_pre_deduction where consumer_date='{0}' and order_id={1}
'''.format(day, str(orderid))
print sql
result = db.selectsql('devvoyager', sql)
print result
return result
def checkcate():
sql= '''SELECT spu_id,COUNT(1) from normandy_jd_goods where spu_id is not null GROUP BY spu_id HAVING COUNT(1)>1 ORDER BY COUNT(1) desc'''
re=db.selectsql('nomandytest',sql)
tmpspuid=[]
# # 最终有问题的spuid
tmpspucat=[]
for i in re:
tmpspuid.append(str(i[0]))
# print tmpspuid
# tmpspuid=['1012439','1012451','1012519','1058908','1315798','1315806','1328750','1328761','1603742','1875992','1875996','1887518','1887526','2141148','2141152','2141153','2141154','2217736','2217746','2217750','2218385','2218387','530233','971067','971592']
for i in tmpspuid:
catsql='''SELECT DISTINCT category_id_level_1,category_id_level_2 from normandy_jd_goods where spu_id={}'''.format(str(i))
recat=db.selectsql('nomandytest',catsql)
print tmpspuid.index(i)
if len(recat)>1:
tmpspucat.append(str(i))
print tmpspucat
return 1
def getactpicpathyiqifa(self):
tmpsql = '''SELECT PRODUCT_NAME,PRODUCT_IMG FROM interest_product where CREATE_TIME>'2020-07-09' '''
if self.env == 'test':
re = db.selectsql("testtest", tmpsql)
tmp = {}
for i in re:
tmp[i[0]] = i[1]
print tmp
print type(tmp)
return tmp
def getbeijing_region():
sql = '''SELECT 'beijing',ip FROM voyagerlog.ad_show_log20191014 where region='天津天津' '''
re = db.selectsql('devvoyager', sql)
# print re
tmpbeijing = []
for i in re:
tlist = str(i[1]).split('.')
tip = tlist[0] + '.' + tlist[1] + '.' + tlist[2]
if tip not in tmpbeijing:
tmpbeijing.append(tip)
print '111111111111111111111'
print tmpbeijing
return 1
def loginuser(self):
# hostname = socket.getfqdn(socket.gethostname( ))
HTTP_HOST = socket.getfqdn(socket.gethostname())
# # ip=socket.gethostbyname(hostname)
# ip= request.META['REMOTE_ADDR']
# tmpsql="SELECT count(1) from test.testuser where username=\'%s\' and pass=\'%s\'"%(str(username),str(password))
tmpsql = "SELECT id,username from test.testuser where username='******' and pass='******' and status=1".format(
str(self.username), str(self.password))
r = db.selectsql('testtest', tmpsql)
# 更新下登录时间
updatesql = "UPDATE test.testuser set login_time=current_timestamp,hostname='{}',ip='{}' where username='******' ".format(
HTTP_HOST, self.REMOTE_ADDR, str(self.username))
insertsql = '''INSERT INTO `test`.`user_login_log` (`username`, `ip`, `method`) VALUES ('{username}', '{ip}', '{method}');'''.format(
username=self.username, ip=self.REMOTE_ADDR, method='test')
db.execsql('testtest', updatesql)
db.execsql('testtest', insertsql)
return r
def loginuserotp(self):
HTTP_HOST = socket.getfqdn(socket.gethostname())
tmpsql = "SELECT id,username,isotp,isfirstotp from test.testuser where username='******' and pass='******' and status=1".format(
str(self.username), str(self.password))
r = db.selectsql('testtest', tmpsql)
# 更新下登录时间
if len(r) > 0:
insertsql = '''INSERT INTO `test`.`user_login_log` (`username`, `ip`, `method`) VALUES ('{username}', '{ip}', '{method}');'''.format(
username=self.username, ip=self.REMOTE_ADDR, method='test')
if r[0][2] == '1':
updatesql = "UPDATE test.testuser set isfirstotp=0,login_time=current_timestamp,hostname='{}',ip='{}' where username='******' ".format(
HTTP_HOST, self.REMOTE_ADDR, str(self.username))
else:
updatesql = "UPDATE test.testuser set login_time=current_timestamp,hostname='{}',ip='{}' where username='******' ".format(
HTTP_HOST, self.REMOTE_ADDR, str(self.username))
# db.execsql('testtest',updatesql)
db.execsql('testtest', insertsql)
return r
def getregion():
tmpsql = '''
select start_ip1,cityid from voyager.ipservice where create_time is not null and (city not LIKE'%北京%' and city not LIKE'%上海%' and city not LIKE'%天津%') and city LIKE '%市%';'''
result = db.selectsql('testvoyager', tmpsql)
url = 'https://api01.aliyun.venuscn.com/ip?'
theader = {'Authorization': "APPCODE 870b5c272b134c6396bb1f66ad1b588b"}
tmperr = []
for i in result:
print i
para = {"ip": str(i[0])}
r1 = r.Session()
time.sleep(2)
res = r1.get(url, headers=theader, params=para)
print res.text
city_id = str(res.json()['data']['city_id'])
if city_id <> str(i[1]):
tmperr.append(i[0])
print tmperr
return 1
def update_job_adzoneclickids(self):
tmpsql = str(self.job[0][3])
print tmpsql
# 将任务更新为 获取广告位点击id正在运行
self.update_jobrunstatus(2)
# 查出来所有的adzonclickids
adzonclickids = db.selectsql('devvoyager', tmpsql)
# print adzonclickids
adzonclickids = self.tuptostr(adzonclickids)
# 更新到job_ad_reason
updatetmpsql = '''UPDATE test.job_ad_reason SET adzone_click_ids='{}' where id={}'''.format(
str(adzonclickids), self.jobid)
try:
r = db.execsql('testtest', updatetmpsql)
except Exception as e:
print e.message
return False, e.message
# 将任务更新为 获取广告位点击id完成
self.update_jobrunstatus(3)
return True, r
def jd_price_db(self):
normandysql = 'SELECT jd_sku_id,platform_buy_price,xyg_max_redpacket,xyg_sell_price from normandy_jd_goods limit 100'
re = db.selectsql('nomandytest', normandysql)
tmplist = []
for i in re:
jd_platform_buy_price = 0
xyg_max_redpacket = 0
xyg_sell_price = 0
jd_platform_buy_price = int(
json.loads(self.getSellPrice(str(
i[0])).text)['result'][0]['price'] * 100)
jdprice = con_jd(jd_platform_buy_price)
xyg_max_redpacket, xyg_sell_price = jdprice.getprice()
if (jd_platform_buy_price != int(i[1])
or xyg_max_redpacket != int(i[2])
or xyg_sell_price != int(i[3])):
tmplist.append(
str(i[0]) + "_" + str(i[1]) + "_" + str(i[2]) + "_" +
str(i[3]))
return tmplist
def getactremark(self):
tmpsql = '''SELECT remark from voyager.base_act_info where id=5188'''
if self.env == 'test':
re = db.selectsql("testvoyager", tmpsql)
return eval(re[0][0])
def __init__(self, id):
self.jobid = id
sql = '''SELECT * from test.job_ad_reason where id={};'''.format(
self.jobid)
self.job = db.selectsql('testtest', sql)
def getmediainfor():
tmpsql = '''SELECT ad_click_tag,act_id from voyagerlog.ad_effect_log_04 LIMIT 20;'''
res = db.selectsql('devvoyager', tmpsql)
return res
def get_re(self, tmpsql):
if self.env == 'test':
re = db.selectsql('testvoyager', tmpsql)
else:
re = db.selectsql('devvoyager', tmpsql)
return re
