Integrating a Python Trading Bot With Timescaledb
If you're a keen follower of our blog here, you'll have built a Python bot using the Binance rest-api and set up a TimescaleDB instance that streams live trade data straight from the exchange and finally converts it to OHLC candlestick format.
But how do we put all this together? We'll need to adapt our trading bot to use our database as our primary data source.
If you haven't followed our tutorials the same concepts will still apply for someone wanting to integrate Timescale and Python.
Getting Set Up
I have my timescale instance set up in a Docker container as per the documentation. And I also have a .env file in the same folder as my bot with my Binance API keys, as well as the password to the database
1API_KEY=aixorGU9FSQ-fake-api-key-ymc5nbfyF9WKki0J
2SECRET_KEY=lM6PUMGzj-fake-secret-key-VjMx56BqqqDpoWJFE4lZI
3db_pass=fake-db-password
You'll want to install the psycopg2 module (pip install psycopg2-binary) to interact with Timescale/Postgres.
Pulling our data from Timescale
Let's just concentrate on our fetch_klines function at the beginning of our file. Make sure to comment out the rest of the code whilst we test here.
1...
2res = client.get_exchange_info()
3
4connection = psycopg2.connect(user="postgres",
5 password=config("db_pass"),
6 host="127.0.0.1",
7 port="5432",
8 database="postgres")
9cursor = connection.cursor()
10
11def fetch_klines(asset):
12 pass
13...
If your timescale instance is set up with the default settings, you'll be able to use the same settings as I have above. Let's start re-writing our fetch_klines function.
1....
2def fetch_klines(asset):
3 query = '''SELECT * from ohlc_data_minute
4 WHERE symbol = %s
5 ORDER BY date desc
6 LIMIT 50'''
7 cursor.execute(query, ("ETHBTC",))
8 return cursor.fetchall();
9
10
11print(fetch_klines("BTCETH")[0])
All being well this should yield a tuple with all of our OHLCV information
1('ETHBTC', datetime.datetime(2022, 2, 1, 18, 52), 0.071785, 0.071808, 0.071775, 0.071776, 16.964199999999995)
Since I only use the time and the close price in my calculations for my bot, I'll edit the query a little
1...
2def fetch_klines(asset):
3 query = '''SELECT date, close from ohlc_data_minute
4 WHERE symbol = %s
5 ORDER BY date desc
6 LIMIT 50'''
7...
Which should yield a simpler tuple when printed out
1(datetime.datetime(2022, 2, 1, 18, 54), 0.071782)
In our query we have ordered the results by date descending, so the first element is the most recent. The reason we do this is so we can use LIMIT to grab only the first 50 candles. Otherwise we'd have to parse our entire table just to get the few candles we want.
However pandas-ta relies on us having the candles in ascending order (earliest first). So we'll have to sort that before passing it to the rest of our script. First off let's convert to a dataframe.
1...
2def fetch_klines(asset):
3 query = '''SELECT date, close from ohlc_data_minute
4 WHERE symbol = %s
5 ORDER BY date desc
6 LIMIT 50'''
7 cursor.execute(query, ("ETHBTC",))
8 results = cursor.fetchall();
9 df = pd.DataFrame(results, columns = ["time","price"])
10 return df
11...
if you're still following along with the bot template from our previous tutorial, be sure to use the column names "time" and "price" because that's what our other functions expect.
Finally, let's sort ascending by time
1...
2def fetch_klines(asset):
3 query = '''SELECT date, close from ohlc_data_minute
4 WHERE symbol = %s
5 ORDER BY date desc
6 LIMIT 50'''
7 cursor.execute(query, ("ETHBTC",))
8 results = cursor.fetchall();
9 df = pd.DataFrame(results, columns = ["time","price"])
10 df = df.sort_values(by="time")
11 return df
12...
Which after your print it out should yield a nice dataframe
1time price
249 2022-02-01 18:17:00 0.071611
348 2022-02-01 18:18:00 0.071634
447 2022-02-01 18:19:00 0.071592
546 2022-02-01 18:20:00 0.071633
645 2022-02-01 18:21:00 0.071642
At this point you should be able to uncomment the rest of the code and the bot should work as normal. Except that you'll now have super fast data retrieval from your database without having to make a bunch of REST API requests and risking getting banned.
Video Tutorial
If you'd prefer a video tutorial, you can check out this free course on our youtube channel: