In [52]:

    

exchange = "btc_eth"
starttime = "2016-12-18 05:10:30"
use_now = True
MySQL_dbname = 'poloniex'
MySQL_user = 'polo'
MySQL_passwd = 'polo'


import time
import datetime

def now():
    return int(time.mktime(datetime.datetime.utcnow().timetuple()))

if use_now:
    print ("starting from now")
    print (datetime.datetime.utcnow())
    start_timestamp = now()
else:
    print ("starting from %s"%(starttime))
    start_timestamp = int(time.mktime(datetime.datetime.strptime(starttime, "%Y-%m-%d %H:%M:%S").timetuple()))

print (start_timestamp)


    

    




starting from now
2017-10-20 05:39:07.529345
1508449147

In [ ]:

    

import datetime
import requests

#currency_pair: currency pair expression which is supported in poloniex, to: unixtime value
def get_url(currency_pair, end_unix):
    poloniex_url_template = "https://poloniex.com/public?command=returnTradeHistory&currencyPair=%s&start=%s&end=%s"
    
    SIXMONTHS = 60*60*24*30*6
    ONEDAY = 60*60*24
    ONWWEEK = 7*ONEDAY
    
    date_range = ONEDAY
    start_unix = end_unix - date_range
    url = poloniex_url_template%(currency_pair, start_unix, end_unix)
    print (url)
    return url

def get_gmttime(lastdate):
    #print(datetime.datetime.strptime(lastdate, "%Y-%m-%d %H:%M:%S"))
    endtime = int(datetime.datetime.strptime(lastdate, "%Y-%m-%d %H:%M:%S").timestamp())+(9*60*60)
    #print(endtime)
    return endtime

def get_batch_list(total_size, batch_size=100):
    total = [a for a in range(0, total_size)]
    #print (total)
    
    res = list()
    while len(total) > 0:
        bs = min(batch_size, len(total))
        #print (bs)
        a = total[:bs]
        res.append(a)
        if bs < len(total):
            b = total[bs:]
            total = b
        else:
            total = []
            break
    return res
    

def get_exchange_data(lasttime):
    url = get_url(exchange.upper(), lasttime)
    r = requests.get(url)
    js = r.json()
    
    if len(js) > 0:
        lastdate = js[-1]['date']
        endtime = get_gmttime(lastdate)
        print ("downloaded %d items, by %s"%(len(js), lastdate))
        return (js, endtime)
    else:
        print ("No data found")
        return (js, "no date")

#print(js[:2])
#print(js[-2:])


    

In [ ]:

    

import MySQLdb

conn = MySQLdb.connect(db=MySQL_dbname, user=MySQL_user, passwd=MySQL_passwd)
curr = conn.cursor()


def execute_query(query):
    try:
        curr.execute(query)
    except (MySQLdb.Error, MySQLdb.Warning) as e:
        print ("MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
    except IndexError:
        print ("MySQL Error: [%s]" % str(e))

def create_table_if_not_exists(tablename):
    if not tablename:
        return None
    
    CREATE_TABLE_STATEMENT_TEMPLATE = """
    CREATE TABLE IF NOT EXISTS `%s` (
        `gTradeID` INT(11) UNSIGNED NOT NULL DEFAULT '0',
        `tradeID` INT(11) UNSIGNED NULL DEFAULT NULL,
        `total` FLOAT UNSIGNED NULL DEFAULT NULL,
        `rate` FLOAT UNSIGNED NULL DEFAULT NULL,
        `amount` FLOAT UNSIGNED NULL DEFAULT NULL,
        `type` CHAR(4) NOT NULL DEFAULT 'buy',
        `date` DATETIME NOT NULL,
        PRIMARY KEY (`gTradeID`),
        INDEX `date` (`date`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;
    """
    query = CREATE_TABLE_STATEMENT_TEMPLATE%(tablename)
    execute_query(query)
            
def get_db_size():
    curr.execute("SELECT COUNT(*) FROM %s"%(exchange))
    (number_of_rows,)=curr.fetchone()
    return number_of_rows


    

In [ ]:

    

#make insert statement
def add_data(trade_json):
    batch_size = 1000
    batch = get_batch_list(len(trade_json), batch_size)

    for bs in batch:
        query = "insert into %s values "%(exchange)

        first = True
        for b in bs:
            item = trade_json[b]
            #print (item)

            value = "(%d,%d,%s,%s,%s,'%s','%s')"%(item['globalTradeID'], item['tradeID'], item['total'], item['rate'], item['amount'], item['type'], item['date'])

            if first:
                first = False
            else:
                query += ','

            query += value
        #print ("processing %d"%(len(bs)))
        #print (query)
        
        execute_query(query)
        #print ("done@@")
    
    #print ("begin commit")
    if len(batch) > 0:
        conn.commit()
    #print ("end commit")
    
    print ("trade data count = %d"%(get_db_size()))


    

In [ ]:

    

create_table_if_not_exists(exchange)


lasttime = start_timestamp
prev_lasttime = 0
while prev_lasttime != lasttime:
    prev_lasttime = lasttime
    print (lasttime)
    (js_result, lasttime) = get_exchange_data(lasttime)
    #print js_result
    #continue
        
    if len(js_result) == 0:
        break
    
    #add trade data to db
    add_data(js_result)