Thank you so *much* for the quick and detailed reply Michael, worked like a
charm!
Haha, yes, I'm not really using Numpy (just a standard import, hehe).
On a sidenote, SQLAlchemy is one of the most complex and impressive
libraries I've ever seen, and the documentation is beyond words - so thank
you again!
Best, Victor
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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> 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.
