SQLite example

lampalork

Dear Community,

Does anyone has a SQLite example (database schema + class extending AbstractDatabase) that she is willing to share?
For now, I'm just looking at setting of simple database with one table that will host daily bars (symbol, date, open, high, low, close, volume).

Thanks a lot in advance
Lamp'

run-out

I have examples but could you be more clear what you want to have/do?

lampalork

Hi @run-out, essentially, I want to store data in sqlite, create data feeds for each symbol from SQLite (instead of genericCsv of Panda feed) and run a backtest. Down the road, I want to snap latest bars from broker and append these prices to sqlite table.
Regards
Lamp'

vladisld

Please take a look at the PSQL data feed implementation suggested in the following PR:

https://github.com/mementum/backtrader/pull/393

it could be relatively easily converted to use SQLite API. However, you need an additional code to store new data since the above PR is only for data feed.

run-out

Did @vladisld answer your question or are you still looking for a straight up sqlite3 solution?

lampalork

It is a partial match; I was hoping someone had already ready-to-run code. But if nobody has anything better; I'll find some time to look at this PR and adapt it. Thanks for checking @run-out

vladisld

Just out of curiosity, why SQLite and not some time series database like Influxdb?

lampalork

In short, I'm designing an Expert Advisor based on backtrader. I worked with daily bars. SQLite will be clearly fast enough for my needs (in fact, I'm already ok with CSV perf). I will be snapping latest market data from a broker (for this; it will be easier/cleaner to add data to the database than CSV), compute signals and list down order required for rebalancing the portfolio.

run-out

@lampalork In my case I simply connect to sqlite3 using pandas, create a dataframe, and then input this into backtrader using pandas feeds.

import sqlite3

pdict = {}
#
# Create database engine
engine = sqlite3.connect("filepath", detect_types=sqlite3.PARSE_DECLTYPES)
table = 'tablename'
sql_start = pdict["from_date"]
sql_end = pdict["to_date"]

sql = (
        "SELECT * FROM "
        + table
        + " WHERE datetime BETWEEN '"
        + sql_start
        + "' AND '"
        + sql_end
        + "'"
)
df_ohlcv = pd.read_sql(sql, con=engine)
df_ohlcv = df_ohlcv.set_index("datetime", drop=True)
df_ohlcv.index = pd.to_datetime(df_ohlcv.index)

From here you can input into backtrader as a pandas dataframe.

If you have multiple tickers, then you just grab the tickers from the dataframe

tickers_list = df['tickers'].unique()

Then cycle through the tickers to filter your dataframe and add in the datas one at a time.

lampalork

@run-out thanks a lot. I m building a dedicated feed using sqlalchemy (i.e. not using pandas). I ll publish as soon as I get a stable version

lampalork

Hi All,
Long overdue. In case it can be useful to her folks.

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num
from sqlalchemy import create_engine

class SQLiteData(DataBase):
    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900,1,1)),
        ('todate', dt.datetime.max),
        )

    def __init__(self):
        try:
            self.engine = create_engine('sqlite:///{0}'.format(self.p.database), echo=False)
            self._timeframe = self.p.timeframe
            self._compression = self.p.compression
            self._dataname = '{0}'.format(self.p.symbol)
        except Exception as ex:
            print('Unable to open database {0} - {1}'.format(self.p.database, ex))

    def start(self):
        try:
            self.conn = self.engine.connect()
            sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '"+ self.p.fromdate.strftime("%Y-%m-%d %H:%M:%S") +"' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"
            #print(sql_query)
            self.result = self.conn.execute(sql_query)
        except Exception as ex:
            print('Unable to read table historical_data from database {0} - {1}'.format(self.p.database, ex))
            return

    def stop(self):
        self.engine.dispose()

    def _load(self):
        one_row = self.result.fetchone()
        if one_row is None:
            return False
        self.lines.datetime[0] = date2num(dt.datetime.strptime(str(one_row[0]),'%Y-%m-%d %H:%M:%S')) # date parsing
        self.lines.open[0] = float(one_row[1])
        self.lines.high[0] = float(one_row[2])
        self.lines.low[0] = float(one_row[3])
        self.lines.close[0] = float(one_row[4])
        self.lines.volume[0] = int(one_row[5])
        self.lines.openinterest[0] = -1
        return True

vladisld

Thanks for sharing.

As a side note:

