Настройка базы

https://poloniex.com/public?command=returnTradeHistory&currencyPair=USDT_BTC&start=1518628155&end=1518629000

$ sudo mysql -u root -p
(Можно задать пароль)

MariaDB [(none)]> create database polo;

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

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

MariaDB [(none)]> quit

$ mysql -u u -p polo

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

MariaDB [polo]> CREATE UNIQUE INDEX gtrade_id ON trade(gtrade_id);

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

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

MariaDB [polo]> describe trade;

+-----------+---------------+------+-----+-------------------+----------------+
| Field     | Type          | Null | Key | Default           | Extra          |
+-----------+---------------+------+-----+-------------------+----------------+
| id        | int(11)       | NO   | PRI | NULL              | auto_increment |
| ts        | timestamp     | NO   |     | CURRENT_TIMESTAMP |                |
| gtrade_id | int(11)       | NO   | UNI | NULL              |                |
| trade_id  | int(11)       | NO   | UNI | NULL              |                |
| date      | int(11)       | NO   | MUL | NULL              |                |
| type      | varchar(4)    | NO   |     | NULL              |                |
| rate      | decimal(30,8) | NO   | MUL | NULL              |                |
| amount    | decimal(30,8) | NO   |     | NULL              |                |
| total     | decimal(30,8) | NO   |     | NULL              |                |
+-----------+---------------+------+-----+-------------------+----------------+

9 rows in set (0.00 sec)

$ mysql -u u -p polo



MariaDB [exmo]> create table verify ( 

id int auto_increment primary key,

ts timestamp default current_timestamp,

st_time int not null,

end_time int not null,

count int not null);

MariaDB [polo]> CREATE UNIQUE INDEX end_time ON verify(end_time);

MariaDB [polo]> describe verify;
+----------+-----------+------+-----+-------------------+----------------+
| Field    | Type      | Null | Key | Default           | Extra          |
+----------+-----------+------+-----+-------------------+----------------+
| id       | int(11)   | NO   | PRI | NULL              | auto_increment |
| ts       | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| st_time  | int(11)   | NO   |     | NULL              |                |
| end_time | int(11)   | NO   | UNI | NULL              |                |
| count    | int(11)   | NO   |     | NULL              |                |
+----------+-----------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
 

MariaDB [polo]> delete from  verify; 

MariaDB [polo]> delete from  trade;
Query OK, 15 rows affected (0.00 sec)
 
