class TokenBacePro(): def __init__(self): self.db = Mysqldb(config.MySqlHost, config.MySqlUser, config.MySqlPasswd, config.MySqlDb, config.MySqlPort) #self.driver = webdriver.Chrome(executable_path = config.chromedriver) self.driver = webdriver.Firefox() def get_token_address(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) self.driver.get(config.eth_tokens_url) xpath_str = '//*[@id="ContentPlaceHolder1_divpagingpanel"]/div[2]/p/span/b[1]' element_present = EC.text_to_be_present_in_element( (By.XPATH, xpath_str), '1') WebDriverWait(self.driver, 30, 1).until(element_present) page_max = self.driver.find_element_by_xpath( '//*[@id="ContentPlaceHolder1_divpagingpanel"]/div[2]/p/span/b[2]' ).text print(page_max) for page_index in range(1, int(page_max) + 1): page_url = config.eth_tokens_url + '?p=' + str(page_index) res_html = common_fun.get_url_html(page_url) for xpath_str in res_html.xpath( '//*[@id="ContentPlaceHolder1_divresult"]/table/tbody/tr'): href_str = xpath_str.xpath('./td[3]/h5/a/@href')[0] token_str = xpath_str.xpath('./td[3]/h5/a/text()')[0] address = href_str.split('/')[-1] en_name = token_str.split('(')[0].strip() token_name = token_str.split('(')[1].split(')')[0] sel_str = "select * from token_base where token_name ='" + token_name + "'and en_name='" + en_name + "'" sel_res = self.db.select(sel_str) if len(sel_res) == 1: updata_str = "UPDATE token_base SET erc20_contract='" + address updata_str += "' where token_name = '" + token_name + "' and en_name = '" + en_name + "'" try: self.db.update(updata_str) except Exception as e: print(updata_str) print('update err, token = ', token_name) elif len(sel_str) == 2: print('token_name repeat: token_name = ', token_name, ',address = ', address) else: insert_str = "INSERT INTO token_base (token_name, en_name, erc20_contract, created_at, updata_at )" insert_str += "VALUES ('" + token_name + "','" + en_name + "','" + address + "','" + str( recordDate) + "','" + str(recordDate) + "')" try: self.db.update(insert_str) except Exception as e: print(insert_str) print('insert err, token = ', token_name) def get_token_data(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) sel_str = "SELECT id, erc20_contract, github_address, twitter_address, facebook_address, telegraph_address, whitepaper_address from token_base WHERE erc20_contract <> ''" db_res = self.db.select(sel_str) for token in db_res: url = config.eth_token_url + token[1] self.driver.get(url) git_address = token[2] twitter_address = token[3] facebook_address = token[4] telegram_address = token[5] whitepaper_address = token[6] total_issued = self.driver.find_element_by_xpath( '//*[@id="ContentPlaceHolder1_divSummary"]/div[1]/table/tbody/tr[1]/td[2]' ).text total_issued = total_issued.split('(')[0] for element in self.driver.find_elements_by_xpath( '//*[@id="ContentPlaceHolder1_tr_officialsite_2"]/td[2]/ul/li' ): original_str = element.find_element_by_xpath( './a').get_attribute('data-original-title') if original_str.startswith('Github'): if git_address == '': git_address = element.find_element_by_xpath( './a').get_attribute('href') elif original_str.startswith('Telegram'): if telegram_address == '': telegram_address = element.find_element_by_xpath( './a').get_attribute('href') elif original_str.startswith('Facebook'): if facebook_address == '': facebook_address = element.find_element_by_xpath( './a').get_attribute('href') elif original_str.startswith('Twitter'): if twitter_address == '': twitter_address = element.find_element_by_xpath( './a').get_attribute('href') elif original_str.startswith('Whitepaper'): if whitepaper_address == '': whitepaper_address = element.find_element_by_xpath( './a').get_attribute('href') updata_str = "UPDATE token_base SET github_address='" + git_address + "', twitter_address='" + str( twitter_address ) + "', facebook_address='" + facebook_address + "', telegraph_address='" + str( telegram_address ) + "', whitepaper_address='" + whitepaper_address + "', total_issued='" + total_issued + "',created_at='" + recordDate + "'" updata_str += " where id =" + str(token[0]) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('update err internet_data, token_id = ', token[0]) recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) def get_fxh_address(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) sel_str = "SELECT en_name, erc20_contract, fxh_address from token_base WHERE erc20_contract <> ''" db_res = self.db.select(sel_str) print(len(db_res)) for token in db_res: fxh_address = token[2] if fxh_address == '': fxh_address = config.fxh_base_url + token[0].replace('.', '') res_html = common_fun.get_url_html(fxh_address) if len(res_html): cn_name = '' name = res_html.xpath( '//*[@id="baseInfo"]/div[1]/div[1]/h1/text()') if len(name) > 1: cn_name = name[1].strip() updata_str = "UPDATE token_base SET fxh_address='" + fxh_address + "', cn_name='" + cn_name + "'" updata_str += " where en_name ='" + str(token[0]) + "'" try: self.db.update(updata_str) except Exception as e: print(updata_str) print('update err internet_data, token_id = ', token[0]) else: print(token[0], token[1]) print(fxh_address) def driver_close(self): self.driver.close()
class TokenDataPro(): def __init__(self): self.db = Mysqldb() def token_data_tosql(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) for key in config.DicExchangeName.keys(): print(key) file_name = 'source_data\\' + key + '_clean_data.csv' with open(file_name, 'r', encoding='utf8') as csvfile: reader = csv.reader(csvfile) for line in reader: if line[0] == 'token_name': continue sel_str = "SELECT exchange FROM token_base WHERE en_name = '" + line[ 2] + "'" dbres = self.db.select(sel_str) if len(dbres) > 1: print(len(dbres), key, line[2], line[0]) if dbres: exchange_str = dbres[0][0] if dbres[0][0].find(config.DicExchangeName[key]) == -1: exchange_str = dbres[0][ 0] + ',' + config.DicExchangeName[key] else: continue updata_str = "UPDATE token_base SET exchange='" + exchange_str + "',updata_at='" + recordDate + "'" updata_str += " where en_name = '" + line[2] + "'" try: self.db.update(updata_str) except Exception as e: print(updata_str) print('update err, token = ', line[0]) else: insert_str = "INSERT INTO token_base (token_name, en_name, cn_name, exchange, created_at, updata_at )" if line[2] == 'Bitcoin Cash(BCC)': insert_str += "VALUES ('BCH','" + line[ 2] + "','" + line[ 1] + "','" + config.DicExchangeName[ key] + "','" + str( recordDate) + "','" + str( recordDate) + "')" else: insert_str += "VALUES ('" + line[0] + "','" + line[ 2] + "','" + line[ 1] + "','" + config.DicExchangeName[ key] + "','" + str( recordDate) + "','" + str( recordDate) + "')" try: self.db.update(insert_str) except Exception as e: print(insert_str) print('insert err, token = ', line[0], key) recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) return
class GithubDataPro(): def __init__(self): self.db = Mysqldb(config.MySqlHost, config.MySqlUser, config.MySqlPasswd, config.MySqlDb, config.MySqlPort) self.git_url = 'https://github.com/eosio' def get_gitaddress(self): sel_str = "SELECT project_name FROM github_base" project_name_list = self.db.select(sel_str) sel_str = "SELECT id, github_address FROM token_base WHERE github_address <>''" res_sel = self.db.select(sel_str) for token_bace in res_sel: res_html = common_fun.get_url_html(token_bace[1]) if res_html == 404: print('404:', token_bace[1]) continue elif res_html == 500: print('500', token_bace[1]) continue elif res_html == '': print('err:', token_bace[1]) continue #res_html = common_fun.get_url_html(self.git_url) for pro_data_xpath in res_html.xpath( '//*[@id="org-repositories"]/div[1]/div/li'): project_name = pro_data_xpath.xpath( 'div[1]/h3/a/text()')[0].strip() project_address = pro_data_xpath.xpath( 'div[1]/h3/a/@href')[0].strip() if (project_name, ) not in project_name_list: project_address = 'https://github.com' + project_address insert_str = "INSERT INTO github_base (token_id, project_name, project_address)" insert_str += "VALUES (" + str( token_bace[0] ) + ",'" + project_name + "','" + project_address + "')" try: self.db.insert(insert_str) except Exception as e: print(insert_str) print('INSERT err internet_data, token_id = ', token_bace[0]) continue else: pass def open_driver(self): options = webdriver.FirefoxOptions() options.add_argument('--headless') driver = webdriver.Firefox(options=options) driver.set_page_load_timeout(20) driver.maximize_window() return driver def init_git_detail_data(self): sel_str = "SELECT id FROM github_base WHERE project_address <> ''" res_sel = self.db.select(sel_str) timestamps = int(time.time()) for git_base in res_sel: insert_str = "INSERT INTO github_detail_data (project_id, get_data_time)" insert_str += "VALUES (" + str( git_base[0]) + "," + str(timestamps) + ")" try: self.db.insert(insert_str) except Exception as e: print(insert_str) print('INSERT err internet_data, project_id = ', git_base[0]) continue def get_detail_data(self): sel_str = "SELECT b.project_id, b.get_data_time, a.project_address from github_base as a, github_detail_data as b WHERE (b.star_num = -1 or b.watch_num = -1 or b.fork_num = -1 or b.commits_num = -1 or b.branches_num = -1 or b.releases_num = -1 or b.contributors_num = -1)AND a.id = b.project_id" retry_times = 5 while True: res_sel = self.db.select(sel_str) retry_times = retry_times - 1 if retry_times < 0: for git_base in res_sel: updata_str = "UPDATE github_detail_data SET star_num=" + str( -2) + ", watch_num=" + str(-2) + ", fork_num=" + str( -2) + ", commits_num=" + str( -2) + ",branches_num=" + str( -2) + ", releases_num=" + str( -2) + ", contributors_num=" + str(-2) updata_str += " where project_id =" + str( git_base[0]) + " and get_data_time=" + str(git_base[1]) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('UPDATE err retry_times < 0, gitaddress = ', git_base[2]) continue break driver = self.open_driver() for git_base in res_sel: try: driver.get(git_base[2]) except TimeoutException: driver.execute_script('window.stop()') try: watch_num = driver.find_element_by_xpath( '//*[@id="js-repo-pjax-container"]/div[1]/div/ul/li[1]/a[2]' ).text.replace(',', '') star_num = driver.find_element_by_xpath( '//*[@id="js-repo-pjax-container"]/div[1]/div/ul/li[2]/a[2]' ).text.replace(',', '') fork_num = driver.find_element_by_xpath( '//*[@class="pagehead-actions"]/li[3]/a[2]' ).text.replace(',', '') commit_num = driver.find_element_by_xpath( '/html/body/div[4]/div/div/div[2]/div[1]/div[3]/div/div/ul/li[1]/a/span' ).text.replace(',', '') branches_num = driver.find_element_by_xpath( '/html/body/div[4]/div/div/div[2]/div[1]/div[3]/div/div/ul/li[2]/a/span' ).text.replace(',', '') releases_num = driver.find_element_by_xpath( '/html/body/div[4]/div/div/div[2]/div[1]/div[3]/div/div/ul/li[3]/a/span' ).text.replace(',', '') contributors_num = driver.find_element_by_xpath( '/html/body/div[4]/div/div/div[2]/div[1]/div[3]/div/div/ul/li[4]/a/span' ).text.replace(',', '') updata_str = "UPDATE github_detail_data SET star_num=" + str( star_num ) + ", watch_num=" + str(watch_num) + ", fork_num=" + str( fork_num) + ", commits_num=" + str( commit_num) + ",branches_num=" + str( branches_num) + ", releases_num=" + str( releases_num ) + ", contributors_num=" + str( contributors_num) updata_str += " where project_id =" + str( git_base[0]) + " and get_data_time=" + str(git_base[1]) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('UPDATE err, git_address = ', git_base[2], '-----project_id = ', git_base[0]) continue except: pass driver.close() driver.service.stop()
class Erc20Data(): def __init__(self): self.db = Mysqldb(config.MySqlHost, config.MySqlUser, config.MySqlPasswd, config.MySqlDb, config.MySqlPort) #self.driver = webdriver.Chrome(executable_path = config.chromedriver) #由于网络原因,数据获取经常性的中断,所以需要分成多步来获取数据,原则上数据每天更新一次,每天0点启动脚本 #首先通过时间戳和token id生成erc20_data的唯一标识,并且给每条数据标记未获取 #通过数据库筛选出来所有未获取的数据得唯一标识和erc20_contract,并更新数据 def erc20_data_key(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print('erc20_data_key', recordDate) timestamps = int(time.time()) sel_str = "SELECT id, erc20_contract from token_base WHERE erc20_contract <> ''" res_sel = self.db.select(sel_str) for token_bace in res_sel: insert_str = "INSERT INTO erc20_data (token_id, get_data_time, top100_detail)" insert_str += "VALUES (" + str( token_bace[0]) + "," + str(timestamps) + "," + '0' + ")" try: self.db.insert(insert_str) except Exception as e: print(insert_str) print('INSERT err internet_data, token_id = ', token_bace[0]) continue def open_driver(self): options = webdriver.FirefoxOptions() options.add_argument('--headless') driver = webdriver.Firefox(options=options) driver.set_page_load_timeout(20) driver.maximize_window() return driver def get_erc20_data(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print('get_erc20_data', recordDate) sel_str = "SELECT a.token_id, a.get_data_time, b.erc20_contract from erc20_data as a, token_base as b WHERE (a.token_hold_num = -1 or a.token_tx_num = -1)AND a.token_id = b.id" while True: res_sel = self.db.select(sel_str) print(len(res_sel)) if len(res_sel) <= 0: break driver = self.open_driver() #driver = webdriver.Chrome(executable_path = config.chromedriver) for token_bace in res_sel: url = config.eth_token_url + token_bace[2] try: driver.get(url) except TimeoutException: driver.execute_script('window.stop()') try: token_holders = driver.find_element_by_xpath( '//*[@id="ContentPlaceHolder1_divSummary"]/div[1]/table/tbody/tr[3]/td[2]' ).text token_holders = token_holders.strip().split(' ')[0] tx_num = driver.find_element_by_xpath( '//*[@id="totaltxns"]').text updata_str = "UPDATE erc20_data SET token_hold_num=" + token_holders + ", token_tx_num=" + str( tx_num) updata_str += " where token_id =" + str( token_bace[0]) + " and get_data_time=" + str( token_bace[1]) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('UPDATE err internet_data, token_id = ', token_bace[0]) continue except: print('xpath err', url) driver.close() driver.service.stop() recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print('get_erc20_data', recordDate) def get_top100_hold(self): sel_str = "SELECT a.token_id, a.get_data_time, b.erc20_contract, a.token_hold_num from erc20_data as a, token_base as b WHERE top100_detail = 0 AND a.token_id = b.id LIMIT 30" while True: res_sel = self.db.select(sel_str) recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(len(res_sel), recordDate) if len(res_sel) <= 0: break driver = self.open_driver() #driver = webdriver.Chrome(executable_path = config.chromedriver) for token_bace in res_sel: url = config.eth_token_url + token_bace[2] try: driver.get(url) except TimeoutException: driver.execute_script('window.stop()') try: driver.find_element_by_xpath( '//*[@id="ContentPlaceHolder1_li_balances"]/a').click( ) except: print('click err', time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())) time.sleep(5) continue try: driver.switch_to_alert().accept() except: pass driver.switch_to.frame('tokeholdersiframe') xpath_str = '//*[@id="maintable"]/table/tbody/tr[2]/td[1]' try: element_present = EC.text_to_be_present_in_element( (By.XPATH, xpath_str), '1') WebDriverWait(driver, 20, 1).until(element_present) recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) if len( driver.find_elements_by_xpath( '//*[@id="maintable"]/table/tbody/tr')) < 2: print('rank data len less') continue insert_list = [] for element in driver.find_elements_by_xpath( '//*[@id="maintable"]/table/tbody/tr'): try: rank = element.find_element_by_xpath( './td[1]').text.replace(',', '') address = element.find_element_by_xpath( './td/span').text.replace(',', '') quantity = element.find_element_by_xpath( './td[3]').text.replace(',', '') percentage = element.find_element_by_xpath( './td[4]').text.replace(',', '').replace('%', '') insert_str = "INSERT INTO hold_top_100 (token_id, get_data_time, rank, address, quantity, percentage, creat_at)" insert_str += "VALUES (" + str( token_bace[0] ) + "," + str(token_bace[1]) + "," + str( rank) + ",'" + str(address) + "'," + str( quantity) + "," + str( percentage) + ",'" + recordDate + "');" insert_list.append(insert_str) except: continue if token_bace[3] > 50: driver.find_element_by_xpath( '//*[@id="PagingPanel"]/a[3]').click() element_present = EC.text_to_be_present_in_element( (By.XPATH, xpath_str), '51') WebDriverWait(driver, 20, 1).until(element_present) for element in driver.find_elements_by_xpath( '//*[@id="maintable"]/table/tbody/tr'): try: rank = element.find_element_by_xpath( './td[1]').text.replace(',', '') address = element.find_element_by_xpath( './td/span').text.replace(',', '') quantity = element.find_element_by_xpath( './td[3]').text.replace(',', '') percentage = element.find_element_by_xpath( './td[4]').text.replace(',', '').replace( '%', '') insert_str = "INSERT INTO hold_top_100 (token_id, get_data_time, rank, address, quantity, percentage, creat_at)" insert_str += "VALUES (" + str( token_bace[0]) + "," + str( token_bace[1] ) + "," + str(rank) + ",'" + str( address ) + "'," + str(quantity) + "," + str( percentage) + ",'" + recordDate + "');" insert_list.append(insert_str) except Exception as e: continue if len(insert_list) == 100 or len( insert_list) == token_bace[3]: try: self.db.execute_list(insert_list) updata_str = "UPDATE erc20_data SET top100_detail=" + '1' updata_str += " where token_id =" + str( token_bace[0]) + " and get_data_time=" + str( token_bace[1]) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('UPDATE err updata_str, token_id = ', token_bace[0]) except Exception as e: print('INSERT err internet_data, >50 token_id = ', token_bace[0]) except: print('Timed out waiting for page to load. token_id:', token_bace[0]) driver.close() driver.service.stop() time.sleep(5) recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print('get_top100_hold', recordDate)
class RiskDataPro(): def __init__(self): self.db = Mysqldb() def clean_data(self): recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) print(recordDate) new_data = [] en_name_list = [] for key in config.DicExchangeName.keys(): print(key) file_name = 'source_data\\' + key + '_clean_data.csv' with open(file_name, 'r', encoding='utf8') as csvfile: reader = csv.reader(csvfile) for line in reader: if line[0] == 'token_name': continue new_line = [] new_line.append(line[0]) new_line.append(line[2]) new_line.append(line[5]) new_line.append(line[6]) new_line.append(line[7]) if line[2] not in en_name_list: en_name_list.append(line[2]) new_data.append(new_line) print(len(new_data)) head_line = [ 'token_name', 'en_name', 'tx_amount_24h', 'change_rate_24h', 'total_value' ] print(head_line) file_name = 'risk_clean.csv' csvFile = open(file_name, 'w', newline='', encoding='utf8') writer = csv.writer(csvFile) if head_line != None: writer.writerow(head_line) for line in new_data: writer.writerow(line) csvFile.close() def make_risk_data(self): file_name = 'risk_clean.csv' df = pd.read_csv(file_name, sep=',') def huanshou(number): if number <= 3: return 1 elif number > 3 and number <= 7: return 1.02 elif number > 7 and number <= 10: return 1.05 elif number > 10 and number <= 15: return 1.08 elif number > 15: return 1.1 df['new_amount'] = df['tx_amount_24h'] * df['change_rate_24h'].map( huanshou) btc_shizhi = df.loc[df['token_name'] == 'BTC']['total_value'] def shizhi(shizhi): return shizhi / btc_shizhi.values[0] df['shizhi_amount'] = df['new_amount'] * df['total_value'].map(shizhi) percent_df = df.describe(percentiles=[0.1, 0.3, 0.7, 0.9]) print(percent_df) def number_to_star(number): if number <= percent_df.loc['10%', 'shizhi_amount']: return 5 elif number > percent_df.loc[ '10%', 'shizhi_amount'] and number <= percent_df.loc[ '30%', 'shizhi_amount']: return 4 elif number > percent_df.loc[ '30%', 'shizhi_amount'] and number <= percent_df.loc[ '70%', 'shizhi_amount']: return 3 elif number > percent_df.loc[ '70%', 'shizhi_amount'] and number <= percent_df.loc[ '90%', 'shizhi_amount']: return 2 elif number > percent_df.loc['90%', 'shizhi_amount']: return 1 df['star'] = df['shizhi_amount'].map(number_to_star) print(len(df.loc[df['token_name'] == 'CIC']['star'].values)) if len(df.loc[df['token_name'] == 'CIC']['star'].values) > 0: if df.loc[df['token_name'] == 'CIC']['star'].values[0] > 2: df.loc[df['token_name'] == 'CIC', ['star']] = 2 if len(df.loc[df['token_name'] == 'OKB']['star'].values) > 0: if df.loc[df['token_name'] == 'OKB']['star'].values[0] > 1: df.loc[df['token_name'] == 'OKB', ['star']] = 1 if len(df.loc[df['token_name'] == 'BNB']['star'].values) > 0: if df.loc[df['token_name'] == 'BNB']['star'].values[0] > 1: df.loc[df['token_name'] == 'BNB', ['star']] = 1 if len(df.loc[df['token_name'] == 'HT']['star'].values) > 0: if df.loc[df['token_name'] == 'HT']['star'].values[0] > 1: df.loc[df['token_name'] == 'HT', ['star']] = 1 if len(df.loc[df['token_name'] == 'CTR']['star'].values) > 0: df.loc[df['token_name'] == 'CTR', ['star']] = 5 save_name = 'risk_final.csv' df.to_csv(save_name, index=False, encoding='utf8') def risk_data_tosql(self): file_name = 'risk_final.csv' recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) with open(file_name, 'r', encoding='utf8') as csvfile: reader = csv.reader(csvfile) for line in reader: if line[0] == 'token_name': continue select_str = "SELECT id FROM token_base WHERE en_name = '" + line[ 1] + "'" token_id = self.db.select(select_str)[0][0] select_str = "SELECT * FROM risk_data WHERE token_id = " + str( token_id) sel_res = self.db.select(select_str) if sel_res: updata_str = "UPDATE risk_data SET drop_off_risk_data=" + line[ 7] + ",tx_amount=" + str( line[2]) + ",change_rate=" + str( line[3]) + ",total_value='" + str(line[4]) updata_str += " where token_id = " + str(token_id) try: self.db.update(updata_str) except Exception as e: print(updata_str) print('update err, token = ', line[0]) else: insert_str = "INSERT INTO risk_data (token_id, drop_off_risk_data, tx_amount, change_rate, total_value, created_at)" insert_str += "VALUES (" + str(token_id) + "," + str( line[7]) + "," + str(line[2]) + "," + str( line[3]) + "," + str( line[4]) + ",'" + str(recordDate) + "')" try: self.db.update(insert_str) except Exception as e: print(insert_str) print('insert err, token = ', line[0]) try: self.db.update( "UPDATE risk_data a INNER JOIN (SELECT a.total_value ,COUNT(*) AS pm FROM risk_data a LEFT JOIN risk_data b ON a.total_value <= b.total_value GROUP BY a.total_value) c ON a.total_value=c.total_value SET a.total_value_rank =c.pm" ) self.db.update( "UPDATE risk_data a INNER JOIN (SELECT a.tx_amount ,COUNT(*) AS pm FROM risk_data a LEFT JOIN risk_data b ON a.tx_amount <= b.tx_amount GROUP BY a.tx_amount) c ON a.tx_amount=c.tx_amount SET a.tx_amount_rank =c.pm" ) except Exception as e: print('update err rank') print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))