Navigation

    Backtrader Community

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    For code/output blocks: Use ``` (aka backtick or grave accent) in a single line before and after the block. See: http://commonmark.org/help/

    MySQL DataFeed

    General Discussion
    8
    9
    2456
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • mrahmadt
      mrahmadt last edited by mrahmadt

      Hello

      I created a DataFeed to read the data from MySQL database, just want to share it with you in case someone want to use it

      
      from __future__ import (absolute_import, division, print_function,
                              unicode_literals)
      
      import datetime
      from backtrader.feed import DataBase
      from backtrader import date2num
      from sqlalchemy import create_engine
      
      
      class MySQLData(DataBase):
          params = (
              ('dbHost', None),
              ('dbUser', None),
              ('dbPWD', None),
              ('dbName', None),
              ('ticker', 'ISL'),
              ('fromdate', datetime.datetime.min),
              ('todate', datetime.datetime.max),
              ('name', ''),
              )
      
          def __init__(self):
              self.engine = create_engine('mysql://'+self.p.dbUser+':'+ self.p.dbPWD +'@'+ self.p.dbHost +'/'+ self.p.dbName +'?charset=utf8mb4', echo=False)
      
          def start(self):
              self.conn = self.engine.connect()
              self.stockdata = self.conn.execute("SELECT id FROM stocks WHERE ticker LIKE '" + self.p.ticker + "' LIMIT 1")
              self.stock_id = self.stockdata.fetchone()[0]
              #self.result = self.engine.execute("SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `eoddata` WHERE `stock_id` = 10 AND `date` between '"+self.p.fromdate.strftime("%Y-%m-%d")+"' and '"+self.p.todate.strftime("%Y-%m-%d")+"' ORDER BY `date` ASC")
              self.result = self.conn.execute("SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `eoddata` WHERE `stock_id` = " + str(self.stock_id) + " AND `date` between '"+self.p.fromdate.strftime("%Y-%m-%d")+"' and '"+self.p.todate.strftime("%Y-%m-%d")+"' ORDER BY `date` ASC")
      
          def stop(self):
              #self.conn.close()
              self.engine.dispose()
      
          def _load(self):
              one_row = self.result.fetchone()
              if one_row is None:
                  return False
              self.lines.datetime[0] = date2num(one_row[0])
              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
      
      Duy Vinh Đỗ LeggoMaEggo 2 Replies Last reply Reply Quote 7
      • spyamine
        spyamine last edited by

        thx for sharing

        1 Reply Last reply Reply Quote 0
        • S
          Shiva last edited by

          @mrahmadt
          Is there a way to indicate in _load function that this feed is live feed and to modify load function to return None.
          If _load returns None, then it would indicate cerebro that wait, feed is on the way to be received from database. Otherwise Cerebro will exist.
          Your above script helped me a lot, thanks.

          1 Reply Last reply Reply Quote 0
          • Duy Vinh Đỗ
            Duy Vinh Đỗ @mrahmadt last edited by

            @mrahmadt you are the real hero, love you <3

            1 Reply Last reply Reply Quote 0
            • Aditya Khurana
              Aditya Khurana last edited by

              Slightly off topic - What are the benefits of using MySQL over a CSV?

              S 1 Reply Last reply Reply Quote 0
              • sidianhao
                sidianhao last edited by

                非常感谢!
                Thanks a lot

                1 Reply Last reply Reply Quote 0
                • S
                  Shiva @Aditya Khurana last edited by

                  @Aditya-Khurana Database can be used for live trading where one feed is live and another feed is for historical data from database.

                  1 Reply Last reply Reply Quote 0
                  • Princeton Wong
                    Princeton Wong last edited by

                    How to use this with Cerebro?

                    1 Reply Last reply Reply Quote 0
                    • LeggoMaEggo
                      LeggoMaEggo @mrahmadt last edited by

                      @mrahmadt Running into an OperationalError: (psycopg2.OperationalError) FATAL: sorry, too many clients already for a list of 500 stocks. I have tried max_overflow=-1 and pool_size=0 to have unlimited connections. Still doesn't work. Anyone have any suggestions?

                      1 Reply Last reply Reply Quote 0
                      • 1 / 1
                      • First post
                        Last post
                      Copyright © 2016, 2017, 2018, 2019, 2020, 2021 NodeBB Forums | Contributors