Part One: Create a schema and tables within a PostgreSQL database to support data collection from a REST API
Note: I recommend going through a tutorial on how to create a PostgreSQL database using Amazon RDS prior to proceeding. I’ve written one here, and it’s designed to integrate seamlessly with this two part series.
For demonstration purposes, I chose to use the Cryptowatch REST API. However, if you’ve selected another REST API and have an idea of the data you’ll be collecting from it, you’re most likely ready to go ahead and create a schema and tables for your PostgreSQL database. With that, feel free to skip over the first section where I discuss the Cryptowatch API.
Creating a Cryptowatch account and information about the Cryptowatch REST API
The Cryptowatch API is a good starting point if you’re interested in collecting data from more than one cryptocurrency exchange. Why? For now, getting initial access to the Cryptowatch API is free with an account. In addition, using one API to collect data across multiple exchanges assists with consistent formatting.
To create a Cryptowatch account, click here.
How do you get a public API key once you’ve created an account?
- Make sure you’re signed into your account and on the home page of the Cryptowatch website.
- Click “My Account” located in the top right hand corner of the home page.
- Select “API Access” within the drop down menu.
- To get a public key, scroll down on the API Access page until you see “Generate a key.” Click “Generate key.” Once you do so, you should now see a “Public key” within your “API Keys.”
If you’re following along with this tutorial, it’s a good idea to go ahead and copy the public key and paste it into a safe, accessible place for ease of access later.
Exchanges and trading pairs
I selected three exchanges and trading pairs to collect data on from the Cryptowatch API. These can be easily adjusted based on your own objectives.
Exchanges (The exchanges Cryptowatch supports can be found here.)
- Bitfinex
- Coinbase Pro
- HitBTC
Trading pairs
- BCH-BTC
- ETH-BTC
- LTC-BTC
What does the data returned by the API look like?
The format of the data returned by the Cryptowatch API will inform how the tables are structured within the database schema. For this walk through, I’m interested in collecting OHLC data. As we see from the image directly below, each candlestick returned consists of closing time (integer value), open (float value), high (float value), low (float value), close (float value) and base volume (float value).
Cryptowatch offers the option to collect candlesticks for different time periods, 1 min, 5 min and 1 hour.
From the most recent timestamp, the API goes back approximately:
- 12 hours for 1 min candlesticks
- 3 days for 5 min candlesticks
- 3 weeks for 1 hour candlesticks
Create a schema and tables within a PostgreSQL database.
First, open pgAdmin and make sure you’re within the desired server and database.
Schema
The primary purpose of a schema is to create a logical group of objects (i.e. tables) and give structure to the data.
Step 1- To create a schema, right click on “Schemas.” Click on “Create” and then “Schema…”
Step 2- Give the schema a name, and then save.
Step 3- Verify that the schema has been created by looking at the side bar on the left hand side. Just like that, you now have a schema.
Tables
Note: Tables can be created directly within pgAdmin, however, I use python and SQL code instead. For ease and simplification, I also suggest using a Jupyter Notebook or python notebook in Google Colab to run the code below.
Step 1- Within your notebook, make sure the following packages and libraries are imported.
import pandas as pd
import requests
import psycopg2 as psStep 2- Create ONE list that contains the table names of the exchanges/trading pairs you’re collecting data on.
# Lists of the table names for each exchange and trading pair.bitfinex_table_names = ["bitfinex_bch_btc", "bitfinex_eth_btc", "bitfinex_ltc_btc"]coinbase_pro_table_names = ["coinbase_pro_bch_btc", "coinbase_pro_eth_btc", "coinbase_pro_ltc_btc"]hitbtc_table_names = ["hitbtc_bch_btc", "hitbtc_eth_btc", "hitbtc_ltc_btc"] # Consolidate each of the separate table name lists above into one list.table_list = bitfinex_table_names + coinbase_pro_table_names + hitbtc_table_names
Step 3- Define the credentials used to connect to the database and API.
credentials = {"POSTGRES_ADDRESS" : "FILL THIS IN",
"POSTGRES_PORT" : "FILL THIS IN",
"POSTGRES_USERNAME" : "FILL THIS IN",
"POSTGRES_PASSWORD" : "FILL THIS IN",
"POSTGRES_DBNAME" : "FILL THIS IN",
"API_KEY" : "FILL THIS IN"}# This is a more defined idea of what your credentials should look like.credentials = {"POSTGRES_ADDRESS" : "example.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com",
"POSTGRES_PORT" : "5432",
"POSTGRES_USERNAME" : "test123",
"POSTGRES_PASSWORD" : "xxxxxxxxx",
"POSTGRES_DBNAME" : "example",
"API_KEY" : "xxxxxxxxxxxxxxxxxxxx"}
4. Establish a connection to the database and create a table structure consistent with the format of your data. (Note: Notice how I explicitly follow the format of the OHLC data from the Cryptowatch API to inform the structure of my tables.)
# Create a connection to the database.conn = ps.connect(host=credentials['POSTGRES_ADDRESS'],port=credentials['POSTGRES_PORT'],user=credentials['POSTGRES_USERNAME'],password=credentials['POSTGRES_PASSWORD'],database=credentials['POSTGRES_DBNAME'])# Create a cursor.cur = conn.cursor()# Loop through table_list.for table_name in table_list:# example1 is the name of the schema.cur.execute('''CREATE TABLE example1.{table_name}(closing_time integer,open float,high float,low float,close float,base_volume float);'''.format(table_name=table_name))# Commit and close.conn.commit()print("Tables created successfully!")conn.close()
After running the code above, look within pgAdmin to verify that tables have been created. Success! 🙌
Primary Accomplishments 👏
· Create a Cryptowatch account and get a public API key.
· Create a schema using pgAdmin.
· Create tables within the database schema using python and SQL code.
I’d love to connect! The best place to find me is on LinkedIn. :)
