For code/output blocks: Use ``` (aka backtick or grave accent) in a single line before and after the block. See:

SQLite example

  • 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

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

  • 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.

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

    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.

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

  • 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

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

  • 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.

  • @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.

  • @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

  • 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):
                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):
                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"
                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))
        def stop(self):
        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
  [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

  • Thanks for sharing.

    As a side note:

    suggest to take a look at the discussion in the PR I was posting above (

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

Log in to reply