suggest to take a look at the discussion in the PR I was posting above (https://github.com/mementum/backtrader/pull/393).

There are several issues in this PR that were reviewed/fixed that your code may benefit from as well.

lampalork

Thanks a lot. My previous stuff was working but it was breaking while running optimization with multiple cpu because of the way it was designed. This psql example has shown me what to do.

Here is the new version for the benefits of others. Happy Friday!

Lamp'

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num

class SQLiteData(DataBase):
   
    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900, 1, 1)),
        ('todate', dt.datetime.max),

        # parameterized column indices for ease of overwriting/re-implementing
        ('datetime', 0),
        ('open', 1),
        ('high', 2),
        ('low', 3),
        ('close', 4),
        ('volume', 5),
        ('openinterest', -1),
    )

    def __init__(self):
        self._timeframe = self.p.timeframe
        self._compression = self.p.compression
        self._dataname = '{0}-{1:.2f}'.format(self.p.symbol, self.p.tick_value)

    def start(self):
        engine = self._connect_db()

        sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '" + self.p.fromdate.strftime(
            "%Y-%m-%d %H:%M:%S") + "' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"

        result = engine.execute(sql_query)
        self.price_rows = result.fetchall()
        result.close()

        self.price_i = 0
        super(SQLiteData, self).start()

    def _load(self):
        if self.price_i >= len(self.price_rows):
            return False
        row = self.price_rows[self.price_i]
        self.price_i += 1

        for datafield in self.getlinealiases():
            if datafield == 'datetime':
                self.lines.datetime[0] = date2num(dt.datetime.strptime(row[self.p.datetime],'%Y-%m-%d %H:%M:%S'))
            elif datafield == 'volume':
                self.lines.volume[0] = row[self.p.volume]

            else:
                # get the column index
                colidx = getattr(self.params, datafield)
                if colidx < 0:
                    # column not present -- skip
                    continue
                # get the line to be set
                line = getattr(self.lines, datafield)
                line[0] = float(row[colidx])

        return True


    def _connect_db(self):
        from sqlalchemy import create_engine
        url = 'sqlite:///{0}'.format(self.p.database)
        engine = create_engine(url, echo=False)
        return engine


    def preload(self):
        super(SQLiteData, self).preload()
        # preloaded - no need to keep the price_rows as data is stored in the cerebro's Lines
        self.price_rows = None

lampalork

Latest version that resolves the memory consumption error described here:
https://community.backtrader.com/topic/2397/high-memory-consumption-while-optimizing-using-influxdb-data-feed

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num


class SQLiteData(DataBase):
    '''
    Fetches data from SQLite, and wraps it into a Feed consumable by cerebro
    takes url connection string in form of :
        sqlite://{database}

    this implementation assumes a single table (historical_data) with all prices,
    conforming to a schema similar to the following:

            symbol        TEXT,
            date          TEXT (YYYY-mm-dd HH:mm),
            open          REAL,
            high          REAL,
            low           REAL,
            close         REAL,
            volume        INTEGER,
            unique (symbol, date)

    if your databases are set up differently, you can override the
    start() method.
    '''

    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900, 1, 1)),
        ('todate', dt.datetime.max),

        # parameterized column indices for ease of overwriting/re-implementing
        ('datetime', 0),
        ('open', 1),
        ('high', 2),
        ('low', 3),
        ('close', 4),
        ('volume', 5),
        ('openinterest', -1),
    )

    def __init__(self):
        self._timeframe = self.p.timeframe
        self._compression = self.p.compression
        self._dataname = '{0}-{1:.2f}'.format(self.p.symbol, self.p.tick_value)

    def start(self):
        super(SQLiteData, self).start()
        self.biter = None
        self.preloaded = False

    def _preload(self):
        engine = self._connect_db()

        sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '" + self.p.fromdate.strftime(
            "%Y-%m-%d %H:%M:%S") + "' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"

        result = engine.execute(sql_query)
        dbars = result.fetchall()
        result.close()

        self.biter = iter(dbars)

    def preload(self):
        if not self.biter:
            self._preload()

        while self.load():
            pass

        self._last()
        self.home()

        self.biter = None
        self.preloaded = True

    def _load(self):
        if self.preloaded:
            return False

        if not self.biter:
            self._preload()

        try:
            bar = next(self.biter)
        except StopIteration:
            return False

        for field in self.getlinealiases():
            if field == 'datetime':
                self.lines.datetime[0] = date2num(dt.datetime.strptime(bar[self.p.datetime], '%Y-%m-%d %H:%M:%S'))
            elif field == 'volume':
                self.lines.volume[0] = bar[self.p.volume]
            else:
                # get the column index
                colidx = getattr(self.params, field)
                if colidx < 0:
                # column not present -- skip
                    continue

                # get the line to be set
                line = getattr(self.lines, field)
                line[0] = float(bar[colidx])

        return True


    def _connect_db(self):
        from sqlalchemy import create_engine
        url = 'sqlite:///{0}'.format(self.p.database)
        engine = create_engine(url, echo=False)
        return engine