601 lines
33 KiB
Python
601 lines
33 KiB
Python
# 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()
|