Think I solved it, thanks anyway!
Den tisdag 28 juli 2015 kl. 10:59:02 UTC+2 skrev Victor Tingström:
>
> A follow-up question: If I remove the platform from fund, how do I update
> the entries in ts_types?
>
> Den måndag 27 juli 2015 kl. 17:26:40 UTC+2 skrev Michael Bayer:
>>
>>
>>
>> On 7/27/15 10:56 AM, Victor Tingström wrote:
>>
>> 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)
>>
>>
>> OK, it's (mostly) runnable code, so I can just cut and paste (and cut out
>> numpy, ecch !) and run, great.
>>
>> First thing, localize to where it is actually breaking, use constraints:
>>
>> class TimeseriesTypeORM(Base):
>> __tablename__ = 'timeseriestypes'
>>
>> platform_id = Column(Integer, ForeignKey('platforms.platform_id'),
>> nullable=False)
>> # ...
>>
>> now we see the problem:
>>
>> <stacktrace>
>> sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL
>> constraint failed: timeseriestypes.platform_id [SQL: u'INSERT INTO
>> timeseriestypes (platform_id, fund_id, ts_type_name) VALUES (?, ?, ?)']
>> [parameters: (None, 1, 'spot')]
>>
>>
>> so why is that. I'm looking around, and I don't see any relationships to
>> TimeseriesTypeORM linked to Platform, so that would be why.
>>
>> Platform is only linked to FundORM, and that goes to TimeseriesTypeORM.
>> So you either need to add another relationship, or use flush events to
>> double up the timeseriestypes.platform_id value. The latter might be
>> tricky because it might not fire off at the right time, so lets use
>> relationship which makes sure everything is done in the right order.
>> Let's use an append event to make the association automatic also:
>>
>> 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)
>>
>>
>> @event.listens_for(FundORM.ts_types, "append")
>> def append_ts_type(target, value, initiator):
>> if target.platform is None:
>> raise ValueError("FundORM has no PlatformORM assigned!")
>> value.platform = target.platform
>>
>>
>> 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')
>>
>> platform = relationship("PlatformORM")
>>
>> 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)
>>
>>
>> now we run the same code and we get:
>>
>> SELECT timeseriestypes.platform_id AS timeseriestypes_platform_id,
>> timeseriestypes.fund_id AS timeseriestypes_fund_id,
>> timeseriestypes.ts_type_id AS timeseriestypes_ts_type_id,
>> timeseriestypes.ts_type_name AS timeseriestypes_ts_type_name
>> FROM timeseriestypes
>> WHERE timeseriestypes.ts_type_id = ?
>> 2015-07-27 11:22:35,956 INFO sqlalchemy.engine.base.Engine (1,)
>> <Type('spot'), Fund ID('1'), Platform ID('1')>
>>
>>
>>
>> see attached.
>>
>>
>>
>>
>>
>>
>>
>>
>> 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.
>>
>>
>>
--
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.
