class AppStatistics():
def __init__(self):
self.db = Mysqldb(config.MySqlHostTicker, config.MySqlUserTicker, config.MySqlPasswdTicker,
config.MySqlDbTicker, config.MySqlPortTicker)
def node_total_count(self):
select_str = 'SELECT id from users'
res_sel = self.db.select(select_str)
return len(res_sel)
#start_time,表示统计的开始时间
#record_type:两种类型，stealCrystal：偷水晶,collectCrystal：收取水晶
def collect_total_count(self, record_type, start_time):
select_str = 'select *,count(uid) as count from balance_record WHERE record_type = "' + record_type +'" and txtime > ' + str(start_time) + ' group by uid having count>0'
#select_str = 'SELECT uid FROM balance_record WHERE record_type = "collectCrystal" and txtime > ' + str(start_time)
res_sel = self.db.select(select_str)
return len(res_sel)
def insurance_total_count(self):
select_str = 'select b.name, count(*) as count from orders as a, products as b WHERE a.product_id = b.product_id group by a.product_id'
res_sel = self.db.select(select_str)
return res_sel
def adv_child_node_count(self):
select_str = 'select parent_id, count(*) as count from invite_agent group by parent_id'
res_sel = self.db.select(select_str)
total = 0
for node in res_sel:
total += node[1]
return total / len(res_sel)
def send_email(self, mail_text):
smtp_server = 'smtp.exmail.qq.com'
from_addr = '*****@*****.**'
receivers = ['*****@*****.**', '*****@*****.**']
password = '******'
message = MIMEText(mail_text, 'plain', 'utf-8')
message['From'] = Header("马耀光", 'utf-8') # 发送者
message['To'] = Header("云保链", 'utf-8') # 接收者
subject = 'cichain用户数据统计'
message['Subject'] = Header(subject, 'utf-8')
server = smtplib.SMTP_SSL(smtp_server, 465)
server.login(from_addr, password)
try:
server.sendmail(from_addr, receivers, message.as_string())
server.quit()
print("邮件发送成功")
except smtplib.SMTPException:
print("Error: 无法发送邮件")
class TxDataPro():
def __init__(self):
self.db = Mysqldb(config.MySqlHost, config.MySqlUser,
config.MySqlPasswd, config.MySqlDb, config.MySqlPort)
def get_tx_data(self):
sel_str = "SELECT id, fxh_address from token_base WHERE fxh_address <> ''"
db_res = self.db.select(sel_str)
timestamps = int(time.time())
recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
print('get_tx_data', recordDate)
for token_bace in db_res:
res_html = common_fun.get_url_html(token_bace[1])
price_list = res_html.xpath(
'//*[@id="baseInfo"]//*[@class="coinprice"]//text()')
price_cny = price_list[0].split('￥')[1].replace(',', '').replace(
'?', '0')
gains_24h = price_list[1].replace('%', '')
price_usdt = res_html.xpath(
'//*[@id="baseInfo"]//*[@class="sub"]//text()')[0].replace(
'≈', '')
price_btc = res_html.xpath(
'//*[@id="baseInfo"]//*[@class="sub"]//text()')[2].replace(
'≈', '')
price_usdt = price_usdt.split('$')[1].replace(',', '')
price_btc = price_btc.split('BTC')[0].replace(',', '')
flow_num = res_html.xpath(
'//*[@id="baseInfo"]/div[1]/div[3]/div[2]/text()')[0].split(
' ')[0].replace(',', '')
tx_amount_24h = res_html.xpath(
'//*[@id="baseInfo"]/div[1]/div[4]/div[2]/text()')[0].replace(
',', '')
if tx_amount_24h == '?' or tx_amount_24h == '？':
pass
else:
tx_amount_24h = tx_amount_24h.split('¥')[1].replace(',', '')
issued_num = res_html.xpath(
'//*[@id="baseInfo"]/div[1]/div[3]/div[4]/text()')[0].split(
' ')[0].replace(',', '')
change_rate_24h = res_html.xpath(
'//*[@id="baseInfo"]/div[1]/div[4]/div[5]/div/span/text()'
)[0].replace('%', '')
insert_str = "INSERT INTO erc20_tx_data (token_id, get_data_time, issued_num, flow_num, tx_amount_24h, change_rate_24h, gains_24h, price_cny, price_usdt, price_btc, created_at)"
insert_str += "VALUES (" + str(
token_bace[0]
) + "," + str(timestamps) + ",'" + issued_num + "','" + str(
flow_num) + "','" + str(tx_amount_24h) + "','" + str(
change_rate_24h) + "','" + str(gains_24h) + "','" + str(
price_cny) + "','" + str(price_usdt) + "','" + str(
price_btc) + "','" + str(recordDate) + "')"
try:
self.db.insert(insert_str)
except Exception as e:
print(insert_str)
print('insert err, token = ', token_bace[0])
recordDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
print('get_tx_data', recordDate)
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()
def foreign_city_name(self, index_file):
city_name_extra = [
'-oh',
'-ca',
'-mi',
'-ga',
'-tx',
'-ca',
'-wa',
'-ma',
'-new-town',
'-estate',
'-fl',
'-ia',
'-az',
'-wv',
'-nc',
'-va',
'-il',
'-tn',
'-co',
'-or',
'-in',
'-hi',
'-ny',
'-ut',
'-ct',
'-dc',
'-ok',
'-sd',
'-la',
'-pa',
'-ne',
'-nv',
]
write_data = []
db = Mysqldb(config.MySqlHost, config.MySqlUser, config.MySqlPasswd,
config.MySqlDb, config.MySqlPort)
i = 0
with open(index_file, 'r', encoding='utf8') as csvfile:
reader = csv.reader(csvfile)
for line in reader:
for del_str in city_name_extra:
if line[0].endswith(del_str):
end_index = -len(del_str)
line[0] = line[0][:end_index]
line[0] = line[0].replace('-', ' ')
sel_str = "SELECT * from city_location_copy_bk WHERE city like '" + line[
0] + "%'"
res_db = db.select(sel_str)
if len(res_db) == 1:
line.append(res_db[0][3])
line.append(res_db[0][4])
i += 1
elif len(res_db) > 1:
print(line[0])
write_data.append(line)
print(i)
self.write_csv_file('foreign_loc_index.csv', write_data)
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 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 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 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()))
