class Allocation(db.BaseModel):
__tablename__ = 'allocation'
id = db.Column('allocation_id', db.Integer, primary_key=True)
name = db.Column('name', db.String)
target = db.Column('target', db.Float, nullable=False, default=0.0)
assets = db.relationship('Asset',
secondary='assetAllocationRelationship',
lazy='joined')
class Exchange(db.Base):
""" Class of an exchange data object
"""
__tablename__ = 'exchange'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String)
currency = db.Column(db.String)
created_date = db.Column(db.DateTime)
last_updated = db.Column(db.DateTime)
class Asset(db.BaseModel):
__tablename__ = 'assets'
id = db.Column('ticker', db.String, primary_key=True)
name = db.Column('name', db.String)
shares = db.Column('shares', db.Float, default=0, nullable=False)
is_active = db.Column('is_active', db.Boolean, default=True)
allocation = db.relationship('Allocation',
secondary='assetAllocationRelationship',
uselist=False)
class AssetAllocationRelationship(db.BaseModel):
__tablename__ = 'assetAllocationRelationship'
id = db.Column('relationship_id', db.Integer, primary_key=True)
asset_id = db.Column('asset_id',
db.String,
db.ForeignKey('assets.ticker'),
nullable=False)
allocation_id = db.Column('allocation_id',
db.Integer,
db.ForeignKey('allocation.allocation_id'),
nullable=False)
class DataVendor(db.Base):
""" Class of an datavendor object (e.g. Yahoo)
"""
__tablename__ = 'datavendor'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String)
website = db.Column(db.String)
created_date = db.Column(db.DateTime)
last_updated = db.Column(db.DateTime)
def __repr__(self):
return (self.name + " " + self.website + " (" + str(self.last_updated) + ")")
async def repopulate(self):
user = await self._db.table(
"User", db.Column("name", db.tp.STR, db.tp.NULL),
db.Column("password", db.tp.STR, db.tp.NULL))
game = await self._db.table("Game",
db.Column("name", db.tp.STR, db.tp.NULL),
db.Column("folder", db.tp.STR, db.tp.NULL))
ai = await self._db.table("AI", db.Column.Foreign("gid", game),
db.Column("name", db.tp.STR, db.tp.NULL),
db.Column("filename", db.tp.STR, db.tp.NULL))
score = await self._db.table("Score", db.Column.Foreign("uid", user),
db.Column.Foreign("aid", ai),
db.Column("value", db.tp.INT, db.tp.NULL))
# Default Users
await self.register("annon", "annon_user_password")
class Security(db.Base):
""" Class of a security data object (i.e. a general financial object
such as an index or stock)
"""
__tablename__ = 'security'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String)
type = db.Column(db.String)
symbol = db.Column(db.String)
created_date = db.Column(db.DateTime)
last_updated = db.Column(db.DateTime)
def __repr__(self):
return (str(self.name) + ": " + str(self.symbol) + " (" + str(self.type) + ")")
class Quotation(db.Base):
""" Class of a quotation object (security [e.g. stock or index] information on a given date)
"""
__tablename__ = 'quotations'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
date = db.Column(db.DateTime)
open = db.Column(db.Numeric(10, 2))
high = db.Column(db.Numeric(10, 2))
low = db.Column(db.Numeric(10, 2))
close = db.Column(db.Numeric(10, 2))
adj_close = db.Column(db.Numeric(10, 2))
volume = db.Column(db.Numeric(10, 2))
created_date = db.Column(db.DateTime)
last_updated = db.Column(db.DateTime)
data_vendor_id = db.Column(db.Integer, db.ForeignKey('datavendor.id'))
data_vendor = db.relationship('DataVendor', backref='datavendor')
security_id = db.Column(db.Integer, db.ForeignKey('security.id'))
security = db.relationship('Security', backref='security')
@classmethod
def get_dataframe(cls, symbol):
""" Get Pandas Dataframe of quotations for given symbol
Args:
symbol (string): Symbol of security (e.g. "^DJI")
Returns:
Panadas dataframe with quotation data
None in case of error
"""
return_df = None
# Get security object for given symbol
security = db_session.query(Security).filter_by(symbol=symbol).first()
if security is not None:
# Get quotation data for symbol as dataframe
t = sa.Table(cls.__tablename__, sa.MetaData(), autoload_with=db_engine)
return_df = pd.read_sql(t.select().where(t.c.security_id == security.id), db_engine)
return return_df
def __repr__(self):
return (str(self.date) + ": " + str(self.adj_close) + "(" + str(self.volume) + ")" + " Created: " + str(self.created_date))
