def fetch_market_and_save(): """Fetches the most recent market-summaries snapshot and saves to the database. Returns the JSON result from the fetch operation. https://cryptowat.ch/docs/api#rate-limit roughly 3s """ # try: res = requests.get('https://api.cryptowat.ch/markets/summaries').json()['result'] except Exception as e: # raise Exception("Cryptowatch allowance out @{}".format(SLEEP)) print("Cryptowatch allowance out @{}".format(SLEEP)) return query = "" for key, val in res.items(): tablename = key.replace(':', '_').replace('-', '_') create_table_if_not_exists(tablename) # TODO sanitize via conn.execute(text(query), :a=a, :b=b) query += """ INSERT INTO {name} (last, high, low, change_percent, change_absolute, volume) VALUES ({last}, {high}, {low}, {change_percent}, {change_absolute}, {volume}); """.format( name=tablename, last=val['price']['last'], high=val['price']['high'], low=val['price']['low'], change_percent=val['price']['change']['percentage'], change_absolute=val['price']['change']['absolute'], volume=val['volume'] ) conn.execute(query) return res
def fetch_market_and_save(): """https://cryptowat.ch/docs/api#ohlc""" try: res = dict( gdax=requests.get('https://api.cryptowat.ch/markets/gdax/btcusd/ohlc').json()['result'], okcoin=requests.get('https://api.cryptowat.ch/markets/okcoin/btccny/ohlc').json()['result'] ) except Exception as e: # raise Exception("Cryptowatch allowance out @{}".format(SLEEP)) print("Cryptowatch allowance out @{}".format(SLEEP)) return for exchange, periods in res.items(): for period, candles in periods.items(): for candle in candles: query = """ INSERT INTO ohlc_{n} (close_time, period, open_price, high_price, low_price, close_price, volume) VALUES (:close_time, :period, :open_price, :high_price, :low_price, :close_price, :volume) ON CONFLICT DO NOTHING; -- there _will_ be overlap periods each iteration, which don't change """.format(n=exchange) conn.execute(text(query), period=period, close_time=candle[0], open_price=candle[1], high_price=candle[2], low_price=candle[3], close_price=candle[4], volume=candle[5] )
def fetch_market_and_save(): """https://cryptowat.ch/docs/api#ohlc""" try: res = dict( gdax=requests.get( 'https://api.cryptowat.ch/markets/gdax/btcusd/ohlc').json() ['result'], okcoin=requests.get( 'https://api.cryptowat.ch/markets/okcoin/btccny/ohlc').json() ['result']) except Exception as e: # raise Exception("Cryptowatch allowance out @{}".format(SLEEP)) print("Cryptowatch allowance out @{}".format(SLEEP)) return for exchange, periods in res.items(): for period, candles in periods.items(): for candle in candles: query = """ INSERT INTO ohlc_{n} (close_time, period, open_price, high_price, low_price, close_price, volume) VALUES (:close_time, :period, :open_price, :high_price, :low_price, :close_price, :volume) ON CONFLICT DO NOTHING; -- there _will_ be overlap periods each iteration, which don't change """.format(n=exchange) conn.execute(text(query), period=period, close_time=candle[0], open_price=candle[1], high_price=candle[2], low_price=candle[3], close_price=candle[4], volume=candle[5])
def create_table_if_not_exists(tablename): conn.execute(""" CREATE TABLE IF NOT EXISTS {name}( id SERIAL PRIMARY KEY, last DOUBLE PRECISION, high DOUBLE PRECISION, low DOUBLE PRECISION, change_percent DOUBLE PRECISION, change_absolute DOUBLE PRECISION, volume DOUBLE PRECISION, ts TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL ); CREATE INDEX IF NOT EXISTS {name}_ts_idx ON {name} (ts); """.format(name=tablename))
def create_table_if_not_exists(name): conn.execute(""" CREATE TABLE IF NOT EXISTS ohlc_{n} ( close_time INTEGER NOT NULL, -- TIMESTAMP NOT NULL, period VARCHAR(16) NOT NULL, -- "60" for 1min candles, "180" for 3m, etc open_price DOUBLE PRECISION, high_price DOUBLE PRECISION, low_price DOUBLE PRECISION, close_price DOUBLE PRECISION, volume DOUBLE PRECISION, PRIMARY KEY (close_time, period) ); """.format(n=name))
# raise Exception("Cryptowatch allowance out @{}".format(SLEEP)) print("Cryptowatch allowance out @{}".format(SLEEP)) return query = "" for key, val in res.items(): tablename = key.replace(':', '_').replace('-', '_') create_table_if_not_exists(tablename) # TODO sanitize via conn.execute(text(query), :a=a, :b=b) query += """ INSERT INTO {name} (last, high, low, change_percent, change_absolute, volume) VALUES ({last}, {high}, {low}, {change_percent}, {change_absolute}, {volume}); """.format( name=tablename, last=val['price']['last'], high=val['price']['high'], low=val['price']['low'], change_percent=val['price']['change']['percentage'], change_absolute=val['price']['change']['absolute'], volume=val['volume'] ) conn.execute(query) return res i = 0 while True: fetch_market_and_save() time.sleep(SLEEP) i += 1 if i % 100 == 0: print("gdax_btcusd.count: ", conn.execute("select count(*) from gdax_btcusd").fetchone().count)
# raise Exception("Cryptowatch allowance out @{}".format(SLEEP)) print("Cryptowatch allowance out @{}".format(SLEEP)) return for exchange, periods in res.items(): for period, candles in periods.items(): for candle in candles: query = """ INSERT INTO ohlc_{n} (close_time, period, open_price, high_price, low_price, close_price, volume) VALUES (:close_time, :period, :open_price, :high_price, :low_price, :close_price, :volume) ON CONFLICT DO NOTHING; -- there _will_ be overlap periods each iteration, which don't change """.format(n=exchange) conn.execute(text(query), period=period, close_time=candle[0], open_price=candle[1], high_price=candle[2], low_price=candle[3], close_price=candle[4], volume=candle[5] ) i = 0 while True: create_table_if_not_exists('gdax') create_table_if_not_exists('okcoin') fetch_market_and_save() print("ohlc.count: ", conn.execute("select count(*) from ohlc_gdax where period='60'").fetchone().count) time.sleep(SLEEP) i += 1
for exchange, periods in res.items(): for period, candles in periods.items(): for candle in candles: query = """ INSERT INTO ohlc_{n} (close_time, period, open_price, high_price, low_price, close_price, volume) VALUES (:close_time, :period, :open_price, :high_price, :low_price, :close_price, :volume) ON CONFLICT DO NOTHING; -- there _will_ be overlap periods each iteration, which don't change """.format(n=exchange) conn.execute(text(query), period=period, close_time=candle[0], open_price=candle[1], high_price=candle[2], low_price=candle[3], close_price=candle[4], volume=candle[5]) i = 0 while True: create_table_if_not_exists('gdax') create_table_if_not_exists('okcoin') fetch_market_and_save() print( "ohlc.count: ", conn.execute("select count(*) from ohlc_gdax where period='60'"). fetchone().count) time.sleep(SLEEP) i += 1