def tester():
class FooObj(DataObject):
partition_attribute = 'id'
@schema([IntegerColumn(name='id')])
class Foo(FooObj):
identity_key_ = (('id', 'id'),)
sort_key_ = ('id',)
# Create a test database and table
engine = create_engine('sqlite://')
metadata = MetaData(bind=engine)
foos_table = Foo.to_sqa_table(metadata, 'foos')
metadata.create_all()
# Define the mapping between tables and objects for writing
writer_config = {Foo: SqaWriterConfig(foos_table),
}
# Define the mapping between tables and objects for reading
reader_config = {Foo: SqaReaderConfig(foos_table, engine_wrapper(engine), **reader_args)}
# Create some objects
foos = [Foo(id=i) for i in range(1000)]
# Write the objects to the database
writer = Foo.writer(writer_config)
for foo in foos:
writer.write(foo)
def do_the_reading():
return [foo for foo in Foo.reader(reader_config)]
assertion(do_the_reading)
def dump_ddl(metadata: MetaData,
dialect_name: str,
fileobj: TextIO = sys.stdout,
checkfirst: bool = True) -> None:
"""
Sends schema-creating DDL from the metadata to the dump engine.
This makes ``CREATE TABLE`` statements.
Args:
metadata: SQLAlchemy :class:`MetaData`
dialect_name: string name of SQL dialect to generate DDL in
fileobj: file-like object to send DDL to
checkfirst: if ``True``, use ``CREATE TABLE IF NOT EXISTS`` or
equivalent.
"""
# http://docs.sqlalchemy.org/en/rel_0_8/faq.html#how-can-i-get-the-create-table-drop-table-output-as-a-string # noqa
# http://stackoverflow.com/questions/870925/how-to-generate-a-file-with-ddl-in-the-engines-sql-dialect-in-sqlalchemy # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
# noinspection PyUnusedLocal
def dump(querysql, *multiparams, **params):
compsql = querysql.compile(dialect=engine.dialect)
writeline_nl(fileobj, f"{compsql};")
writeline_nl(fileobj, sql_comment(f"Schema (for dialect {dialect_name}):"))
engine = create_engine(f'{dialect_name}://',
strategy='mock',
executor=dump)
metadata.create_all(engine, checkfirst=checkfirst)
def create_single_table(self, table_name, engine):
meta_data = MetaData(engine)
stock = Table(
table_name,
meta_data,
Column('id', Integer, primary_key=True),
Column('PB', Numeric(8, 4)),
Column('PE', Numeric(8, 4)),
Column('PE1', Numeric(8, 4)),
Column('accumAdjFactor', Integer),
Column('actPreClosePrice', Numeric(8, 4)),
Column('chgPct', Numeric(8, 4)),
Column('closePrice', Numeric(8, 4)),
Column('dealAmount', BigInteger),
Column('highestPrice', Numeric(8, 4)),
Column('isOpen', Integer),
Column('lowestPrice', Numeric(8, 4)),
Column('marketValue', BigInteger),
Column('negMarketValue', BigInteger),
Column('openPrice', Numeric(8, 4)),
Column('preClosePrice', Numeric(8, 4)),
Column('turnoverRate', Numeric(8, 4)),
Column('turnoverValue', BigInteger),
Column('turnoverVol', BigInteger),
Column('vwap', Numeric(8, 4)),
Column('date', DateTime),
)
meta_data.create_all(engine)
def nontest_update_from_main_model_instance():
app = create_test_app()
update_aux_db_engine_discovery_map(app,
force_update=True,
aux_db_engine_name_prefix='test_aux_')
engine = get_aux_helpers_engine(app)
metadata = MetaData()
metadata.create_all(engine)
session = get_aux_helpers_session(app)
create_tables([TxHelperModel], engine, recreate_if_exists=True)
session.query(TxHelperModel).all()
sm = AuxSessionManager(app=app)
seq_num = 1
bm_session = sm.get(seq_num)
bm_engine = sm.get_engine(seq_num)
create_tables([TxModel], bm_engine, recreate_if_exists=True)
len_bm_first = len(TxModel.query.with_session(bm_session).all())
assert len_bm_first == 0
block = Block(b64decode_hashes=True, from_detailed_dict=d3[0])
BlockModel.update_from_block(block, session=bm_session)
assert len(
BlockModel.query.with_session(bm_session).all()) == len_bm_first + 1
bm_instance = BlockModel.query.with_session(bm_session).all()[0]
len_first = len(BlockHelperModel.query.with_session(session).all())
assert len_first == 0
BlockHelperModel.update_from_block_model_instance(
bm_instance,
session=session,
block_model_session=bm_session,
seq_num=seq_num)
num_of_bm_fields = len(BlockModel.__table__.columns)
assert len(
BlockHelperModel.query.with_session(session).filter_by(
seq_num=seq_num).all()) == len_first + num_of_bm_fields - 1
BlockHelperModel.query.with_session(session).filter_by(
seq_num=seq_num, block_id=bm_instance.id).delete()
BlockHelperModel.update_from_block_model_instance(
bm_instance,
session=session,
block_model_session=bm_session,
seq_num=seq_num)
assert len(
BlockHelperModel.query.with_session(session).filter_by(
seq_num=seq_num).all()) == len_first + num_of_bm_fields - 1
def connection(sync_engine):
with sync_engine.connect() as conn:
metadata = MetaData()
Table("table", metadata, Column("column1", Integer, primary_key=True))
Table("table2", metadata, Column("fk_column", ForeignKey("table.column1")))
if conn.dialect.name != "sqlite":
conn.execute(CreateSchema("altschema"))
Table("table3", metadata, Column("fk_column", Integer), schema="altschema")
metadata.create_all(conn)
yield conn
if conn.dialect.name != "sqlite":
metadata.drop_all(conn)
conn.execute(DropSchema("altschema"))
def test_vector_greater_than():
metadata = MetaData()
table = Table('people', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String),
Column('middle_name', String),
Column('last_name', String),
Column('blood_type', String))
def random_person(idx):
first = names.get_first_name()
last = names.get_last_name()
middle = random.choice([names.get_first_name, names.get_last_name, lambda: None])()
blood_type = random.choice(['A', 'A', 'B', 'B', 'O', 'O', 'O', 'O', 'AB'])
return {'id': idx,
'first_name': first,
'middle_name': middle,
'last_name': last,
'blood_type': blood_type
}
engine = create_engine('sqlite:///:memory:', echo=False)
metadata.create_all(engine)
def compare_results(compa, cols, vals):
vals = tuple(map(none_to_minus_inf, vals))
res = set([row['id'] for row in engine.execute(select(table.columns).where(compa))])
all_ = [valmap(none_to_minus_inf, row) for row in engine.execute(select(table.columns))]
cor = set()
for row in all_:
if tuple(row[col.name] for col in cols) > vals:
cor.add(row['id'])
assert_equal(res, cor)
for i in range(1000):
engine.execute(table.insert(random_person(i)))
col_tuples = [(table.columns['id'],),
(table.columns['blood_type'], table.columns['id']),
(table.columns['blood_type'], table.columns['middle_name'], table.columns['id']),
(table.columns['blood_type'], table.columns['id'], table.columns['middle_name']),
(table.columns['middle_name'], table.columns['blood_type'], table.columns['id']),]
val_tuples = [(5,),
('AB', 500),
('B', None, 500),
('B', 500, None),
(None, 'B', 500)]
for cols, vals in zip(col_tuples, val_tuples):
compare_results(vector_greater_than(cols, vals), cols, vals)
def test_connect_test_database(database_url):
metadata1 = MetaData()
Table('table', metadata1, Column('column1', Integer, primary_key=True))
Table('table2', metadata1, Column('fk_column', ForeignKey('table.column1', name='fk_1')))
with connect_test_database(database_url) as connection:
metadata1.create_all(connection)
metadata2 = MetaData()
Table('table', metadata2, Column('column2', Unicode(20)))
with connect_test_database(database_url) as connection:
metadata2.create_all(connection)
metadata3 = MetaData(bind=connection, reflect=True)
assert len(metadata3.tables) == 1
table = metadata3.tables['table']
assert len(table.columns) == 1
assert 'column2' in table.columns.keys()
def run(self):
# Source reflection
source_meta = MetaData()
source_meta.reflect(bind=self.source_engine)
source_tables = source_meta.tables
source_table_names = [k for k, v in source_tables.items()]
# Destination Binding
destination_meta = MetaData(bind=self.destination_engine)
for name, table in source_tables.items():
table.metadata = destination_meta
if name in self.settings.exclude_data.keys():
table.__mapper_args__ = {"exclude_properties": self.settings.exclude_data[name]}
# Drop table for testing purposes
# destination_meta.drop_all(self.destination_engine)
for table in source_table_names:
self.sessions.destination.execute("DROP TABLE {table};".format(table=table))
self.sessions.destination.commit()
print("DROPPED TABLE {table}".format(table=table))
# Begin migration
source_meta.create_all(self.destination_engine)
source_data = {table: self.sessions.source.query(source_tables[table]).all() for table in source_table_names}
for table in source_table_names:
print("Migrating:", table)
# if table in self.settings.exclude_data.keys():
# pprint(source_tables[table].__mapper_args__)
# exit(1)
for row in source_data[table]:
try:
self.sessions.destination.execute(source_tables[table].insert(row))
except StatementError:
print("Bad data in table: ", table, "row data:\n", row[0], "Error:", sys.exc_info()[0])
print("Data for:", table, "added to the queue..")
self.sessions.destination.commit()
print("Migration Complete!")
def run(self):
# Source reflection
source_meta = MetaData()
source_meta.reflect(bind=self.source_engine)
source_tables = source_meta.tables
source_table_names = [k for k, v in source_tables.items()]
# Destination Binding
destination_meta = MetaData(bind=self.destination_engine)
for name, table in source_tables.items():
table.metadata = destination_meta
if name in self.settings.exclude_data.keys():
table.__mapper_args__ = {'exclude_properties': self.settings.exclude_data[name]}
# Drop table for testing purposes
# destination_meta.drop_all(self.destination_engine)
for table in source_table_names:
self.sessions.destination.execute('DROP TABLE {table};'.format(table=table))
self.sessions.destination.commit()
print('DROPPED TABLE {table}'.format(table=table))
# Begin migration
source_meta.create_all(self.destination_engine)
source_data = {table: self.sessions.source.query(source_tables[table]).all() for table in source_table_names}
for table in source_table_names:
print("Migrating:", table)
# if table in self.settings.exclude_data.keys():
# pprint(source_tables[table].__mapper_args__)
# exit(1)
for row in source_data[table]:
try:
self.sessions.destination.execute(source_tables[table].insert(row))
except StatementError:
print('Bad data in table: ', table, 'row data:\n', row[0], 'Error:', sys.exc_info()[0])
print('Data for:', table, 'added to the queue..')
self.sessions.destination.commit()
print('Migration Complete!')
def tester():
class FooObj(DataObject):
partition_attribute = 'id'
@schema([IntegerColumn(name='id')])
class Foo(FooObj):
identity_key_ = (('id', 'id'), )
sort_key_ = ('id', )
# Create a test database and table
engine = create_engine('sqlite://')
metadata = MetaData(bind=engine)
foos_table = Foo.to_sqa_table(metadata, 'foos')
metadata.create_all()
# Define the mapping between tables and objects for writing
writer_config = {
Foo: SqaWriterConfig(foos_table),
}
# Define the mapping between tables and objects for reading
reader_config = {
Foo: SqaReaderConfig(foos_table, engine_wrapper(engine),
**reader_args)
}
# Create some objects
foos = [Foo(id=i) for i in range(1000)]
# Write the objects to the database
writer = Foo.writer(writer_config)
for foo in foos:
writer.write(foo)
def do_the_reading():
return [foo for foo in Foo.reader(reader_config)]
assertion(do_the_reading)
def engine(request, tmpdir_factory):
engine = None
if request.param == 'sqlite-file':
tmpdir = tmpdir_factory.mktemp('asphalt-sqlalchemy')
db_path = str(tmpdir.join('test.db'))
engine = create_engine('sqlite:///' + db_path)
elif request.param == 'sqlite-memory':
engine = create_engine('sqlite:///:memory:')
elif request.param == 'mysql':
url = request.getfixturevalue('mysql_url')
engine = create_engine(url)
elif request.param == 'postgresql':
url = request.getfixturevalue('postgresql_url')
engine = create_engine(url)
if engine.dialect.name != 'sqlite':
engine.execute(CreateSchema('altschema'))
if request.param != 'sqlite-memory':
metadata = MetaData()
Table('table', metadata, Column('column1', Integer, primary_key=True))
Table('table2', metadata,
Column('fk_column', ForeignKey('table.column1')))
if engine.dialect.name != 'sqlite':
Table('table3',
metadata,
Column('fk_column', Integer),
schema='altschema')
metadata.create_all(engine)
yield engine
if engine.dialect.name != 'sqlite':
metadata.drop_all(engine)
engine.execute(DropSchema('altschema'))
class MigrateManager(object):
def __init__(self, from_db_uri, to_db_uri, migrate_tables, **kwargs):
self.migrate_tables = migrate_tables
self.from_db = create_engine(from_db_uri)
self.from_metadata = MetaData(bind=self.from_db,
reflect=True,
schema=kwargs.get('from_schema', None))
self.from_session = sessionmaker(bind=self.from_db)()
self.to_db = create_engine(to_db_uri)
self.to_metadata = MetaData(bind=self.to_db,
schema=kwargs.get('to_schema', None))
self.to_session = sessionmaker(bind=self.to_db)()
def init_to_metadata(self):
tables = OrderedDict()
for table_name in self.migrate_tables:
table = self.from_metadata.tables[table_name]
tables[table_name] = table
self.to_metadata.tables = tables
def drop_table(self):
self.init_to_metadata()
self.to_metadata.drop_all(bind=self.to_db)
def create_table(self):
self.init_to_metadata()
print(self.to_metadata.create_all(bind=self.to_db))
def insert_data(self):
for table_name in self.migrate_tables:
table = self.to_metadata.tables[table_name]
results = self.from_session.query(table).all()
table.metadata = self.to_metadata
table.schema = self.to_metadata.schema
print(results)
for result in results:
print(self.to_db.execute(table.insert().values(result)))
class _Orm(object):
def __init__(self, engine, schema=None):
self._metadata = MetaData(schema=schema)
# TODO This is hackish. How to check if we already have defined the mapping?
clear_mappers()
self.mappers = []
_feedinfo_id_column = Column('feed_id', String, primary_key=True)
_agency_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_route_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_feedinfo_mapper = Table('feed_info', self._metadata,
_feedinfo_id_column,
Column('feed_publisher_name', String),
Column('feed_publisher_url', String),
Column('feed_contact_email', String), # Non-standard (yet) field
Column('feed_contact_url', String), # Non-standard (yet) field
Column('feed_lang', String),
Column('feed_start_date', Date),
Column('feed_end_date', Date),
Column('feed_version', String))
self.mappers.append(mapper(FeedInfo, _feedinfo_mapper, properties={
}))
_agency_id_column = Column('agency_id', String, primary_key=True)
_route_agency_id_column = Column('agency_id', String, nullable=False)
_agency_mapper = Table('agency', self._metadata,
_agency_feed_id_column,
_agency_id_column,
Column('agency_name', String, nullable=False),
Column('agency_url', String, nullable=False),
Column('agency_timezone', String, nullable=False),
Column('agency_lang', String),
Column('agency_phone', String),
Column('agency_fare_url', String),
Column('agency_email', String))
self.mappers.append(mapper(Agency, _agency_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('agencies', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_agency_feed_id_column))
}))
_zone_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_zone_id_column = Column('zone_id', String, primary_key=True)
_zone_mapper = Table('zones', self._metadata,
_zone_feed_id_column,
_zone_id_column)
self.mappers.append(mapper(Zone, _zone_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('zones', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_zone_feed_id_column))
}))
_stop_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_id_column = Column('stop_id', String, primary_key=True)
_stop_parent_id_column = Column('parent_station_id', String, nullable=True)
_stop_zone_id_column = Column('zone_id', String, nullable=True)
_stop_mapper = Table('stops', self._metadata,
_stop_feed_id_column,
_stop_id_column,
_stop_parent_id_column,
Column('location_type', Integer, nullable=False),
Column('stop_name', String, nullable=False),
Column('stop_lat', Float, nullable=False),
Column('stop_lon', Float, nullable=False),
Column('wheelchair_boarding', Integer, nullable=False),
Column('stop_code', String),
Column('stop_desc', String),
_stop_zone_id_column,
Column('stop_url', String),
Column('stop_timezone', String),
ForeignKeyConstraint(['feed_id', 'parent_station_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'zone_id'], ['zones.feed_id', 'zones.zone_id']),
# TODO Make those index parametrable
Index('idx_stops_lat', 'stop_lat'),
Index('idx_stops_lon', 'stop_lon'),
Index('idx_stops_code', 'feed_id', 'stop_code'),
Index('idx_stops_zone', 'feed_id', 'zone_id'),
Index('idx_stops_parent', 'feed_id', 'parent_station_id'))
self.mappers.append(mapper(Stop, _stop_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_stop_feed_id_column)),
'sub_stops' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_parent_id_column], uselist=True,
primaryjoin=(_stop_parent_id_column == foreign(_stop_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'parent_station' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_id_column],
primaryjoin=(_stop_id_column == foreign(_stop_parent_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'zone' : relationship(Zone, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_stop_zone_id_column)) & (_zone_feed_id_column == _stop_feed_id_column))
}))
_transfer_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_transfer_from_stop_id_column = Column('from_stop_id', String, primary_key=True)
_transfer_to_stop_id_column = Column('to_stop_id', String, primary_key=True)
_transfer_mapper = Table('transfers', self._metadata,
_transfer_feed_id_column,
_transfer_from_stop_id_column,
_transfer_to_stop_id_column,
Column('transfer_type', Integer, nullable=False),
Column('min_transfer_time', Integer),
ForeignKeyConstraint(['feed_id', 'from_stop_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'to_stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_transfer_from', 'feed_id', 'from_stop_id'),
Index('idx_transfer_to', 'feed_id', 'to_stop_id'))
self.mappers.append(mapper(Transfer, _transfer_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('transfers', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_transfer_feed_id_column)),
'from_stop' : relationship(Stop, backref=backref('from_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_from_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column)),
'to_stop' : relationship(Stop, backref=backref('to_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_to_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column))
}))
_route_id_column = Column('route_id', String, primary_key=True)
_route_mapper = Table('routes', self._metadata,
_route_feed_id_column,
_route_id_column,
_route_agency_id_column,
Column('route_short_name', String),
Column('route_long_name', String),
Column('route_desc', String),
Column('route_type', Integer, nullable=False),
Column('route_url', String),
Column('route_color', String),
Column('route_text_color', String),
ForeignKeyConstraint(['feed_id', 'agency_id'], ['agency.feed_id', 'agency.agency_id']),
Index('idx_routes_agency', 'feed_id', 'agency_id'),
Index('idx_routes_short_name', 'feed_id', 'route_short_name'),
Index('idx_routes_type', 'feed_id', 'route_type'))
self.mappers.append(mapper(Route, _route_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_route_feed_id_column)),
'agency' : relationship(Agency, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=(_agency_id_column == foreign(_route_agency_id_column)) & (_agency_feed_id_column == _route_feed_id_column))
}))
_calendar_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_calendar_id_column = Column('service_id', String, primary_key=True)
_calendar_mapper = Table('calendar', self._metadata,
_calendar_feed_id_column,
_calendar_id_column
)
self.mappers.append(mapper(Calendar, _calendar_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('calendars', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_calendar_feed_id_column))
}))
_calendar_date_mapper = Table('calendar_dates', self._metadata,
Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True),
Column('service_id', String, primary_key=True),
Column('date', Date, primary_key=True),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
# TOCHECK It seems a composite primary key on (a,b,c) does not need indexing on left elements,
# such as (a) and (a,b); but need on (a,c) for example.
Index('idx_calendar_dates_date', 'feed_id', 'date'))
self.mappers.append(mapper(CalendarDate, _calendar_date_mapper, properties={
'calendar' : relationship(Calendar, backref=backref('dates', cascade="all,delete-orphan"))
}))
_shape_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_mapper = Table('shapes', self._metadata,
_shape_feed_id_column,
_shape_id_column
)
self.mappers.append(mapper(Shape, _shape_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('shapes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_shape_feed_id_column))
}))
_shape_pt_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_pt_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_pt_seq_column = Column('shape_pt_sequence', Integer, primary_key=True)
_shape_pt_mapper = Table('shape_pts', self._metadata,
_shape_pt_feed_id_column,
_shape_pt_shape_id_column,
_shape_pt_seq_column,
Column('shape_dist_traveled', Float, nullable=False),
Column('shape_pt_lat', Float, nullable=False),
Column('shape_pt_lon', Float, nullable=False),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_shape_pt_shape', 'feed_id', 'shape_id'))
self.mappers.append(mapper(ShapePoint, _shape_pt_mapper, properties={
# Note: here we specify foreign() on shape_pt feed_id column as there is no ownership relation of feed to shape_pts
'shape' : relationship(Shape, backref=backref('points', order_by=_shape_pt_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_shape_pt_shape_id_column)) & (_shape_feed_id_column == foreign(_shape_pt_feed_id_column)))
}))
_trip_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_trip_id_column = Column('trip_id', String, primary_key=True)
_trip_route_id_column = Column('route_id', String, nullable=False)
_trip_calendar_id_column = Column('service_id', String, nullable=False)
_trip_shape_id_column = Column('shape_id', String, nullable=True)
_trip_mapper = Table('trips', self._metadata,
_trip_feed_id_column,
_trip_id_column,
_trip_route_id_column,
_trip_calendar_id_column,
_trip_shape_id_column,
Column('wheelchair_accessible', Integer, nullable=False),
Column('bikes_allowed', Integer, nullable=False),
Column('exact_times', Integer, nullable=False),
Column('frequency_generated', Boolean, nullable=False),
Column('trip_headsign', String),
Column('trip_short_name', String),
Column('direction_id', Integer),
Column('block_id', String),
ForeignKeyConstraint(['feed_id', 'route_id'], ['routes.feed_id', 'routes.route_id']),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_trips_route', 'feed_id', 'route_id'),
Index('idx_trips_service', 'feed_id', 'service_id'))
self.mappers.append(mapper(Trip, _trip_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_trip_feed_id_column)),
'route' : relationship(Route, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_trip_route_id_column)) & (_route_feed_id_column == _trip_feed_id_column)),
'calendar' : relationship(Calendar, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_calendar_id_column == foreign(_trip_calendar_id_column)) & (_calendar_feed_id_column == _trip_feed_id_column)),
'shape' : relationship(Shape, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_trip_shape_id_column)) & (_shape_feed_id_column == _trip_feed_id_column))
}))
_stop_times_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_times_trip_id_column = Column('trip_id', String, primary_key=True)
_stop_seq_column = Column('stop_sequence', Integer, primary_key=True)
_stop_times_stop_id_column = Column('stop_id', String, nullable=False)
_stop_times_mapper = Table('stop_times', self._metadata,
_stop_times_feed_id_column,
_stop_times_trip_id_column,
_stop_seq_column,
_stop_times_stop_id_column,
Column('arrival_time', Integer, nullable=True),
Column('departure_time', Integer, nullable=True),
Column('interpolated', Boolean, nullable=False),
Column('shape_dist_traveled', Float, nullable=False),
Column('timepoint', Integer, nullable=False),
Column('pickup_type', Integer, nullable=False),
Column('drop_off_type', Integer, nullable=False),
Column('stop_headsign', String),
ForeignKeyConstraint(['feed_id', 'trip_id'], ['trips.feed_id', 'trips.trip_id']),
ForeignKeyConstraint(['feed_id', 'stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_stop_times_stop', 'feed_id', 'stop_id'),
Index('idx_stop_times_sequence', 'feed_id', 'stop_sequence'))
self.mappers.append(mapper(StopTime, _stop_times_mapper, properties={
# Note: here we specify foreign() on stop_times feed_id column as there is no ownership relation of feed to stop_times
'trip' : relationship(Trip, backref=backref('stop_times', order_by=_stop_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_trip_id_column == foreign(_stop_times_trip_id_column)) & (_trip_feed_id_column == foreign(_stop_times_feed_id_column))),
'stop' : relationship(Stop, backref=backref('stop_times', cascade="all,delete-orphan"),
primaryjoin=(_stop_id_column == foreign(_stop_times_stop_id_column)) & (_stop_feed_id_column == _stop_times_feed_id_column)),
}))
_fareattr_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_fareattr_id_column = Column('fare_id', String, primary_key=True)
_fareattr_mapper = Table('fare_attributes', self._metadata,
_fareattr_feed_id_column,
_fareattr_id_column,
Column('price', Float, nullable=False),
Column('currency_type', String, nullable=False),
Column('payment_method', Integer, nullable=False),
Column('transfers', Integer),
Column('transfer_duration', Integer))
self.mappers.append(mapper(FareAttribute, _fareattr_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('fare_attributes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_fareattr_feed_id_column))
}))
_farerule_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_farerule_id_column = Column('fare_id', String, primary_key=True)
_farerule_route_id_column = Column('route_id', String, primary_key=True, nullable=True)
_farerule_origin_id_column = Column('origin_id', String, primary_key=True, nullable=True)
_farerule_destination_id_column = Column('destination_id', String, primary_key=True, nullable=True)
_farerule_contains_id_column = Column('contains_id', String, primary_key=True, nullable=True)
_farerule_mapper = Table('fare_rules', self._metadata,
_farerule_feed_id_column,
_farerule_id_column,
_farerule_route_id_column,
_farerule_origin_id_column,
_farerule_destination_id_column,
_farerule_contains_id_column,
ForeignKeyConstraint(['feed_id', 'fare_id'], ['fare_attributes.feed_id', 'fare_attributes.fare_id']))
self.mappers.append(mapper(FareRule, _farerule_mapper, properties={
'fare_attribute' : relationship(FareAttribute, backref=backref('fare_rules', cascade="all,delete-orphan")),
'route' : relationship(Route, backref=backref('fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_farerule_route_id_column)) & (_route_feed_id_column == _farerule_feed_id_column)),
'origin' : relationship(Zone, backref=backref('origin_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_origin_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'destination' : relationship(Zone, backref=backref('destination_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_destination_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'contains' : relationship(Zone, backref=backref('contains_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_contains_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column))
}))
self._metadata.create_all(engine)
self._class_for_table = {}
self._table_for_class = {}
for _mapper in self.mappers:
self._class_for_table[_mapper.mapped_table.name] = _mapper.class_
self._table_for_class[_mapper.class_] = _mapper.mapped_table.name
def class_for_table(self, tablename):
"""Return the class associated to a given table name.
We implement ourselves this method as there does not seem a reliable way
of getting this information from SqlAlchemy itself w/o some brittle hacking."""
return self._class_for_table.get(tablename)
def table_for_class(self, clazz):
"""Return the table name associated to a give entity class."""
return self._table_for_class.get(clazz)
class _Orm(object):
def __init__(self, engine, schema=None):
self._metadata = MetaData(schema=schema)
self.mappers = []
_feedinfo_id_column = Column('feed_id', String, primary_key=True)
_agency_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_route_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_feedinfo_mapper = Table('feed_info', self._metadata,
_feedinfo_id_column,
Column('feed_publisher_name', String),
Column('feed_publisher_url', String),
Column('feed_contact_email', String), # Non-standard (yet) field
Column('feed_contact_url', String), # Non-standard (yet) field
Column('feed_lang', String),
Column('feed_start_date', Date),
Column('feed_end_date', Date),
Column('feed_version', String))
self.mappers.append(mapper(FeedInfo, _feedinfo_mapper, properties={
}))
_agency_id_column = Column('agency_id', String, primary_key=True)
_route_agency_id_column = Column('agency_id', String, nullable=False)
_agency_mapper = Table('agency', self._metadata,
_agency_feed_id_column,
_agency_id_column,
Column('agency_name', String, nullable=False),
Column('agency_url', String, nullable=False),
Column('agency_timezone', String, nullable=False),
Column('agency_lang', String),
Column('agency_phone', String),
Column('agency_fare_url', String),
Column('agency_email', String))
self.mappers.append(mapper(Agency, _agency_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('agencies', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_agency_feed_id_column))
}))
_zone_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_zone_id_column = Column('zone_id', String, primary_key=True)
_zone_mapper = Table('zones', self._metadata,
_zone_feed_id_column,
_zone_id_column)
self.mappers.append(mapper(Zone, _zone_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('zones', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_zone_feed_id_column))
}))
_stop_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_id_column = Column('stop_id', String, primary_key=True)
_stop_parent_id_column = Column('parent_station_id', String, nullable=True)
_stop_zone_id_column = Column('zone_id', String, nullable=True)
_stop_mapper = Table('stops', self._metadata,
_stop_feed_id_column,
_stop_id_column,
_stop_parent_id_column,
Column('location_type', Integer, nullable=False),
Column('stop_name', String, nullable=False),
Column('stop_lat', Float, nullable=False),
Column('stop_lon', Float, nullable=False),
Column('wheelchair_boarding', Integer, nullable=False),
Column('stop_code', String),
Column('stop_desc', String),
_stop_zone_id_column,
Column('stop_url', String),
Column('stop_timezone', String),
ForeignKeyConstraint(['feed_id', 'parent_station_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'zone_id'], ['zones.feed_id', 'zones.zone_id']),
# TODO Make those index parametrable
Index('idx_stops_lat', 'stop_lat'),
Index('idx_stops_lon', 'stop_lon'),
Index('idx_stops_code', 'feed_id', 'stop_code'),
Index('idx_stops_zone', 'feed_id', 'zone_id'),
Index('idx_stops_parent', 'feed_id', 'parent_station_id'))
self.mappers.append(mapper(Stop, _stop_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_stop_feed_id_column)),
'sub_stops' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_parent_id_column], uselist=True,
primaryjoin=(_stop_parent_id_column == foreign(_stop_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'parent_station' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_id_column],
primaryjoin=(_stop_id_column == foreign(_stop_parent_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'zone' : relationship(Zone, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_stop_zone_id_column)) & (_zone_feed_id_column == _stop_feed_id_column))
}))
_transfer_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_transfer_from_stop_id_column = Column('from_stop_id', String, primary_key=True)
_transfer_to_stop_id_column = Column('to_stop_id', String, primary_key=True)
_transfer_mapper = Table('transfers', self._metadata,
_transfer_feed_id_column,
_transfer_from_stop_id_column,
_transfer_to_stop_id_column,
Column('transfer_type', Integer, nullable=False),
Column('min_transfer_time', Integer),
ForeignKeyConstraint(['feed_id', 'from_stop_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'to_stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_transfer_from', 'feed_id', 'from_stop_id'),
Index('idx_transfer_to', 'feed_id', 'to_stop_id'))
self.mappers.append(mapper(Transfer, _transfer_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('transfers', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_transfer_feed_id_column)),
'from_stop' : relationship(Stop, backref=backref('from_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_from_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column)),
'to_stop' : relationship(Stop, backref=backref('to_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_to_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column))
}))
_route_id_column = Column('route_id', String, primary_key=True)
_route_mapper = Table('routes', self._metadata,
_route_feed_id_column,
_route_id_column,
_route_agency_id_column,
Column('route_short_name', String),
Column('route_long_name', String),
Column('route_desc', String),
Column('route_type', Integer, nullable=False),
Column('route_url', String),
Column('route_color', String),
Column('route_text_color', String),
ForeignKeyConstraint(['feed_id', 'agency_id'], ['agency.feed_id', 'agency.agency_id']),
Index('idx_routes_agency', 'feed_id', 'agency_id'),
Index('idx_routes_short_name', 'feed_id', 'route_short_name'),
Index('idx_routes_type', 'feed_id', 'route_type'))
self.mappers.append(mapper(Route, _route_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_route_feed_id_column)),
'agency' : relationship(Agency, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=(_agency_id_column == foreign(_route_agency_id_column)) & (_agency_feed_id_column == _route_feed_id_column))
}))
_calendar_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_calendar_id_column = Column('service_id', String, primary_key=True)
_calendar_mapper = Table('calendar', self._metadata,
_calendar_feed_id_column,
_calendar_id_column
)
self.mappers.append(mapper(Calendar, _calendar_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('calendars', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_calendar_feed_id_column))
}))
_calendar_date_mapper = Table('calendar_dates', self._metadata,
Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True),
Column('service_id', String, primary_key=True),
Column('date', Date, primary_key=True),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
# TOCHECK It seems a composite primary key on (a,b,c) does not need indexing on left elements,
# such as (a) and (a,b); but need on (a,c) for example.
Index('idx_calendar_dates_date', 'feed_id', 'date'))
self.mappers.append(mapper(CalendarDate, _calendar_date_mapper, properties={
'calendar' : relationship(Calendar, backref=backref('dates', cascade="all,delete-orphan"))
}))
_shape_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_mapper = Table('shapes', self._metadata,
_shape_feed_id_column,
_shape_id_column
)
self.mappers.append(mapper(Shape, _shape_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('shapes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_shape_feed_id_column))
}))
_shape_pt_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_pt_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_pt_seq_column = Column('shape_pt_sequence', Integer, primary_key=True)
_shape_pt_mapper = Table('shape_pts', self._metadata,
_shape_pt_feed_id_column,
_shape_pt_shape_id_column,
_shape_pt_seq_column,
Column('shape_dist_traveled', Float, nullable=False),
Column('shape_pt_lat', Float, nullable=False),
Column('shape_pt_lon', Float, nullable=False),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_shape_pt_shape', 'feed_id', 'shape_id'))
self.mappers.append(mapper(ShapePoint, _shape_pt_mapper, properties={
# Note: here we specify foreign() on shape_pt feed_id column as there is no ownership relation of feed to shape_pts
'shape' : relationship(Shape, backref=backref('points', order_by=_shape_pt_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_shape_pt_shape_id_column)) & (_shape_feed_id_column == foreign(_shape_pt_feed_id_column)))
}))
_trip_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_trip_id_column = Column('trip_id', String, primary_key=True)
_trip_route_id_column = Column('route_id', String, nullable=False)
_trip_calendar_id_column = Column('service_id', String, nullable=False)
_trip_shape_id_column = Column('shape_id', String, nullable=True)
_trip_mapper = Table('trips', self._metadata,
_trip_feed_id_column,
_trip_id_column,
_trip_route_id_column,
_trip_calendar_id_column,
_trip_shape_id_column,
Column('wheelchair_accessible', Integer, nullable=False),
Column('bikes_allowed', Integer, nullable=False),
Column('exact_times', Integer, nullable=False),
Column('frequency_generated', Boolean, nullable=False),
Column('trip_headsign', String),
Column('trip_short_name', String),
Column('direction_id', Integer),
Column('block_id', String),
ForeignKeyConstraint(['feed_id', 'route_id'], ['routes.feed_id', 'routes.route_id']),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_trips_route', 'feed_id', 'route_id'),
Index('idx_trips_service', 'feed_id', 'service_id'))
self.mappers.append(mapper(Trip, _trip_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_trip_feed_id_column)),
'route' : relationship(Route, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_trip_route_id_column)) & (_route_feed_id_column == _trip_feed_id_column)),
'calendar' : relationship(Calendar, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_calendar_id_column == foreign(_trip_calendar_id_column)) & (_calendar_feed_id_column == _trip_feed_id_column)),
'shape' : relationship(Shape, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_trip_shape_id_column)) & (_shape_feed_id_column == _trip_feed_id_column))
}))
_stop_times_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_times_trip_id_column = Column('trip_id', String, primary_key=True)
_stop_seq_column = Column('stop_sequence', Integer, primary_key=True)
_stop_times_stop_id_column = Column('stop_id', String, nullable=False)
_stop_times_mapper = Table('stop_times', self._metadata,
_stop_times_feed_id_column,
_stop_times_trip_id_column,
_stop_seq_column,
_stop_times_stop_id_column,
Column('arrival_time', Integer, nullable=True),
Column('departure_time', Integer, nullable=True),
Column('interpolated', Boolean, nullable=False),
Column('shape_dist_traveled', Float, nullable=False),
Column('timepoint', Integer, nullable=False),
Column('pickup_type', Integer, nullable=False),
Column('drop_off_type', Integer, nullable=False),
Column('stop_headsign', String),
ForeignKeyConstraint(['feed_id', 'trip_id'], ['trips.feed_id', 'trips.trip_id']),
ForeignKeyConstraint(['feed_id', 'stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_stop_times_stop', 'feed_id', 'stop_id'),
Index('idx_stop_times_sequence', 'feed_id', 'stop_sequence'))
self.mappers.append(mapper(StopTime, _stop_times_mapper, properties={
# Note: here we specify foreign() on stop_times feed_id column as there is no ownership relation of feed to stop_times
'trip' : relationship(Trip, backref=backref('stop_times', order_by=_stop_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_trip_id_column == foreign(_stop_times_trip_id_column)) & (_trip_feed_id_column == foreign(_stop_times_feed_id_column))),
'stop' : relationship(Stop, backref=backref('stop_times', cascade="all,delete-orphan"),
primaryjoin=(_stop_id_column == foreign(_stop_times_stop_id_column)) & (_stop_feed_id_column == _stop_times_feed_id_column)),
}))
_fareattr_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_fareattr_id_column = Column('fare_id', String, primary_key=True)
_fareattr_mapper = Table('fare_attributes', self._metadata,
_fareattr_feed_id_column,
_fareattr_id_column,
Column('price', Float, nullable=False),
Column('currency_type', String, nullable=False),
Column('payment_method', Integer, nullable=False),
Column('transfers', Integer),
Column('transfer_duration', Integer))
self.mappers.append(mapper(FareAttribute, _fareattr_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('fare_attributes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_fareattr_feed_id_column))
}))
_farerule_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'))
_farerule_id_column = Column('fare_id', String)
# Use a dummy autoincrement numerical field for primary key,
# as a primary key is mandatory, and the natural primary key
# for the model (feed_id, fare_id, route+zones ids) do have
# some fields that can be null.
_farerule_rule_id_column = Column('fare_rule_id', Integer, primary_key=True, autoincrement=True)
_farerule_route_id_column = Column('route_id', String, nullable=True)
_farerule_origin_id_column = Column('origin_id', String, nullable=True)
_farerule_destination_id_column = Column('destination_id', String, nullable=True)
_farerule_contains_id_column = Column('contains_id', String, nullable=True)
_farerule_mapper = Table('fare_rules', self._metadata,
_farerule_feed_id_column,
_farerule_id_column,
_farerule_rule_id_column,
_farerule_route_id_column,
_farerule_origin_id_column,
_farerule_destination_id_column,
_farerule_contains_id_column,
ForeignKeyConstraint(['feed_id', 'fare_id'], ['fare_attributes.feed_id', 'fare_attributes.fare_id']))
self.mappers.append(mapper(FareRule, _farerule_mapper, properties={
'fare_attribute' : relationship(FareAttribute, backref=backref('fare_rules', cascade="all,delete-orphan")),
'route' : relationship(Route, backref=backref('fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_farerule_route_id_column)) & (_route_feed_id_column == _farerule_feed_id_column)),
'origin' : relationship(Zone, backref=backref('origin_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_origin_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'destination' : relationship(Zone, backref=backref('destination_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_destination_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'contains' : relationship(Zone, backref=backref('contains_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_contains_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column))
}))
self._metadata.create_all(engine)
self._class_for_table = {}
self._table_for_class = {}
for _mapper in self.mappers:
self._class_for_table[_mapper.mapped_table.name] = _mapper.class_
self._table_for_class[_mapper.class_] = _mapper.mapped_table.name
def class_for_table(self, tablename):
"""Return the class associated to a given table name.
We implement ourselves this method as there does not seem a reliable way
of getting this information from SqlAlchemy itself w/o some brittle hacking."""
return self._class_for_table.get(tablename)
def table_for_class(self, clazz):
"""Return the table name associated to a give entity class."""
return self._table_for_class.get(clazz)
Column('id', Integer, primary_key=True),
Column('coin', String(10)),
Column('user_phone', String(40)),
Column('email_address', String(40)),
Column('price', Float(10)),
Column('create_at', DateTime),
Column('update_at', DateTime),
)
coin_price_history = Table(
'coin_price_history',
meta_data,
Column('id', Integer, primary_key=True),
Column('coin', String(10)),
Column('user_phone', String(40)),
Column('email_address', String(40)),
Column('price', Float(10)),
Column('create_at', DateTime),
Column('update_at', DateTime),
)
meta_data.create_all(engine)
BaseData = declarative_base()
def dao_to_dict(model):
columns = [c.key for c in class_mapper(model.__class__).columns]
return dict((c, getattr(model, c)) for c in columns
if c != 'create_at' and c != 'update_at')
def set_schema(self, meta_data: MetaData) -> None:
"""Create tables based on the metadata object."""
meta_data.create_all(bind=self.conn)