python3 sql_pol.py
k =  13
[{'globalTradeID': 355582604, 'tradeID': 20851900, 'date': '2018-03-20 18:33:30',
 'type': 'buy', 'rate': '8805.27999977', 'amount': '0.01230000', 'total': '108.30494399'},
 {'globalTradeID': 355582602, 'tradeID': 20851899, 'date': '2018-03-20 18:33:29',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00017037', 'total': '1.50010785'},
 {'globalTradeID': 355582601, 'tradeID': 20851898, 'date': '2018-03-20 18:33:29',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00017037', 'total': '1.50010785'},
 {'globalTradeID': 355582599, 'tradeID': 20851897, 'date': '2018-03-20 18:33:29',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00017037', 'total': '1.50010785'},
 {'globalTradeID': 355582597, 'tradeID': 20851896, 'date': '2018-03-20 18:33:29',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00017037', 'total': '1.50010785'},
 {'globalTradeID': 355582593, 'tradeID': 20851895, 'date': '2018-03-20 18:33:28',
 'type': 'buy', 'rate': '8805.27999978', 'amount': '0.00085355', 'total': '7.51574674'},
 {'globalTradeID': 355582583, 'tradeID': 20851894, 'date': '2018-03-20 18:33:26',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00028395', 'total': '2.50017975'},
 {'globalTradeID': 355582581, 'tradeID': 20851893, 'date': '2018-03-20 18:33:26',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00028395', 'total': '2.50017975'},
 {'globalTradeID': 355582579, 'tradeID': 20851892, 'date': '2018-03-20 18:33:26',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00017037', 'total': '1.50010785'},
 {'globalTradeID': 355582578, 'tradeID': 20851891, 'date': '2018-03-20 18:33:26',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00028395', 'total': '2.50017975'},
 {'globalTradeID': 355582577, 'tradeID': 20851890, 'date': '2018-03-20 18:33:26',
 'type': 'sell', 'rate': '8805.00000000', 'amount': '0.00028395', 'total': '2.50017975'},
 {'globalTradeID': 355582576, 'tradeID': 20851889, 'date': '2018-03-20 18:33:26',
 'type': 'buy', 'rate': '8805.27999980', 'amount': '0.00400478', 'total': '35.26320923'},
 {'globalTradeID': 355582571, 'tradeID': 20851888, 'date': '2018-03-20 18:33:25',
 'type': 'buy', 'rate': '8805.27999980', 'amount': '0.00136569', 'total': '12.02528284'}]

MariaDB [polo]> select * from trade;
MariaDB [polo]> select * from trade;
+----+---------------------+-----------+----------+------------+------+---------------+------------+--------------+
| id | ts                  | gtrade_id | trade_id | date       | type | rate          | amount     | total        |
+----+---------------------+-----------+----------+------------+------+---------------+------------+--------------+
| 52 | 2018-03-20 21:33:36 | 355582571 | 20851888 | 1521570805 | buy  | 8805.27999980 | 0.00136569 |  12.02528284 |
| 53 | 2018-03-20 21:33:36 | 355582576 | 20851889 | 1521570806 | buy  | 8805.27999980 | 0.00400478 |  35.26320923 |
| 54 | 2018-03-20 21:33:36 | 355582577 | 20851890 | 1521570806 | sell | 8805.00000000 | 0.00028395 |   2.50017975 |
| 55 | 2018-03-20 21:33:36 | 355582578 | 20851891 | 1521570806 | sell | 8805.00000000 | 0.00028395 |   2.50017975 |
| 56 | 2018-03-20 21:33:36 | 355582579 | 20851892 | 1521570806 | sell | 8805.00000000 | 0.00017037 |   1.50010785 |
| 57 | 2018-03-20 21:33:36 | 355582581 | 20851893 | 1521570806 | sell | 8805.00000000 | 0.00028395 |   2.50017975 |
| 58 | 2018-03-20 21:33:36 | 355582583 | 20851894 | 1521570806 | sell | 8805.00000000 | 0.00028395 |   2.50017975 |
| 59 | 2018-03-20 21:33:36 | 355582593 | 20851895 | 1521570808 | buy  | 8805.27999978 | 0.00085355 |   7.51574674 |
| 60 | 2018-03-20 21:33:36 | 355582597 | 20851896 | 1521570809 | sell | 8805.00000000 | 0.00017037 |   1.50010785 |
| 61 | 2018-03-20 21:33:36 | 355582599 | 20851897 | 1521570809 | sell | 8805.00000000 | 0.00017037 |   1.50010785 |
| 62 | 2018-03-20 21:33:36 | 355582601 | 20851898 | 1521570809 | sell | 8805.00000000 | 0.00017037 |   1.50010785 |
| 63 | 2018-03-20 21:33:36 | 355582602 | 20851899 | 1521570809 | sell | 8805.00000000 | 0.00017037 |   1.50010785 |
| 64 | 2018-03-20 21:33:36 | 355582604 | 20851900 | 1521570810 | buy  | 8805.27999977 | 0.01230000 | 108.30494399 |
+----+---------------------+-----------+----------+------------+------+---------------+------------+--------------+
15 rows in set (0.00 sec)

MariaDB [polo]> SELECT COUNT(1) FROM trade;
+----------+
| COUNT(1) |
+----------+
|     3846 |
+----------+
1 row in set (0.00 sec)
  

Чтобы экспортировать БД

$ mysqldump -u u -p polo > polo-dump.sql

Импортировать базу пароль: secret (пользователь u)
  

Чтобы импортировать БД
В первый раз нужно ее создать:

$ sudo mysql -u root -p
(Можно задать пароль)

MariaDB [(none)]> create database polo;

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

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

MariaDB [(none)]> quit

$ mysql -u u -p polo

$ mysql -u u -p polo < polo-dump.sql

Некоторые команды делать не нужно: 

MariaDB [polo]> select * from trade;
Empty set (0.01 sec)
 

MariaDB [polo]> delete from  trade;
Query OK, 13 rows affected (0.02 sec)
 

MariaDB [polo]> show tables;
+----------------+
| Tables_in_polo |
+----------------+
| trade          |
+----------------+
1 row in set (0.00 sec)

MariaDB [polo]> drop table trade;
Query OK, 0 rows affected (0.00 sec)


MariaDB [polo]> SHOW TABLES IN polo;
+----------------+
| Tables_in_polo |
+----------------+
| trade          |
+----------------+