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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[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.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
Base = declarative_base()
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
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)
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)
e = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(e)
session = Session(e)
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()
print ts_type
