Правим def get_ticks()

def get_ticks(pair, PER):
    print('PER =', PER)
    #_SQL = """select * from trade"""
    x=int(time.time())
    pr = PER * 60
    x= x - pr*100
    _SQL = """SELECT
                    FLOOR(MIN(`date`)/%s)*%s AS date,
                    SUBSTRING_INDEX(MIN(CONCAT(`date`, '_', id, '_', price)), '_', -1) AS `open`,
                    MAX(price) AS high,
                    MIN(price) AS low,
                    SUBSTRING_INDEX(MAX(CONCAT(`date`, '_', id, '_', price)), '_', -1) AS `close`
              FROM trade
              WHERE date >= %s
              GROUP BY FLOOR(`date`/%s)
              ORDER BY date"""
    db = mysql.connector.connect(host="localhost", user="u", passwd="secret", db="exmo")
    cur = db.cursor()
    cur.execute(_SQL,(pr,pr,x,pr))
    chart_data = {} # сформируем словарь с ценой закрытия по PERIOD минут
    for item in cur.fetchall():
        d = item[0] # Округляем время сделки до PERIOD минут
        if not d in chart_data:
            chart_data[d] = {'open':0, 'close':0, 'high':0, 'low':0}
        chart_data[d]['close'] = float(item[4])
        chart_data[d]['open'] = float(item[1])
        chart_data[d]['high'] = float(item[2])
        chart_data[d]['low'] = float(item[3])
    cur.close()
    db.close()
    return chart_data
------------------------------------------------------------------------------------------------------------------------
Поясним как работает:
SUBSTRING_INDEX(MAX(CONCAT(`date`, '_', id, '_', price)), '_', -1) AS `close`

Мы замешиваем date, id, price:
1519478976_57723762_1090.00000000
Из записей с одинаковым временем берем с большим id ( функция MAX())
Далее выделяем цену функцией SUBSTRING_INDEX() - 1090.00000000

Python индикатор MACD

import numpy
import talib
import requests
import json
import time

from matplotlib.finance import candlestick2_ohlc
import matplotlib.animation as animation

import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
from datetime import datetime
import mysql.connector

PERIOD = 120 # Период в минутах для построения свечей

BEAR_PERC = 100
BULL_PERC =  0
PAIR = 'BTC_USD'

fig, ax = plt.subplots(3, sharex=True)
fig.comment = plt.figtext(.7,.05, '')

db = mysql.connector.connect(host="localhost", user="u", passwd="secret", db="exmo")
#db = mysql.connector.connect(host="10.8.1.1", user="u", passwd="secret", db="exmo")
cur = db.cursor()

