MySQL DataFeed

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

spyamine

thx for sharing

Shiva

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

Duy Vinh Đỗ

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

Aditya Khurana

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

sidianhao

非常感谢!
Thanks a lot

Shiva

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

Princeton Wong

How to use this with Cerebro?

LeggoMaEggo

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