[Saving CryptoCurrency market data in MySQL database] #cryptocurrency #mysql

We are going to use the Bittrex API to get market summary data

curl -X GET "https://bittrex.com/api/v1.1/public/getmarketsummaries" -o market_summaries.json

Each summary item looks like this

    {
      "MarketName": "USDT-ZEC",
      "High": 251.52,
      "Low": 237.11,
      "Volume": 915.5154001,
      "Last": 243.115,
      "BaseVolume": 223881.42994894,
      "TimeStamp": "2017-09-08T10:05:01.633",
      "Bid": 243.29000004,
      "Ask": 245.608,
      "OpenBuyOrders": 307,
      "OpenSellOrders": 780,
      "PrevDay": 242,
      "Created": "2017-07-14T17:10:10.673"
    }

So we need to setup a mysql database and create a table to store the above JSON structure

1. Create a new database and select it for further use

 DROP DATABASE `crypto-ticker-price-data`;
 
 CREATE DATABASE `crypto-ticker-price-data` DEFAULT CHARACTER SET = `utf8`;
 
 USE `crypto-ticker-price-data`;
 
 DROP TABLE `tickerdata`;

We’ll try using an online tool to generate the SQL

Trying https://editor.datatables.net/generator/ which generated the following SQL for the above JSON structure.

CREATE TABLE IF NOT EXISTS `tickerdata` (
	`id` int(10) NOT NULL auto_increment,
	`market_name` varchar(255),
	`last_price` numeric(18,9),
	`ask_price` numeric(18,9),
	`bid_price` numeric(18,9),
	`open_buy_orders` numeric(11,2),
	`open_sell_orders` numeric(11,2),
	`timestamp` datetime,
	`created` datetime,
	`volume` numeric(22, 10),
	`base_volume` numeric(22, 10),
	`prev_day` numeric(18, 9),
	`high` numeric(18, 9),
	`low` numeric(18,9),
	PRIMARY KEY( `id` )
);
# numeric(18, 9) means that we can store 9 digits on each side of decimal

Other similar tools

https://www.generatedata.com/

We’ll connect to MySQL from Python using PyMySQL -> https://github.com/PyMySQL/PyMySQL

Add PyMySQL to requirements.txt and a class to store data

pip install -r requirements.txt

class TickerStore:
    def __init__(self):
        logger.info("Setting up ticker store")
        self.connection = pymysql.connect(
            host=DB_HOST,
            user=DB_USER,
            password=DB_PASS,
            db=DB_NAME
        )

Based on some rough calculations, we will approximately store around 2 million rows every month

So it is important to calculate the cost of talking to database so that we can measure it over time.

For measuring elapsed time, we’ll try using the contexttimer library

Some example SQL queries

SELECT `market_name`, `timestamp`, `ask_price` FROM `tickerdata` WHERE `market_name`='BTC-XEM' order by `timestamp` desc limit 1000;

SELECT `market_name`, `timestamp`, `ask_price` FROM `tickerdata` WHERE `market_name`='BTC-XEM' order by `timestamp` desc

SELECT timestamp, UNIX_TIMESTAMP(timestamp), ROUND(UNIX_TIMESTAMP(timestamp)/(15 * 1)) AS timekey
FROM `tickerdata` 
WHERE `market_name`='BTC-XEM'
GROUP BY timekey;

SELECT timestamp, UNIX_TIMESTAMP(timestamp), ROUND(UNIX_TIMESTAMP(timestamp)/(15 * 1))
FROM `tickerdata` 
WHERE `market_name`='BTC-XEM';

SELECT timestamp, sec_to_time(time_to_sec(timestamp)- time_to_sec(timestamp)%(15*60)), SUM(ask_price) as intervals 
FROM `tickerdata` 
WHERE `market_name`='BTC-XEM'
group by intervals

namuan/saving_crypto_data.md