def update_with_rank_data(list_data, time_stamp):
total = len(list_data)
index = 0
for data in list_data:
index += 1
if index % 100 == 0:
logger.info('progress: {}/{}'.format(index, total))
tag_str = data.get('tag')
address = data['address']
if tag_str:
tags = tag_str.split('-')
exchange = tags[0]
address_tag = '-'.join(tags[1:])
exchange = get_common_exchange_name(exchange)
execute_sql(addr_sql, [address, exchange, address_tag])
else:
continue
# exchange = 'unknown'
# address_tag = None
balance = data['balance']
update_time = datetime.datetime.fromtimestamp(time_stamp)
execute_sql(balance_sql, [address, balance, 'btc', update_time])
def parse(self, response):
addresses = response.xpath("//table//tr/td[1]/a/text()").extract()
exchange = response.url.split('/')[-1]
logger.info(f"handling exchange {exchange} data")
now = datetime.datetime.now()
addr_sql = """
INSERT INTO exchange_eth_address(address, exchange, create_time)
VALUES (%s, %s, %s)
"""
balance_sql = """
INSERT INTO exchange_balance(address, balance, create_time)
VALUES (%s, %s, %s)
"""
for addr in addresses:
logger.info(
f"insert into exchange_eth_address with values: {addr}, {exchange}"
)
try:
execute_sql(addr_sql, (addr, exchange, now))
except MySQLdb._exceptions.IntegrityError:
logger.warning(f"database integrityerror, address: {addr}")
continue
for addr in addresses:
balance = get_account_balance(addr)
if balance is None:
continue
logger.info(
f"insert into exchange_balance with values: {addr}, {balance}")
execute_sql(balance_sql, (addr, balance, now))
def save_to_balance_of_address_history(address, coin, balance, source):
if balance is not None:
now = datetime.datetime.utcnow()
execute_sql(balance_of_address_history_sql,
[address, coin, balance, source, now])
else:
logger.warning(
f"cannot save data addrees: {address} coin: {coin}, balance: {balance}"
)
def save_to_balance_of_exchange_history(exchange, chain, coin, balance,
source):
if balance is not None:
execute_sql(balance_of_exchange_history,
[exchange, chain, coin, balance, source])
else:
logger.warning(
f"cannot save data chain: {chain} coin: {coin}, balance: {balance}"
)
def add_btc_address():
try:
for address in BTC_TOKEN_VIEW_ADDRESSES:
execute_sql(insert_btc_addr_sql,
('renrenbit', 'BTC', address, 'renrenbit btc',
'tokenview', datetime.datetime.utcnow()))
except MySQLdb._exceptions.IntegrityError:
pass
def parse_store_data(self):
delete_sql = "DELETE FROM balance_of_exchange_history WHERE exchange=%s and date(create_time)=%s"
insert_sql = "INSERT INTO balance_of_exchange_history (exchange, chain, coin, balance, source, create_time) VALUES (%s, %s, %s, %s, %s, %s)"
for info in self.balance_info:
exchange = info['entity']
exchange = get_common_exchange_name(exchange)
balance = info['balance']
execute_sql(delete_sql, (exchange, self.info_date.date()))
execute_sql(insert_sql, (exchange, "multiple", self.coin, balance,
"peckshield", self.info_date))
def parse_store_data(self):
sql = """INSERT INTO balance_of_exchange_history (exchange, chain, coin, balance, source, create_time)
VALUES (%s, %s, %s, %s, %s, %s)
"""
for info in self.balance_info:
exchange = info['entity']
exchange = get_common_exchange_name(exchange)
balance = info['balance']
logger.info(
f"exchange: {exchange}, coin: {self.coin}, balance: {balance}")
execute_sql(sql, (exchange, "multiple", self.coin, balance,
"peckshield", self.info_date))
def parse_store_data(self):
delete_sql = "DELETE FROM balance_of_exchange_history WHERE exchange=%s and date(create_time)=%s"
insert_sql = "INSERT INTO balance_of_exchange_history (exchange, chain, coin, balance, source, create_time) VALUES (%s, %s, %s, %s, %s, %s)"
for info in self.btc_balance_info:
exchange = info['entity']
exchange = get_common_exchange_name(exchange)
btc_balance = info['balance']
logger.info(f"insert into exchange_btc_balances table with "
f"exchange<{exchange}> and btc balance<{btc_balance}>")
# delete old data first, then insert new data
execute_sql(delete_sql, (exchange, self.info_date.date()))
execute_sql(insert_sql, (exchange, "multiple", "BTC", btc_balance,
"peckshield", self.info_date))
def parse_store_data(self):
sql = """INSERT INTO balance_of_exchange_history (exchange, chain, coin, balance, source, create_time)
VALUES (%s, %s, %s, %s, %s, %s)
"""
for info in self.eth_balance_info:
exchange = info['entity']
exchange = get_common_exchange_name(exchange)
# if exchange.lower() == 'bitflyer':
# continue
eth_balance = info['balance']
logger.info(
f"insert into exchange_eth_balances table with "
f"exchange<{exchange}> and eth balance<{eth_balance}>")
execute_sql(sql, (exchange, "multiple", "ETH", eth_balance, "peckshield", self.info_date))
def update_non_rank_data(all_btc_addresses, rank_btc_addresses, time_stamp):
update_time = datetime.datetime.fromtimestamp(time_stamp)
non_rank_addresses = all_btc_addresses - rank_btc_addresses
non_rank_address_no = len(non_rank_addresses)
index = 1
for address in non_rank_addresses:
logger.info('processing %s/%s of non-rank addresses' %
(index, non_rank_address_no))
balance = get_btc_balance_for_address(address)
logger.debug(
f"insert non rank data: ({address}, {balance}, btc, {update_time})"
)
execute_sql(balance_sql, (address, balance, 'btc', update_time))
index += 1
logger.info("complete non rank data update.")
def update_balances(address, html):
datas = get_history_with_data(html)
if not datas:
return
current_date = datas[0]['date']
current_balance = datas[0]['balance']
max_date = datetime.datetime.now()
current_index = 0
max_length = len(datas)
while current_date < max_date:
current_item = datas[current_index]
if current_item['date'] == current_date:
current_balance = current_item['balance']
if current_index < max_length - 1:
current_index += 1
execute_sql(
balance_sql,
(address, current_balance, 'eth', current_date, current_balance))
current_date += datetime.timedelta(1)
def update_balances(address, currency, exchange=None):
datas = get_history_for_address(address, currency)
if not datas:
return
current_date = datas[0]['date']
current_balance = datas[0]['balance']
max_date = datetime.datetime.now()
current_index = 0
max_length = len(datas)
while current_date < max_date:
current_item = datas[current_index]
if current_item['date'] == current_date:
current_balance = current_item['balance']
if current_index < max_length - 1:
current_index += 1
execute_sql(
balance_sql,
(address, currency, current_balance, "etherscan", current_date))
current_date += datetime.timedelta(1)
def find_address():
for exchange, search_name in EXCHANGES.items():
address_list = set()
address_list.update(get_exchange_addresses(search_name, search_name))
address_list.update(
get_exchange_addresses(search_name + '%20', search_name))
address_list.update(
get_exchange_addresses(search_name + ': Hot Wallet',
search_name + ': Hot Wallet'))
address_list.update(
get_exchange_addresses(search_name + ': Cold Wallet',
search_name + ': Cold Wallet'))
for addr in address_list:
try:
execute_sql(exchange_chain_address_sql,
(exchange, "ETH", addr[1], addr[0], "etherscan"))
logger.info(
f"add data: exchange: {exchange}, address: {addr[1]}")
except MySQLdb._exceptions.IntegrityError:
logger.warning(f"database integrityerror, address: {addr}")
continue
def find_balances():
addresses = read_all_from_db('select address from exchange_chain_address',
[])
for address in addresses:
addr = address['address']
print(addr)
balance = get_account_balance(addr)
print(balance)
if balance:
logger.info(
f"insert balance_of_address_history with values: {addr}, {balance}, usdt"
)
execute_sql(balance_of_address_history_sql,
(addr, "USDT", balance, "etherscan"))
eth_balance = get_eth_balance(addr)
if eth_balance:
logger.info(
f"insert exchange_balance with values: {addr}, {eth_balance}, eth"
)
execute_sql(balance_of_address_history_sql,
(addr, "ETH", balance, 'etherscan'))
def save_balance(balance):
now = datetime.datetime.utcnow()
execute_sql(balance_sql, [HYDAX_ADDR, "USDT", balance, 'tokenview', now])
def add_hydax_address():
try:
now = datetime.datetime.utcnow()
execute_sql(addr_sql, ('hydax', "BTC", HYDAX_ADDR, 'hydax', 'tokenview', now))
except MySQLdb._exceptions.IntegrityError:
pass
def add_address():
now = datetime.datetime.utcnow()
for address in USDT_TOKEN_VIEW_ADDRESSES:
print(f"add address: {address}")
execute_sql(exchange_chain_address_sql,
('renrenbit', 'BTC', address, 'renrenbit usdt token',
'tokenview', now))
for address in USDT_ERC_20_ADDRESSES:
print(f"add address: {address}")
execute_sql(exchange_chain_address_sql,
('renrenbit', 'ETH', address, 'renrenbit usdt erc20',
'tokenview', now))
for address in ETH_ERC_20_ADDRESSES:
print(f"add address: {address}")
execute_sql(exchange_chain_address_sql,
('renrenbit', 'ETH', address, 'renrenbit eth erc20',
'tokenview', now))
for address in BTC_TOKEN_VIEW_ADDRESSES:
print(f"add address: {address}")
execute_sql(
exchange_chain_address_sql,
('renrenbit', 'BTC', address, 'renrenbit btc', 'tokenview', now))
for address in HBTC_BTC_ADDRESSES:
print(f"add address: {address}")
execute_sql(exchange_chain_address_sql,
('hbtc', 'BTC', address, 'hbtc btc', 'tokenview', now))
for address in HYDAX_ADDR:
print(f"add address: {address}")
execute_sql(exchange_chain_address_sql,
('hydax', "BTC", address, 'hydax', 'tokenview', now))
def save_btc_balance(exchange, balance):
execute_sql(btc_balance_sql,
(exchange, "multiple", "BTC", balance, "tokenview"))
def save_btc_balance_with_address(address, balance):
execute_sql(erc20_balance_sql, [address, "BTC", balance, 'tokenview'])
def save_erc_20_balance(address, balance):
execute_sql(erc20_balance_sql, [address, "USDT", balance, 'tokenview'])
where currency = 'eth'
) as t_eth on
t_eth.exchange = A.exchange
and t_eth.date = t_btc.date
left join (
select exchange, balance, date
from erc_daily_balances
where currency = 'usdt'
) as t_usdt on
t_usdt.exchange = A.exchange
and t_usdt.date = t_btc.date
) as T
order by exchange, date
"""
execute_sql(sql_0)
execute_sql(sql_1)
execute_sql(sql_2)
execute_sql(sql_3)
data_list = read_all_from_db(sql_4, [args.date, args.date])
QUERY_HISTORY_PRICE = """
select
A.base, usd_price, A.format_date
from
bu_history_price_average join (
select
max(id) as id, base,
date_format(from_unixtime(timestamp), '%%Y-%%m-%%d') as format_date
from
bu_history_price_average
import sys
from utils.db_helper import execute_sql
exchange = sys.argv[1]
file_path = sys.argv[2]
with open(file_path) as file:
for line in file:
line_str = line.strip()
components = line_str.split(',')
address = components[0]
tag = components[1]
result = execute_sql(
'replace into exchange_eth_address (address, exchange, tag) values (%s, %s, %s)',
[address, exchange, tag])
print(f'row effect: {result}')
