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