def create_txs_table(table_name): create_txs_sql = """CREATE TABLE {} ( `id` int(11) NOT NULL AUTO_INCREMENT, `source` char(42) NOT NULL, `target` char(42) NOT NULL, `amount` varchar(32) NOT NULL, `tx` char(66) NOT NULL, `block_num` int(11) NOT NULL, `tx_seq` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `block_num_index` (`block_num`), FULLTEXT `target_index` (`target`), FULLTEXT `source_index` (`source`), FULLTEXT `tx_index` (`tx`) ) ENGINE=InnoDB""".format( table_name) exeSQL(create_txs_sql, True)
def initClusterFeaturesTable(): create_table_balance = ("CREATE TABLE {} (" "id INT NOT NULL AUTO_INCREMENT," "address CHAR(42)," "split_point_start INT NOT NULL, " "split_point_stop INT NOT NULL, " "in_num INT NOT NULL," "in_ethers FLOAT NOT NULL," "in_addrs_set_len INT NOT NULL," "in_dec_places INT NOT NULL," "out_num INT NOT NULL," "out_ethers FLOAT NOT NULL," "out_addrs_set_len INT NOT NULL," "out_dec_places INT NOT NULL," "init_balance FLOAT NOT NULL," "final_balance FLOAT NOT NULL," "PRIMARY KEY(id)) ; ".format(feature_table_name)) exeSQL(create_table_balance, True)
def query_account(addr): query_account_sql = ( "SELECT * FROM {} WHERE address = '{}' limit 1").format( table_name, addr) query_account_sql_result = exeSQL(query_account_sql) return query_account_sql_result
def drop_table(): drop_table_sql = """DROP TABLE account""" exeSQL(drop_table_sql, True)
def create_account_table(): create_account_sql = """CREATE TABLE account ( `id` int(11) NOT NULL AUTO_INCREMENT, `address` char(42) NOT NULL, `kind` enum('normal','sc','none') NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB""" exeSQL(create_account_sql, True)
def dropClusterFeatureTable(): drop_table_balance = ("DROP TABLE IF EXISTS {}".format(feature_table_name)) exeSQL(drop_table_balance, True)
def fetchAddressSet(account_type): fetch_addrs_sql = ("SELECT {} FROM {}").format(account_type, action_table_name) addrs = exeSQL(fetch_addrs_sql) flat_addrs = [item for sublist in addrs for item in sublist] return set((flat_addrs))
def insertFeaturesPerAddr(addr): if (len(queryClusterFeatures(addr)) > 0): return fetch_txs_sql = ("SELECT * FROM {} " "WHERE (source = '{}' OR target = '{}') ").format(action_table_name, addr, addr) txs = exeSQL(fetch_txs_sql) # Sort the txs sorted_txs = sorted(txs, key=operator.itemgetter(6, 7, 8)) split_points = [] last_tx_type = 'out' for index, tx in enumerate(sorted_txs): if tx[2] == addr: last_tx_type = 'out' continue else: if last_tx_type == 'out': split_points.append(index) last_tx_type = 'in' split_points.append(len(sorted_txs)) split_features = [] # print (split_points) for i in range(len(split_points)-1): sub_txs = sorted_txs[split_points[i]:split_points[i+1]] in_num = 0 out_num = 0 in_ethers = 0 out_ethers = 0 in_addrs_set = set() out_addrs_set = set() in_ethers_decimal_places_dict = {} out_ethers_decimal_places_dict = {} for tx in sub_txs: if tx[2] == addr: out_num += 1 out_ethers += int(tx[4], 16) / 10**18 out_addrs_set.add(tx[3]) out_ethers_decimal_places = - decimal.Decimal(str(int(tx[4], 16) / 10**18)).as_tuple().exponent if out_ethers_decimal_places in out_ethers_decimal_places_dict: out_ethers_decimal_places_dict[out_ethers_decimal_places] += 1 else: out_ethers_decimal_places_dict[out_ethers_decimal_places] = 1 else: in_num += 1 in_ethers += int(tx[4], 16) / 10**18 in_addrs_set.add(tx[2]) in_ethers_decimal_places = - decimal.Decimal(str(int(tx[4], 16) / 10**18)).as_tuple().exponent if in_ethers_decimal_places in in_ethers_decimal_places_dict: in_ethers_decimal_places_dict[in_ethers_decimal_places] += 1 else: in_ethers_decimal_places_dict[in_ethers_decimal_places] = 1 init_balance = checkBalance(addr, hex(sub_txs[0][6]-1)) final_balance = checkBalance(addr, hex(sub_txs[-1][6])) max_freq_in_ethers_decimal_places = max(in_ethers_decimal_places_dict, key=in_ethers_decimal_places_dict.get) if len(in_ethers_decimal_places_dict)>0 else -1 max_freq_out_ethers_decimal_places = max(out_ethers_decimal_places_dict, key=out_ethers_decimal_places_dict.get) if len(out_ethers_decimal_places_dict)>0 else -1 fecture = [addr, split_points[i], split_points[i+1], in_num, in_ethers, len(in_addrs_set), max_freq_in_ethers_decimal_places, out_num, out_ethers, len(out_addrs_set), max_freq_out_ethers_decimal_places, init_balance, final_balance] split_features.append(fecture) # return split_features # print (split_features) insertClusterFeatures(split_features)
def queryClusterFeatures(addr): query_feature_sql = ("SELECT * FROM {} WHERE address = '{}' limit 1").format(feature_table_name, addr) features = exeSQL(query_feature_sql) return features