Hey guys! First off, thank you for the library. Now to the problem, I'm
currently trying to implement a simple timeseries database using
`SQLAlchemy`. And so far I've seen the need for the following classes;
First off is the `Platform` class (which is the highest in the hierarchy)
import numpy as np
import pandas as pd
import datetime
from sqlalchemy import create_engine, Column, Integer, String, Float,
ForeignKey, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relation, backref
engine = create_engine('sqlite:///:memory:', echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()
class PlatformORM(Base):
__tablename__ = 'platforms'
platform_id = Column(Integer, primary_key=True)
platform_name = Column(String, unique=True)
subfunds = relationship('FundORM', backref='platform')
def __init__(self, platform_name):
self.platform_name = platform_name
def __repr__(self):
return "<Platform(name = '%s')>" % self.platform_name
Next, comes the `FundORM` class, which is linked to the `PlatformORM`
class. Such that an instance of `PlatformORM` can have several subfunds.
class FundORM(Base):
__tablename__ = 'funds'
fund_id = Column(Integer, primary_key=True)
platform_id = Column(Integer, ForeignKey('platforms.platform_id'))
fund_name = Column(String, unique=True)
ts_types = relationship('TimeseriesTypeORM', backref='fund')
def __init__(self, fund_name):
self.fund_name = fund_name
def __repr__(self):
return "<Fund name('%s'), Platform ID('%s')>" %
(self.fund_name, self.platform_id)
Using only these two classes it's easy to link them. However, I now wish to
add another class called `TimeseriesTypeORM`, which is linked to `FundORM`
and describes the different types of timeseries that `FundORM` has - it
could for example be returns or spot price. This is defined such as
class TimeseriesTypeORM(Base):
__tablename__ = 'timeseriestypes'
platform_id = Column(Integer, ForeignKey('platforms.platform_id'))
fund_id = Column(Integer, ForeignKey('funds.fund_id'))
ts_type_id = Column(Integer, primary_key=True)
ts_type_name = Column(String)
timeseries = relationship('TimeseriesORM', backref='ts_type')
def __init__(self, ts_type_name=None):
self.ts_type_name = ts_type_name
def __repr__(self):
return "<Type('%s'), Fund ID('%s'), Platform ID('%s')>" %
(self.ts_type_name, self.fund_id, self.platform_id)
Furthermore, I'd like to create another class called `TimeseriesORM`, which
is linked to `TimeseriesTypeORM`, containing the actual data of the
timeseries. The class is defined as
class TimeseriesORM(Base):
__tablename__ = 'timeseries'
platform_id = Column(Integer, ForeignKey('platforms.platform_id'))
fund_id = Column(Integer, ForeignKey('funds.fund_id'))
ts_type_id = Column(Integer,
ForeignKey('timeseriestypes.ts_type_id'))
ts_id = Column(Integer, primary_key=True)
date = Column(TIMESTAMP)
value = Column(Float)
def __init__(self, date=None, value=None):
self.date = date
self.value = value
def __repr__(self):
return "<Date('%s'), Fund ID('%s')>" % (self.date, self.fund_id)
As an example, assume that we have a platform called `ABC`, and that `ABC`
has a fund called `DBE`, where `DBE` in turn has data for spot prices which
is to be added. Creating the platform, fund and timeseries type then yields
platform = PlatformORM('ABC')
fund = FundORM('DBE')
platform.subfunds += [fund]
ts_type = TimeseriesTypeORM('spot')
fund.ts_types += [ts_type]
session.add_all([platform, fund, ts_type])
session.commit()
I can run the above code just fine. However, if I try to view `ts_type`,
It'll show
<Type('spot'), Fund ID('1'), Platform ID('None')>,
where `Platform ID` should be 1. However, printing `ts_type.fund` will show
me the correct platform ID. I really can't figure out where I'm going wrong
- but a guess is somewhere in the relation for `FundORM`. Do you guys have
any ideas? It's kinda important to have the `platform_id` and `fund_id` as
keys in `TimeseriesORM`. Thanks! // Victor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