def update_graph(interval):
    x=int(time.time())
    per = PERIOD * 60
    x= x - per*100
    _SQL = """SELECT
                    FLOOR(MIN(`date`)/%s)*%s AS date,
                    SUBSTRING_INDEX(MIN(CONCAT(`date`, '_', id, '_', price)), '_', -1) AS `open`,
                    MAX(price) AS high,
                    MIN(price) AS low,
                    SUBSTRING_INDEX(MAX(CONCAT(`date`, '_', id, '_', price)), '_', -1) AS `close`
              FROM trade              WHERE date >= %s
              GROUP BY FLOOR(`date`/%s)
              ORDER BY date"""
    cur.execute(_SQL,(per,per,x,per))
    chart_data = {} # сформируем словарь с ценой закрытия по PERIOD минут
    for item in cur.fetchall():
        d = item[0] # Округляем время сделки до PERIOD минут
        if not d in chart_data:
            chart_data[d] = {'open':0, 'close':0, 'high':0, 'low':0}
        chart_data[d]['close'] = float(item[4])
        chart_data[d]['open'] = float(item[1])
        chart_data[d]['high'] = float(item[2])
        chart_data[d]['low'] = float(item[3])
    quotes = {}
    quotes['open']=numpy.asarray([chart_data[item]['open'] for item in sorted(chart_data)])
    quotes['close']=numpy.asarray([chart_data[item]['close'] for item in sorted(chart_data)])
    quotes['high']=numpy.asarray([chart_data[item]['high'] for item in sorted(chart_data)])
    quotes['low']=numpy.asarray([chart_data[item]['low'] for item in sorted(chart_data)])
    xdate=[datetime.fromtimestamp(item) for item in sorted(chart_data)]
    ax[0].xaxis.set_major_locator(ticker.MaxNLocator(6))

    def chart_date(x,pos):
        try:
            return xdate[int(x)]
        except IndexError:
            return ''

    ax[0].clear()
    ax[0].xaxis.set_major_formatter(ticker.FuncFormatter(chart_date))
    ax[0].grid(True)
    ax[0].minorticks_on()
    # Customize the major grid
    ax[0].grid(which='major', linestyle='-', linewidth='0.5', color='red')
    # Customize the minor grid
    ax[0].grid(which='minor', linestyle=':', linewidth='0.5', color='black')
    candlestick2_ohlc(ax[0], quotes['open'],quotes['high'],quotes['low'],quotes['close'],width=0.6)
    fig.autofmt_xdate()
    fig.tight_layout()
    macd, macdsignal, macdhist = talib.MACD(quotes['close'], fastperiod=12, slowperiod=26, signalperiod=9)
    #macd, macdsignal, macdhist = talib.MACDFIX(quotes['close'], signalperiod=9)
    #real = talib.RSI(quotes['close'], timeperiod=14)
    #real = talib.AROONOSC(quotes['high'], quotes['low'], timeperiod=14)
    ax[1].clear()
    ax[1].grid(True)
    ax[1].minorticks_on()
    # Customize the major grid
    ax[1].grid(which='major', linestyle='-', linewidth='0.5', color='red')
    # Customize the minor grid
    ax[1].grid(which='minor', linestyle=':', linewidth='0.5', color='black')
    ax[1].plot(macd, color="y")
    ax[1].plot(macdsignal)
    idx = numpy.argwhere(numpy.diff(numpy.sign(macd - macdsignal)) != 0).reshape(-1) + 0
    inters = []
    for offset, elem in enumerate(macd):
        if offset in idx:
            inters.append(elem)
        else:
            inters.append(numpy.nan)
    ax[1].plot(inters, 'ro')
    hist_data = []
    hist_data1 = []
    max_v = 0

    for offset, elem in enumerate(macdhist):
        activity_time = False
        curr_v = macd[offset] - macdsignal[offset]
        if abs(curr_v) > abs(max_v):
            max_v = curr_v
        perc = curr_v/max_v

        if ((macd[offset] > macdsignal[offset] and perc*100 > BULL_PERC)  or (macd[offset] < macdsignal[offset] and perc*100 < (100-BEAR_PERC)) ):
        #if       (   (macd[offset] - macdsignal[offset] > 0) # восходящий тренд
                #):
            #v = 1
            v=curr_v
            activity_time = True
        else:
            v = 0
            #v = -1
        hist_data.append(v)
        v1 = 0
        if curr_v < 0:
            v1 = curr_v
        hist_data1.append(v1)
        if offset in idx and not numpy.isnan(elem):
            # тренд изменился
            max_v = curr_v = 0 # обнуляем пик спреда между линиями
    ax[2].clear()
    ax[2].grid(True)
    ax[2].minorticks_on()
    # Customize the major grid
    ax[2].grid(which='major', linestyle='-', linewidth='0.5', color='red')
    # Customize the minor grid
    ax[2].grid(which='minor', linestyle=':', linewidth='0.5', color='black')
    #ax[2].plot(real, color="y")
    ax[2].fill_between([x for x in range(len(macdhist))], 0, hist_data, facecolor='green', interpolate=True)
    ax[2].fill_between([x for x in range(len(macdhist))], 0, hist_data1, facecolor='red', interpolate=True)
    plt.gcf().texts.remove(fig.comment)
    fig.comment = plt.figtext(.6,.05, '%s %s%s' % (PAIR, time.ctime(), ' ТОРГУЕМ!!!! ' if activity_time else ''), style='italic', bbox={'facecolor':'red' if activity_time else 'green', 'alpha':0.5, 'pad':10})

ani = animation.FuncAnimation(fig, update_graph, interval=1000)
plt.show()

Python добавление данных mysql

import numpy
#import talib
import requests
import json
import time
import mysql.connector
#from matplotlib.finance import candlestick2_ohlc
#i#mport matplotlib.animation as animation
#import matplotlib.pyplot as plt
#import matplotlib.ticker as ticker
from datetime import datetime


def update_mysql():
    PAIR = 'BTC_USD'
    db = mysql.connector.connect(host="localhost", user="u", passwd="secret", db="exmo")
    cur = db.cursor()
    resource = requests.get('https://api.exmo.com/v1/trades/?pair=%s&limit=500' % PAIR)
    #resource = requests.get('https://api.exmo.com/v1/trades/?pair=%s&limit=10000' % PAIR)
    data = json.loads(resource.text)
    #print(data[PAIR][1])
    chart_data = {} # сформируем словарь с ценой закрытия по PERIOD минут
    #print(data[PAIR][1]['date'])
    #print(int(float(data[PAIR][1]['date'])/(PERIOD*60))*(PERIOD*60))
    #print('-----------------------------')
    for item in reversed(data[PAIR]):
        _SQL = """insert ignore into trade
                  (trade_id, type, amount, quantity, date, price)
                  values
                  (%s,%s,%s,%s,%s,%s)"""
        cur.execute(_SQL, (item['trade_id'],item['type'],item['amount'],item['quantity'],item['date'],item['price']))
        db.commit()
    #cur.close()
    #db.close()
   
    FLAG = False
    if FLAG:
        _SQL = """select * from trade"""
        cur.execute(_SQL)
        for row in cur.fetchall():
            print(row)
    db.commit()
    cur.close()
    db.close()
    return()
