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}')