# coding=utf-8 import datetime import json import requests import pymysql from loguru import logger import time class DbIf: def __init__(self, host="172.17.0.1", port=4419, user="root", password="IeQcJNnagkaFP1Or", dbname="btcdb"): self.conn = pymysql.connect(host=host, port=port, user=user, password=password, database=dbname, cursorclass=pymysql.cursors.DictCursor) def update_to_dailyindsv2(self, dt_utc, height_begin, height_end, lth_volume, frm, cvdd, realized_price, transferred_price, balanced_price, nvt_ratio, velocity): with self.conn.cursor() as cursor: print(dt_utc, height_begin, height_end, lth_volume, frm, cvdd, realized_price, transferred_price, balanced_price, nvt_ratio, velocity) # 调用消息订阅的api：向topic中储存rt_dailyindsv2e2的实时数据 # url = "http://10.168.2.125:7101/marketall/push/realtime/btc/dailyindsv2e1" # headers = {"accept": "application/json"} # data = {"unixdt":dt_utc,"height_begin":height_begin,"height_end":height_end,"lth_volume":lth_volume,"frm":frm,"cvdd":cvdd,"realized_price":realized_price,"transferred_price":transferred_price,"balanced_price":balanced_price,"nvt_ratio":nvt_ratio,"velocity":velocity} # response = requests.post(url=url, data=json.dumps(data), headers=headers) sql_insert = "REPLACE INTO rt_dailyindsv3e2 (unixdt, height_begin, height_end, lth_volume, frm, cvdd, realized_price, transferred_price, balanced_price, nvt_ratio, velocity" sql_insert = sql_insert + ") VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute(sql_insert, ( dt_utc, height_begin, height_end, lth_volume, frm, cvdd, realized_price, transferred_price, balanced_price, nvt_ratio, velocity)) ''' def update_to_realtimeindsv2(self, dt_utc, mempool_volume, mempool_fees): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO realtimeindsv2b (unixdt, mempool_volume, mempool_fees)" cursor.execute(sql_insert, (dt_utc, mempool_volume, mempool_fees)) ''' def update_to_dailyinds(self, dt_utc, height_begin, height_end, profitrate, fees, txs, new_address, total_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr, asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, nupl): with self.conn.cursor() as cursor: # 调用消息订阅的api：向topic中储存rt_dailyindsv2e1的实时数据 # 数据结构{dt_utc:'dt_utc'} try: url="https://coinbus.cc/api/v1/marketall/push/realtime/btc/dailyv2e1" headers = {"accept": "application/json"} data = {"unixdt":dt_utc,"height_begin":height_begin,"height_end":height_end,"profitrate":profitrate, "fees":fees,"txs":txs,"new_address":new_address,"total_address":total_address, "new_address_volume":new_address_volume,"active_address":active_address,"send_address":send_address, "receive_address":receive_address,"volume":volume,"eavolume":eavolume,"sopr":sopr,"asopr":asopr,"easopr":easopr, "lthsopr":lthsopr,"sthsopr":sthsopr,"asol":asol,"eaasol":eaasol,"dormancy":dormancy, "adormancy":adormancy,"eadormancy":eadormancy,"cdd":cdd,"sacdd":sacdd,"eacdd":eacdd,"day1":day1,"day7":day7, "day30": day30,"day60":day60,"day90":day90,"day180":day180,"day365":day365,"day730":day730, "csupply":csupply,"mintusd":mintusd,"sumcsupply":sumcsupply,"sumcdd":sumcdd,"sumeacdd":sumeacdd,"liveliness":liveliness, "ealiveliness":ealiveliness,"rprofit":rprofit,"rloss":rloss,"rplrate":rplrate, "price":price,"marketcap":marketcap,"rcap":rcap,"earcap":earcap,"mvrv":mvrv,"nupl":nupl} response = requests.post(url=url, data=json.dumps(data), headers=headers) except: print("api调用失败") sql_insert = "REPLACE INTO rt_dailyindsv3e1 (unixdt, height_begin, height_end, profitrate, fees, txs, new_address, total_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr," sql_insert = sql_insert + " asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, " sql_insert = sql_insert + " ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, nupl" sql_insert = sql_insert + ") VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, ( dt_utc, height_begin, height_end, profitrate, fees, txs, new_address, total_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr, asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, nupl)) self.conn.commit() ''' def update_to_dailyinds(self, dt_utc, height_begin, height_end, profitrate, fees, txs, new_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr, asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, lthmarketcap, lthrcap, sthmarketcap, sthrcap, lthmvrv, sthmvrv, nupl): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO dailyindsv1 (unixdt, height_begin, height_end, profitrate, fees, txs, new_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr," sql_insert = sql_insert + " asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, " sql_insert = sql_insert + " ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, lthmarketcap, lthrcap, sthmarketcap, sthrcap, lthmvrv, sthmvrv, nupl" sql_insert = sql_insert + ") VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, ( dt_utc, height_begin, height_end, profitrate, fees, txs, new_address, new_address_volume, active_address, send_address, receive_address, volume, eavolume, sopr, asopr, easopr, lthsopr, sthsopr, asol, eaasol, dormancy, adormancy, eadormancy, cdd, sacdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv, lthmarketcap, lthrcap, sthmarketcap, sthrcap, lthmvrv, sthmvrv, nupl)) self.conn.commit() ''' ''' # daily daily on-chain volume def query_from_dailyvolume(self, start_id=0, end_id=0, start_time="", end_time="", limit=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `dailyvolume`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) print(sql_query) cursor.execute(sql_query) return cursor.fetchall() ''' # newaddrs ''' def update_to_newaddr(self, dayutc, last_profit_rate, last_fees, last_txs, last_eatxs, last_newaddr_cnt, last_newaddr_vol, last_active_addr_cnt, last_tx_addr_cnt, last_rx_addr_cnt, last_vol_change, last_vol): with self.conn.cursor() as cursor: sql_query = "SELECT COUNT(id) FROM `newaddrs` WHERE unixdt=FROM_UNIXTIME(%s)" cursor.execute(sql_query, {dayutc, }) result = cursor.fetchone() # print(dt_utc) # print(result) if result is not None: if "COUNT(id)" in result: if result["COUNT(id)"] > 0: print("update") sql_update = 'UPDATE newaddrs SET `total`=%s, `amount`=%s, `active`=%s, `tx`=%s, `rx`=%s, `volume_change`=%s, `volume=%s`,`txs`=%s, `eatxs`=%s, `fees`=%s, `last_profit_rate`=%s WHERE unixdt=FROM_UNIXTIME(%s)' cursor.execute(sql_update, ( last_newaddr_cnt, last_newaddr_vol, last_active_addr_cnt, last_tx_addr_cnt, last_rx_addr_cnt, last_vol_change, last_vol, last_txs, last_eatxs, last_fees, last_profit_rate, dayutc)) else: print("insert") sql_insert = "INSERT INTO `newaddrs` (`unixdt`, `total`, `amount`, `active`, `tx`, `rx`, `volume_change`, `volume`, `txs`, `eatxs`, `fees`, `last_profit_rate`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, ( dayutc, last_newaddr_cnt, last_newaddr_vol, last_active_addr_cnt, last_tx_addr_cnt, last_rx_addr_cnt, last_vol_change, last_vol, last_txs, last_eatxs, last_fees, last_profit_rate)) self.conn.commit() ''' ''' def update_to_sellprofit(self, dayutc, current_price, block_buy_volume, block_sell_volume, block_sell_profit, last_height): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailybuysell` (`unixdt`, `price`, `buyvolume`, `sellvolume`, `sellprofit`, `height`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s)" #print(sql_insert) #print(datetime, txid, vout, voutalias, amount, height) cursor.execute(sql_insert, (dayutc, current_price, block_buy_volume, block_sell_volume, block_sell_profit, last_height)) self.conn.commit() ''' ''' def update_to_bigsellprofit(self, dayutc, current_price, tx_sell_average, tx_sell_amount, tx_sell_profit, days_earliest, days_latest, days_largest, days_current, tx_buy_address, txid, block_height): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `bigsell` (`unixdt`, `buyprice`, `sellprice`, `amount`, `profit`, `days_earliest`, `days_latest`, `days_largest`, `days_current`, `address`, `txid`, `height`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) # print(datetime, txid, vout, voutalias, amount, height) cursor.execute(sql_insert, ( dayutc, current_price, tx_sell_average, tx_sell_amount, tx_sell_profit, days_earliest, days_latest, days_largest, days_current, tx_buy_address, txid, block_height)) self.conn.commit() ''' ''' def update_to_dailycdd(self, dt_utc, cdd): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `dailycdd` (`unixdt`, `cdd`) VALUES (FROM_UNIXTIME(%s), %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, cdd)) self.conn.commit() ''' ''' def update_to_dailycdddays(self, dt_utc, dormancy, adormancy, eadormancy, cdd, acdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `dailycdddays` (`unixdt`, dormancy, adormancy, eadormancy, cdd, acdd, eacdd, `day1`, day7, day30, day60, day90, day180, day365, day730) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, ( dt_utc, dormancy, adormancy, eadormancy, cdd, acdd, eacdd, day1, day7, day30, day60, day90, day180, day365, day730)) self.conn.commit() ''' ''' def update_to_dailysopr(self, dt_utc, last_sopr, last_asopr, last_easopr, last_lth_sopr, last_sth_sopr): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `dailysopr` (`unixdt`, `sopr`, asopr, easopr, lth_sopr, sth_sopr) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, last_sopr, last_asopr, last_easopr, last_lth_sopr, last_sth_sopr)) self.conn.commit() ''' ''' def update_to_inds(self, dt_utc, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `inds` (`unixdt`, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, ( dt_utc, csupply, mintusd, sumcsupply, sumcdd, sumeacdd, liveliness, ealiveliness, rprofit, rloss, rplrate, price, marketcap, rcap, earcap, mvrv)) self.conn.commit() ''' # daily volume ''' def update_to_dailyvolume(self, dt_utc, volume): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `dailyvolume` (`unixdt`, `volume`) VALUES (FROM_UNIXTIME(%s), %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, volume)) self.conn.commit() ''' '''with self.conn.cursor() as cursor: sql_query = "SELECT COUNT(id) FROM `dailyvolume` WHERE unixdt=FROM_UNIXTIME(%s)" cursor.execute(sql_query, {dt_utc,}) result = cursor.fetchone() #print(dt_utc) #print(result) if result is not None: if "COUNT(id)" in result: if result["COUNT(id)"] > 0: print("update") sql_update = 'UPDATE dailyvolume SET `volume`=%s WHERE unixdt=FROM_UNIXTIME(%s)' cursor.execute(sql_update, (volume, dt_utc)) else: print("insert") sql_insert = "INSERT INTO `dailyvolume` (`unixdt`, `volume`) VALUES (FROM_UNIXTIME(%s), %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, volume)) self.conn.commit()''' ''' def update_to_dailyfees(self, dt_utc, fees): with self.conn.cursor() as cursor: sql_insert = "REPLACE INTO `dailyfees` (`unixdt`, `fees`) VALUES (FROM_UNIXTIME(%s), %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, fees)) self.conn.commit() ''' ''' def import_to_dailyvolume2(self, dt_utc, volume): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailyvolume` (`unixdt`, `volume`) VALUES (FROM_UNIXTIME(%s), %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, volume)) self.conn.commit() def delete_dailyvolume_data(self, config): with self.conn.cursor() as cursor: sql_query = "DELETE FROM `dailyvolume`" cursor.execute(sql_query) self.conn.commit() # daily market cap def query_from_marketcap(self, start_id=0, end_id=0, start_time="", end_time="", limit=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `dailyprice`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) print(sql_query) cursor.execute(sql_query) return cursor.fetchall() #daily price def import_to_dailyprice(self, dt_utc, price, volume, marketcap, csupply): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailyprice` (`unixdt`, `price`, `volume`, `marketcap`, `csupply`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s)" #print(sql_insert) cursor.execute(sql_insert, (dt_utc, price, volume, marketcap, csupply)) self.conn.commit() def update_to_dailyprice(self, dt_utc, price, volume, change): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailyprice` (`unixdt`, `price`, `volume`, `change`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s)" #print(sql_insert) cursor.execute(sql_insert, (dt_utc, price, volume, change)) self.conn.commit() def update_to_dailyprice2(self, dt_utc, price, volume, change, marketcap, csupply): with self.conn.cursor() as cursor: sql_query = "SELECT COUNT(id) FROM `dailyprice` WHERE unixdt=FROM_UNIXTIME(%s)" cursor.execute(sql_query, {dt_utc,}) result = cursor.fetchone() #print(dt_utc) #print(result) if result is not None: if "COUNT(id)" in result: if result["COUNT(id)"] > 0: print("update") sql_update = 'UPDATE dailyprice SET `price`=%s, `marketcap`=%s, `csupply`=%s, `volume`=%s, `change`=%s WHERE unixdt=FROM_UNIXTIME(%s)' cursor.execute(sql_update, (price, marketcap, csupply, volume, change, dt_utc)) else: print("insert") sql_insert = "INSERT INTO `dailyprice` (`unixdt`, `price`, `volume`, `change`, `marketcap`, `csupply`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s)" # print(sql_insert) cursor.execute(sql_insert, (dt_utc, price, volume, change, marketcap, csupply)) self.conn.commit() def update_dailypricechange(self): with self.conn.cursor() as cursor: sql_query = "SELECT unixdt,price FROM `dailyprice` order by unixdt" cursor.execute(sql_query) results = cursor.fetchall() prevprice = -1 for result in results: if prevprice < 0: prevprice = result["price"] else: #print(result["unixdt"], result["price"], result["marketcap"]) try: change = (result["price"]/prevprice - 1)*100 except: change = 0 #print(csupply) datestr = result["unixdt"] logger.debug(datestr.__format__('%Y-%m-%d') + " " + str(change)) sql_update = 'UPDATE dailyprice SET `change`=%s WHERE unixdt=%s' cursor.execute(sql_update, (str(change), result["unixdt"])) prevprice = result["price"] self.conn.commit() def delete_dailyprice_data(self, config): with self.conn.cursor() as cursor: sql_query = "DELETE FROM `dailyprice`" cursor.execute(sql_query) self.conn.commit() def delete_failed_blockvolume(self, height): with self.conn.cursor() as cursor: sql_insert = "DELETE FROM `bigamountvout` WHERE height=%s" cursor.execute(sql_insert, (height,)) sql_insert = "DELETE FROM `bigamounttx` WHERE height=%s" cursor.execute(sql_insert, (height,)) sql_insert = "DELETE FROM `blockamount` WHERE height=%s" cursor.execute(sql_insert, (height,)) self.conn.commit() #block check --- big amount for vout def query_from_bigamountvout(self, start_id=0, end_id=0, start_time="", end_time="", address="", limit=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `bigamountvout`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) print(sql_query) cursor.execute(sql_query) return cursor.fetchall() ''' def update_to_bigamountvout(self, datetime, txid, vout, voutn, vouttype, amount, height, days, buyin, sellout, profit): with self.conn.cursor() as cursor: # url = "http://10.168.2.125:7101/marketall/push/realtime/btc/dailyindsv2e1" # headers = {"accept": "application/json"} # data = {"unixdt":datetime,"vout":vout,"voutn":voutn,"vouttype":vouttype, # "amount":amount,"height":height,"txid":txid,"days":days,"buyin":buyin, # "sellout":sellout,"profit":profit} # response = requests.post(url=url, data=json.dumps(data), headers=headers) sql_insert = "INSERT INTO `rt_bigamountvoutv3e` (`unixdt`, `vout`, `voutn`, `vouttype`, `amount`, `height`, `txid`, days, buyprice, sellprice, profit) VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # print(sql_insert) # print(datetime, txid, vout, voutalias, amount, height) cursor.execute(sql_insert, (datetime, vout, voutn, vouttype, amount, height, txid, days, buyin, sellout, profit)) self.conn.commit() ''' # block check --- big amount tx def query_from_bigamounttx(self, start_id=0, end_id=0, start_time="", end_time="", address="", limit=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `bigamounttx`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) print(sql_query) cursor.execute(sql_query) return cursor.fetchall() def update_to_bigamounttx(self, datetime, txid, amount, height): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `bigamounttx` (`unixdt`, `amount`, `height`, `txid`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s)" #print(sql_insert) #print(datetime, txid, amount, height) cursor.execute(sql_insert, (datetime, amount, height, txid)) self.conn.commit() # block check --- per block amount def query_from_blockamount(self, start_id=0, end_id=0, start_time="", end_time="", limit=0, amount=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `blockamount`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) if amount > 0: sql_query = sql_query + " AND amount > " + str(amount) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if amount > 0: sql_query = sql_query + " AND amount > " + str(amount) else: if amount > 0: sql_query = sql_query + "WHERE amount > " + str(amount) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" if amount > 0: sql_query = sql_query + " AND amount > " + str(amount) else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" if amount > 0: sql_query = sql_query + " AND amount > " + str(amount) sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) cursor.execute(sql_query) return cursor.fetchall() def update_to_blockamount(self, datetime, blockid, amount, height): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `blockamount` (`unixdt`, `amount`, `height`, `blockid`) VALUES (FROM_UNIXTIME(%s), %s, %s, %s)" #print(sql_insert) #print(datetime, blockid, amount, height) cursor.execute(sql_insert, (datetime, amount, height, blockid)) self.conn.commit() def delete_node_data(self, config): with self.conn.cursor() as cursor: sql_query = "DELETE FROM `blockamount`" cursor.execute(sql_query) sql_query = "DELETE FROM `bigamountvout`" cursor.execute(sql_query) sql_query = "DELETE FROM `bigamounttx`" cursor.execute(sql_query) self.conn.commit() def update_realize_cap(self, dayutc, last_rv): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailyrcap` (`unixdt`, `rcap`) VALUES (FROM_UNIXTIME(%s), %s)" #print(sql_insert) #print(datetime, blockid, amount, height) cursor.execute(sql_insert, (dayutc, last_rv)) self.conn.commit() # daily realize cap def query_from_realizecap(self, start_id=0, end_id=0, start_time="", end_time="", limit=0): with self.conn.cursor() as cursor: sql_query = "SELECT * from `dailyrcap`" if start_id > 0: sql_query = sql_query + " WHERE id > " + str(start_id) if end_id > 0: sql_query = sql_query + " AND id < " + str(end_id) else: if end_id > 0: sql_query = sql_query + " WHERE id < " + str(end_id) if len(start_time) > 0: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(\'" + end_time + "\')" else: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(\'" + start_time + "\') AND UNIX_TIMESTAMP(NOW())" else: if len(end_time) > 0: sql_query = sql_query + " WHERE UNIX_TIMESTAMP(unixdt) BETWEEN \ UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(\'" + end_time + "\')" sql_query = sql_query + " order by `unixdt` desc" if limit > 0: sql_query = sql_query + " LIMIT " + str(limit) print(sql_query) cursor.execute(sql_query) return cursor.fetchall() def update_daily_addr(self, dayutc, last_add_cnt): with self.conn.cursor() as cursor: sql_insert = "INSERT INTO `dailyaddradd` (`unixdt`, `addcnt`) VALUES (FROM_UNIXTIME(%s), %s)" #print(sql_insert) #print(datetime, blockid, amount, height) cursor.execute(sql_insert, (dayutc, last_add_cnt)) self.conn.commit() def delete_daily_addr(self, config): with self.conn.cursor() as cursor: sql_query = "DELETE FROM `dailyaddradd`" cursor.execute(sql_query) self.conn.commit() def delete_daily_rv(self, config): with self.conn.cursor() as cursor: sql_query = "DELETE FROM `dailyrcap`" cursor.execute(sql_query) self.conn.commit() ''' def __del__(self): self.conn.close()