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)