SQL Class Tutorial

The SQL class is used to interact with a SQL database (SQLite and MySQL are supported but future flavour support will be added). Currently only two flat tables are used, one which stores trades history and another which stores OLHCV price history at various frequencies.

Note: The complete script for this tutorial can be found here

##Setup SQL Tables

Note: You may skip this step if you have already run 'setup_sql()'

First step when utilizing the SQL class is to run the initial setup for your SQL database. The setup_sql method will perform two actions.

• Create a text file named auth_sql in your working directory. This text file stores your SQL login/location information so that you do not have to run the setup_sql method in the future. You must navigate your working directory to this text file when running bitQuant.

• Create new SQL database and empty trades and price tables

In this tutorial, we will utilize the SQLite flavour and create a new database file that is not in our working directory.

>> import bitquant as bq
>> bq.setup_sql()

-----SQL Database setup-----

=Select SQL type=
  (1) sqlite
  (2) MySQL

: 1


 Location/Database Name: /home/ross/test.db

Note: When setting the "Location/Database Name", if no filepath is given then bitQuant will look for the database name with the working directory

##Insert trade history into SQL

In order to have data to input and output from our SQL tables, we need to utilize the API Class which is explained in more detail in this tutorial.

We are setting the returned data limit to 5 to make aspects of the tutorial more clear later.

>> a = bq.api()
>> a.add_job('bitfinex','btcusd','trades', limit=5)
>> a.add_job('btcchina','btccny','trades', limit=5)

>> bf_trd = a.run('bitfinex','btcusd','trades')
>> bc_trd = a.run('btcchina','btccny','trades')

The commands above will return two sets of five rows of trade history data from Bitfinex and BTC China exchanges.

Using the insert(table_name, df) method of the SQL object will input the trade history DataFrames from above into the trades SQL table.

>> s = bq.sql()
>> s.insert('trades', bf_trd)
>> s.insert('trades', bc_trd)

##Pull (select) trade history from SQL

Now that we have added ten rows of trade history to our SQL table, we will review how to pull data from the SQL tables using the select(table_name, ...) method.

First we will perform a simple select which pulls all data from the trades table.

>> trd = s.select('trades')
>> print trd

        tid    price    amount  type   timestamp timestamp_ms  exchange  \
0  11771857   232.25  1.000000  sell  1442249099         None  bitfinex   
1  11771855   232.25  8.658419  sell  1442249091         None  bitfinex   
2  11771853   232.26  7.140000  sell  1442249091         None  bitfinex   
3  11771851   232.32  0.807386  sell  1442249091         None  bitfinex   
4  11771849   232.33  6.921195  sell  1442249091         None  bitfinex   
5  38177522  1488.34  0.500000  sell  1442249116         None  btcchina   
6  38177523  1488.24  1.000000  sell  1442249116         None  btcchina   
7  38177524  1488.19  0.571000   buy  1442249116         None  btcchina   
8  38177525  1488.19  0.100000   buy  1442249116         None  btcchina   
9  38177526  1488.19  0.006000   buy  1442249117         None  btcchina

The select method also allows you to set certain filters for pulling data (full documentation here). Below we will pull data from the trades table but only pull trade history for btcchina exchange.

>> trd = s.select('trades', exchange='btcchina')
>> print trd

        tid    price  amount  type   timestamp timestamp_ms  exchange  symbol
0  38177522  1488.34   0.500  sell  1442249116         None  btcchina  btccny
1  38177523  1488.24   1.000  sell  1442249116         None  btcchina  btccny
2  38177524  1488.19   0.571   buy  1442249116         None  btcchina  btccny
3  38177525  1488.19   0.100   buy  1442249116         None  btcchina  btccny
4  38177526  1488.19   0.006   buy  1442249117         None  btcchina  btccny