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

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
    Lamp'



  • 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.
    Regards
    Lamp'



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



  • 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):
            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
    
    


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



  • 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
    
    

Log in to reply
 

});