# Бесконечный цикл процесса - основная логика
while True:
    try:
        update_mysql()
        time.sleep(1)
        print('работаем!')
    except Exception as e:
        print(e)

В обновлялке стоит лимит 500 записей, поставьте 10000 что бы не было пропусков, а затем поставьте обратно 500.

Установка драйвера базы данных MySQL для Python

-------------------------------------------------------------------------------------------------------
ubuntu 16.04

$ sudo apt update

$ sudo apt upgrade 

$ sudo apt install python3-mysql.connector

$ sudo apt install mariadb-server -y

---------------------------------------------------------------- 

для 17.10:

$ sudo systemctl restart mariadb

-----------------------------------------------------------------

$ sudo mysql -u root -p

(Можно задать пароль)

Создать базу: 

MariaDB [(none)]> create database exmo;

Задать учетную запись u для базы exmo с паролем 

MariaDB [(none)]> grant all on exmo.* to 'u' identified by 'secret'; 

MariaDB [(none)]> quit

{'trade_id': 49165408, 'type': 'sell', 'amount': '33.9810928', 'quantity': '0.00364604', 'date': 1518623170, 'price': '9320'}

$ mysql -u u -p exmo

MariaDB [exmo]> create table trade ( 
id int auto_increment primary key,
ts timestamp default current_timestamp,
trade_id int not null,
type varchar(4) not null,
amount decimal(30,8) not null,
quantity decimal(30,8) not null,
date int not null,
price decimal(30,8) not null );

MariaDB [exmo]> CREATE UNIQUE INDEX trade_id ON trade(trade_id);

MariaDB [exmo]> CREATE INDEX date ON trade(date);

MariaDB [exmo]> CREATE INDEX price ON trade(price); 

число строк: 

MariaDB [exmo]> SELECT COUNT(1) FROM trade;

MariaDB [exmo]> SELECT COUNT(1) FROM trade;
+----------+
| COUNT(1) |
+----------+
|    10403 |
+----------+ 

MariaDB [exmo]> describe trade;
+----------+---------------+------+-----+-------------------+----------------+
| Field    | Type          | Null | Key | Default           | Extra          |
+----------+---------------+------+-----+-------------------+----------------+
| id       | int(11)       | NO   | PRI | NULL              | auto_increment |
| ts       | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |
| trade_id | int(11)       | NO   | UNI | NULL              |                |
| type     | varchar(4)    | NO   |     | NULL              |                |
| amount   | decimal(30,8) | NO   |     | NULL              |                |
| quantity | decimal(30,8) | NO   |     | NULL              |                |
| date     | int(11)       | NO   | MUL | NULL              |                |
| price    | decimal(30,8) | NO   | MUL | NULL              |                |
+----------+---------------+------+-----+-------------------+----------------+
 

MariaDB [exmo]> DROP TABLE trade;

MariaDB [exmo]> SHOW TABLES IN exmo;
+----------------+
| Tables_in_exmo |
+----------------+
| trade          |
+----------------+ 

Check ALL Database Size in MySQL: 

$ mysql -u u -p exmo

MariaDB [exmo]> SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024
"Database Size (MB)"  FROM information_schema.TABLES GROUP BY table_schema;

+--------------------+--------------------+
| Database Name      | Database Size (MB) |
+--------------------+--------------------+
| exmo               |        97.10937500 |
| information_schema |         0.07812500 |
+--------------------+--------------------+

 

$ python3
>>> import mysql.connector
>>> db = mysql.connector.connect(host="localhost", user="u", passwd="secret", db="exmo")
>>> cur = db.cursor()
>>> cur.execute("select * from trade")
>>> for row in cur.fetchall(): print(row[0])
>>> db.close()
Изучение MySQL / MariaDB для начинающих
Индексы в MySQL
как правильно реализовать вставку записи если она не существует
ImportError: No module named 'MySQL'
How to Connect to MySQL Database from Python With Example
Group OHLC-Stockmarket Data into multiple timeframes - Mysql
How to connect Python programs to MariaDB
MySQL Connector/Python Developer Guide

Настройка удаленного подключения к mysql
Самоучитель Python
$

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

Это означает, что mysql слушает только локальные соединения и игнорирует внешние подключения.

Разрешим внешние подключения.

---------------------------------------------------------------------------------------------

для mysql:

 Для этого отредактируем /etc/mysql/my.cnf, закомментировав две строки:

$ sudo nano /etc/mysql/my.cnf

---------------------------------------------------------------------------------------

Для MariaDB:

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

#bind-address           = 127.0.0.1
bind-address           = 0.0.0.0

$ sudo systemctl restart mysql

$ netstat -an | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:* 

$ sudo mysql -u root -p

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD("secret"), plugin = "mysql_native_password" WHERE User = "u"; 

-----------------------------------------------------------------------------------
$ sudo ufw allow from 10.8.1.0/24 proto tcp to any port 3306
$ sudo ufw reload
$ sudo ufw status

Group OHLC-Stockmarket Data into multiple timeframes - Mysql
Overlap Studies Functions