def getAccounts(cls): ''' Retrieve a list of all accounts that have trades. The entries in the table are actual account numbers ''' ModelBase.connect(new_session=True) q = ModelBase.session.query(Trade.account).distinct().all() print() return [x[0] for x in q]
def getDistinctStratsQuery(cls): ModelBase.connect(new_session=True) qq = ModelBase.session.query(TradeSum.strategy, func.count(TradeSum.strategy)).group_by( TradeSum.strategy).order_by( desc(func.count( TradeSum.strategy))) return qq
def getTags(cls, tsum_id): ''' :params tsum_id: int. numpy types may fail(e.g. numpy.int64 fails in filter_by) ''' ModelBase.connect(new_session=True) q = ModelBase.session.query(TradeSum).filter_by(id=tsum_id).first() return q.tags if q else []
def getTableNames(self): if not ModelBase.engine: ModelBase.connect(new_session=True) ModelBase.createAll() inspector = inspect(ModelBase.engine) tns = inspector.get_table_names() return tns
def isHoliday(self, d, new_session=False): d = pd.Timestamp(d) d = d.strftime("%Y%m%d") if new_session == True: ModelBase.connect(new_session) session = ModelBase.session q = session.query(Holidays).filter_by(day=d).one_or_none() return True if q else False
def setUp(self): '''Remove schema''' print(Inspire.__table__) ModelBase.connect(new_session=True, con_str=self.con_str) ModelBase.metadata.drop_all(bind=ModelBase.engine, tables=[Inspire.__table__]) ModelBase.session.commit() ModelBase.session.close()
def getStrategy(cls, name=None, sid=None): ModelBase.connect(new_session=True, db="structjourDb") session = ModelBase.session if name: q = session.query(Strategy).filter_by(name=name).one_or_none() return q return session.query(Strategy).filter_by(id=sid).one_or_none()
def getId(cls, name): ''' Get the id of the strategy named name or return None if not found ''' ModelBase.connect(new_session=True, db="structjourDb") session = ModelBase.session q = session.query(Strategy.id).filter_by(name=name).one_or_none() if not q: return None return q.id
def deleteById(cls, tsid): ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(TradeSum).filter_by(id=tsid).one_or_none() if not q: return 0 session.delete(q) session.close() return 1
def test_popHol(self): ModelBase.connect(new_session=True) statement = text('delete from holidays') ModelBase.engine.execute(statement) ibdb = StatementDB() for holiday in ibdb.holidays: for day in holiday[1:]: if day: self.assertTrue(ibdb.tcrud.isHoliday(day))
def exercisegetListsOfTradesForStrategies(): ModelBase.connect(new_session=True) q = ModelBase.session.query(TradeSum).filter( TradeSum.date > "20200101").filter(TradeSum.date < "20200301") trades = TradeSum.getListsOfTradesForStrategies( q, ['ORB', "VWAP MA trend", "Swing", "Momentum"]) print(len(trades)) for t in trades: print(t[0], len(t[1])) print()
def updateTSID(cls, tid, tsid): ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(Trade).filter_by(id=tid).one_or_none() if q: if not isNumeric(q.trade_sum_id) or tsid != q.trade_sum_id: q.trade_sum_id = tsid session.add(q) session.commit() session.close()
def addSource(cls, name, id=None): ModelBase.connect(new_session=True, db="structjourDb") session = ModelBase.session if id: source = Source(datasource=name, id=id) else: source = Source(datasource=name) session.add(source) session.commit() session.close()
def createTables(self): ModelBase.connect(new_session=True, db='structjourDb') ModelBase.createAll() try: Source.addSource('default', 1) Source.addSource('user', 2) Source.addSource('contrib', 3) logging.info('Creating standard Strategy Source entries') except Exception: logging.info('Standard Strategy Source entries have already been created')
def updateMstkVals(cls, tsid, val, note): if not tsid: return ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(TradeSum).filter_by(id=tsid).one_or_none() if q: q.mstkval = val q.mstknote = note session.add(q) session.commit() session.close()
def insertHoliday(cls, day, name, commit=False): if commit: ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(Holidays).filter_by(day=day).filter_by(name=name).one_or_none() if not q: h = Holidays(day=day, name=name) session.add(h) if commit: session.commit() session.close()
def getImage(cls, strat, widget): ''' :return a list of [<Images>, <Strategy>] ''' # Implicit join retrives a list[image, strategy] ModelBase.connect(new_session=True, db="structjourDb") session = ModelBase.session q = session.query(Images, Strategy).filter( and_(Strategy.id == Images.strategy_id, Strategy.name == strat, Images.widget == widget)).one_or_none() return q
def clearTables(self): statements = ['''delete from chart''', '''delete from holidays''', '''delete from ib_covered''', '''delete from ib_trades''', '''delete from ib_positions''', '''delete from trade_sum''' ] ModelBase.connect(new_session=True) for statement in statements: s = text(statement) result = ModelBase.engine.execute(s)
def addStrategy(cls, name, preferred=True): if Strategy.getStrategy(name): logging.info(f'Strategy {name} already exists. No strategy added') return ModelBase.connect(new_session=True, db="structjourDb") session = ModelBase.session strat = Strategy(name=name, preferred=preferred) session.add(strat) session.commit() session.close() return strat
def isDateCovered(cls, account, d, new_session=False): ''' Use an ongoing session ''' d = pd.Timestamp(d) d = d.strftime("%Y%m%d") if new_session == True: ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(Covered).filter_by(day=d).filter_by(account=account).one_or_none() return True if q else False
def appendTags(): '''local proof of concept stuff''' ModelBase.connect(new_session=True) trades = ModelBase.session.query(TradeSum).filter( TradeSum.date > "20200131").all() tags = ModelBase.session.query(Tags).all() print(len(trades), len(tags)) for i, trade in enumerate(trades): # print(f'({trade.id}, {tags[i%11].id}), ', end='') TradeSum.append_tag(trade_sum_id=trade.id, tag_id=tags[i % 11].id) TradeSum.append_tag(trade_sum_id=trade.id, tag_id=tags[(i + 7) % 11].id)
def insertCovered(cls, account, d, covered_b='true', new_session=False): if new_session == True: ModelBase.connect(new_session=True) session = ModelBase.session d = pd.Timestamp(d) d = d.strftime("%Y%m%d") if not Covered.isDateCovered(account, d): cov = Covered(day=d, account=account, covered=covered_b) session.add(cov) if new_session: session.commit() session.close()
def addTradesToSum(cls, tsid, tids): ModelBase.connect(new_session=True) session = ModelBase.session if not isinstance(tids, list): tids = [tids] for t in tids: q = session.query(Trade).filter_by(id=t).one_or_none() if q: q.trade_sum_id = tsid session.commit() session.close()
def createTables(self): ''' Creates the api_keys if it doesnt exist then adds a row for each api that requires a key if they dont exist ''' ModelBase.connect(new_session=True) ModelBase.createAll() curapis = ['fh', 'av', 'bc', 'tgo'] # When this info changes, Use the apisettings control to abstract the data addit = False for api in curapis: key = ApiKey.getKey(api, keyonly=False) if not key: ApiKey.addKey(api)
def setActive(cls, active, tag_id=None, tag_name=None): ''' :params active: Boolean :params tag_id: Either id or name must be given :params tag_name: ''' assert tag_id is not None or tag_name is not None ModelBase.connect(new_session=True) if tag_id: q = ModelBase.session.query(Tags).filter_by(id=tag_id).one() else: q = ModelBase.session.query(Tags).filter_by(name=tag_name).one() q.active = active ModelBase.session.commit()
def test_connect(self): '''Create schema, add, delete. Test the add and the delete''' insp = Inspire(lname='The Dude', subject='On Peace', name='The Dude', who='THE authority on life', quote='This aggression will not stand, man.') ModelBase.connect(new_session=True, con_str=self.con_str) ModelBase.createAll() session = ModelBase.session session.add(insp) session.commit() session.close() ModelBase.connect(new_session=True, con_str=self.con_str) session = ModelBase.session q = session.query(Inspire).all() self.assertEqual(q[0].quote, 'This aggression will not stand, man.') self.assertEqual(len(q), 1) session.delete(q[0]) session.commit() session.close() ModelBase.connect(new_session=True, con_str=self.con_str) session = ModelBase.session q = session.query(Inspire).all() self.assertEqual(len(q), 0) session.close()
def updateAvgBalPlOC(cls, atrade, avg, bal, pl, oc, new_session=True): if new_session: ModelBase.connect(new_session=True) session = ModelBase.session if not isinstance(atrade, Trade): logging.error('atrade record ust be a Trade instance.') return atrade.average = avg atrade.balance = bal atrade.pnl = pl atrade.oc = oc session.add(atrade) if new_session: session.commit() session.close()
class Migration: min_version = '0.9.92a002' version = version dependencies = [ TradeSum, ] # Better, more central location to call createAll? Have to accomodate migrations that use Sessions # and migrations that use engine.connect operations = [ ModelBase.connect(new_session=True), ModelBase.createAll(), ModelBase.checkVersion(min_version, version), Migrate.doUpdate() ]
def addKey(self, api, key=''): ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(ApiKey).filter_by(api=api).one_or_none() if q: q.key = key session.add(q) session.commit() session.close() return newkey = ApiKey(api=api, key=key) session.add(newkey) session.commit() session.close()
def getNote(cls, date): ''' Retrieve the object for the record associated with date. :return: None if the record does not exist. ''' if not date: logging.info('A Date must be provided to DailyNotes.getNote') return None date = pd.Timestamp(date) date = int(date.strftime("%Y%m%d")) ModelBase.connect(new_session=True) session = ModelBase.session q = session.query(DailyNotes).filter_by(date=int(date)).one_or_none() return q