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