ALL_TABLES = [ TABLE_STATES, TABLE_EVENTS, TABLE_RECORDER_RUNS, TABLE_SCHEMA_CHANGES, TABLE_STATISTICS, TABLE_STATISTICS_META, TABLE_STATISTICS_RUNS, TABLE_STATISTICS_SHORT_TERM, ] DATETIME_TYPE = DateTime(timezone=True).with_variant( mysql.DATETIME(timezone=True, fsp=6), "mysql") DOUBLE_TYPE = (Float().with_variant( mysql.DOUBLE(asdecimal=False), "mysql").with_variant(oracle.DOUBLE_PRECISION(), "oracle").with_variant(postgresql.DOUBLE_PRECISION(), "postgresql")) class Events(Base): # type: ignore """Event history data.""" __table_args__ = ( # Used for fetching events at a specific time # see logbook Index("ix_events_event_type_time_fired", "event_type", "time_fired"), { "mysql_default_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci" },
# Import Table, Column, String, Integer, Float, Boolean from sqlalchemy from sqlalchemy import Table, Column, String, Integer, Float, Boolean # Define a new table with a name, count, amount, and valid column: data data = Table('data', metadata, Column('name', String(255)), Column('count', Integer()), Column('amount', Float()), Column('valid', Boolean())) # Use the metadata to create the table metadata.create_all(engine) # Print table details print(repr(engine.table_names()))
class QueueItem(Base): """ queue_weight - rational and description The id will never change, but the queue will be sorted by this weight An admin can change the order of the queue, The algorithum with list the queed items in weight order and set the new weight to be inbetween the prev,next queued item values. This supports order but without changing the primary key To ensure the queue_weight is set approriately, a pre save handler will query the to find an approriate values on first save """ __tablename__ = "queue" id = Column(Integer(), primary_key=True) track_id = Column(String(), ForeignKey('track.id'), nullable=False) performer_name = Column(Unicode(), nullable=True, default="Untitled") session_owner = Column(Unicode(), nullable=True) queue_weight = Column(Float() , index=True, nullable=False) # this by default is set to the id on first save, time_added = Column(DateTime(), nullable=False, default=now) time_touched = Column(DateTime(), nullable=False, default=now) status = Column(_queueitem_statuss , nullable=False, default="pending") # AllanC - there is no linking of the models now. Track and Queue are linked at an API level and can be in two separate DB's #track = relationship("Track", backref=backref('queued')) # # AllanC - this makes the queue aware of the track and tightens coupleling ... ultimatelty the que and tracks should be in differnt db's but then the sqla links wont function ... think neo!! __to_dict__ = copy.deepcopy(Base.__to_dict__) __to_dict__.update({ 'default': { #Base.to_dict_setup(self, list_type='default', field_processors={ 'id' : None , 'track_id' : None , 'performer_name': None , 'time_touched' : None , 'time_added' : None , 'queue_weight' : None , }, }) __to_dict__.update({'full': copy.deepcopy(__to_dict__['default'])}) __to_dict__['full'].update({ 'status' : None , 'session_owner': None , #'track' : lambda queue_item: queue_item.track.to_dict(include_fields='attachments'), #'image' : lambda queue_item: single_image(queue_item), # AllanC - if you use this ensure you have setup eager loading on your query }) @staticmethod def new_weight(DBSession): """ Find the biggest queue weight possible and increment. Used when creating new queue items or moving items in the list beyond the last element """ try: (max_weight,) = DBSession.query(QueueItem.queue_weight).order_by(QueueItem.queue_weight.desc()).limit(1).one() except NoResultFound: max_weight = 0.0 return max_weight + 1.0 @staticmethod def before_insert_listener(mapper, connection, target): """ Event to set weight of queued item before first commit. Find the maximum 'weight' in the database This queue item weight will be set to max_weight + 1.0 """ if not target.queue_weight: target.queue_weight = QueueItem.new_weight(Session(bind=connection))
def load_unicode_test_data(only_metadata=False, force=False): """Loading unicode test dataset from a csv file in the repo""" tbl_name = "unicode_test" database = utils.get_example_database() table_exists = database.has_table_by_name(tbl_name) if not only_metadata and (not table_exists or force): data = get_example_data("unicode_utf8_unixnl_test.csv", is_gzip=False, make_bytes=True) df = pd.read_csv(data, encoding="utf-8") # generate date/numeric data df["dttm"] = datetime.datetime.now().date() df["value"] = [random.randint(1, 100) for _ in range(len(df))] df.to_sql( # pylint: disable=no-member tbl_name, database.get_sqla_engine(), if_exists="replace", chunksize=500, dtype={ "phrase": String(500), "short_phrase": String(10), "with_missing": String(100), "dttm": Date(), "value": Float(), }, index=False, ) print("Done loading table!") print("-" * 80) print("Creating table [unicode_test] reference") obj = db.session.query(TBL).filter_by(table_name=tbl_name).first() if not obj: obj = TBL(table_name=tbl_name) obj.main_dttm_col = "dttm" obj.database = database db.session.merge(obj) db.session.commit() obj.fetch_metadata() tbl = obj slice_data = { "granularity_sqla": "dttm", "groupby": [], "metric": { "aggregate": "SUM", "column": { "column_name": "value" }, "expressionType": "SIMPLE", "label": "Value", }, "row_limit": config.get("ROW_LIMIT"), "since": "100 years ago", "until": "now", "where": "", "viz_type": "word_cloud", "size_from": "10", "series": "short_phrase", "size_to": "70", "rotation": "square", "limit": "100", } print("Creating a slice") slc = Slice( slice_name="Unicode Cloud", viz_type="word_cloud", datasource_type="table", datasource_id=tbl.id, params=get_slice_json(slice_data), ) merge_slice(slc) print("Creating a dashboard") dash = db.session.query(Dash).filter_by(slug="unicode-test").first() if not dash: dash = Dash() js = """\ { "CHART-Hkx6154FEm": { "children": [], "id": "CHART-Hkx6154FEm", "meta": { "chartId": 2225, "height": 30, "sliceName": "slice 1", "width": 4 }, "type": "CHART" }, "GRID_ID": { "children": [ "ROW-SyT19EFEQ" ], "id": "GRID_ID", "type": "GRID" }, "ROOT_ID": { "children": [ "GRID_ID" ], "id": "ROOT_ID", "type": "ROOT" }, "ROW-SyT19EFEQ": { "children": [ "CHART-Hkx6154FEm" ], "id": "ROW-SyT19EFEQ", "meta": { "background": "BACKGROUND_TRANSPARENT" }, "type": "ROW" }, "DASHBOARD_VERSION_KEY": "v2" } """ dash.dashboard_title = "Unicode Test" pos = json.loads(js) update_slice_ids(pos, [slc]) dash.position_json = json.dumps(pos, indent=4) dash.slug = "unicode-test" dash.slices = [slc] db.session.merge(dash) db.session.commit()
class Asset_table(Base): __tablename__ = 'asset_orm' asset_id = Column(Integer(), primary_key=True) user_id = Column(Integer(), index=True, nullable=False) balance = Column(Float(), index=True, nullable=False)
class Point(object): id = Column(Integer, primary_key=True) latitude = Column(Float()) longitude = Column(Float())
class Filingtype(RefTypeMixin, AuditMixin, Model): __tablename__ = 'filingtype' id = Column(Integer, primary_key=True, autoincrement=True) cost = Column(Float(53), nullable=False) perpagecost = Column(Float(53), nullable=False)
def buscarId(matriz, identificador): for valores in matriz: if valores.id == identificador: return valores return False metadata = MetaData() #Estructura de la tabla todo todo = Table( 'todo', metadata, Column('id', Integer, primary_key=True), Column('temperatura', Float()), Column('humedad', Float()), Column('canal1', Float()), Column('canal2', Float()), Column('canal3', Float()), Column('canal4', Float()), Column('tempGabinete', Float()), Column('hora', Time()), Column('fecha', Date()), ) #Estructura de la tabla configuracion configuracion = Table('configuracion', metadata, Column('id', Integer, primary_key=True), Column('tipo', Integer), Column('frec', Integer), Column('potmax', Float()), Column('potmin', Integer()),
def load_energy(): """Loads an energy related dataset to use with sankey and graphs""" tbl_name = 'energy_usage' with gzip.open(os.path.join(DATA_FOLDER, 'energy.json.gz')) as f: pdf = pd.read_json(f) pdf.to_sql( tbl_name, db.engine, if_exists='replace', chunksize=500, dtype={ 'source': String(255), 'target': String(255), 'value': Float(), }, index=False) print("Creating table [wb_health_population] reference") tbl = db.session.query(TBL).filter_by(table_name=tbl_name).first() if not tbl: tbl = TBL(table_name=tbl_name) tbl.description = "Energy consumption" tbl.database = get_or_create_main_db() db.session.merge(tbl) db.session.commit() tbl.fetch_metadata() slc = Slice( slice_name="Energy Sankey", viz_type='sankey', datasource_type='table', datasource_id=tbl.id, params=textwrap.dedent("""\ { "collapsed_fieldsets": "", "groupby": [ "source", "target" ], "having": "", "metric": "sum__value", "row_limit": "5000", "slice_name": "Energy Sankey", "viz_type": "sankey", "where": "" } """) ) misc_dash_slices.append(slc.slice_name) merge_slice(slc) slc = Slice( slice_name="Energy Force Layout", viz_type='directed_force', datasource_type='table', datasource_id=tbl.id, params=textwrap.dedent("""\ { "charge": "-500", "collapsed_fieldsets": "", "groupby": [ "source", "target" ], "having": "", "link_length": "200", "metric": "sum__value", "row_limit": "5000", "slice_name": "Force", "viz_type": "directed_force", "where": "" } """) ) misc_dash_slices.append(slc.slice_name) merge_slice(slc) slc = Slice( slice_name="Heatmap", viz_type='heatmap', datasource_type='table', datasource_id=tbl.id, params=textwrap.dedent("""\ { "all_columns_x": "source", "all_columns_y": "target", "canvas_image_rendering": "pixelated", "collapsed_fieldsets": "", "having": "", "linear_color_scheme": "blue_white_yellow", "metric": "sum__value", "normalize_across": "heatmap", "slice_name": "Heatmap", "viz_type": "heatmap", "where": "", "xscale_interval": "1", "yscale_interval": "1" } """) ) misc_dash_slices.append(slc.slice_name) merge_slice(slc)
class Auction(Base): __tablename__ = 'auctions' aid = Column(Integer, primary_key=True, server_default=text("nextval('auctions_aid_seq'::regclass)")) iid = Column(ForeignKey('item.iid')) startingbid = Column(Float(53)) adeadline = Column(String(8)) item = relationship('Item') t_bids = Table('bids', metadata, Column('aid', ForeignKey('auctions.aid')), Column('uid', ForeignKey('artuser.uid')), Column('bid', Float(53))) class Creditcard(Base): __tablename__ = 'creditcard' cid = Column( Integer, primary_key=True, server_default=text("nextval('creditcard_cid_seq'::regclass)")) uid = Column(ForeignKey('artuser.uid')) cname = Column(String(15)) cnumber = Column(Integer) ctype = Column(String(15)) expdate = Column(String(8))
class Wallet(Base): __tablename__ = 'wallet' id = Column(String(50), primary_key=True) balance = Column(Float(50), nullable=False) coin_symbol = Column(String(10), nullable=False)
Track.c.size, unique=True, ) TrackStore = table( 'track_storage', Column('name', String(1024), nullable=False), ) TrackLocation = table( 'track_location', Column('track', None, ForeignKey('track.uuid'), nullable=False), Column('location', String(4096), nullable=False), Column('store', None, ForeignKey('track_storage.uuid'), nullable=False), ) Play = table( 'play', Column('track', None, ForeignKey('track.uuid'), nullable=False), Column('mood', String(1024), nullable=False), Column('played_seconds', Float(), nullable=False), Column('positive_feedback', Boolean(), nullable=True), Column('reinforced_at_time', DateTime(), nullable=True), ) State = Table( 'state', metadata, Column('mood', String(1024), nullable=True), )
class CareerPath(DBBASE): """ Different career path stages """ __colanderalchemy_config__ = { 'title': u"Etape de parcours", 'help_msg': u"", 'validation_msg': u"L'étape de parcours a bien été enregistrée", 'widget': deform_extensions.GridFormWidget(named_grid=CAREER_PATH_GRID) } __tablename__ = 'career_path' __table_args__ = default_table_args id = Column( 'id', Integer, primary_key=True, info={'colanderalchemy': { 'widget': deform.widget.HiddenWidget() }}, ) userdatas_id = Column( ForeignKey('user_datas.id'), info={ 'colanderalchemy': { 'exclude': True }, 'export': { 'label': u"Identifiant Autonomie", 'stats': { 'exclude': True }, } }, ) userdatas = relationship('UserDatas', info={ 'colanderalchemy': { 'exclude': True }, 'export': { 'exclude': True }, }) start_date = Column(Date(), nullable=False, info={'colanderalchemy': { 'title': u"Date d'effet" }}) end_date = Column(Date(), info={'colanderalchemy': { 'title': u"Date d'échéance" }}) career_stage_id = Column( ForeignKey('career_stage.id'), info={'colanderalchemy': { 'title': u"Type d'étape" }}) career_stage = relationship( 'CareerStage', primaryjoin='CareerStage.id==CareerPath.career_stage_id', info={ 'colanderalchemy': get_excluded_colanderalchemy(u'Etape de parcours'), 'export': { 'related_key': 'label' }, }, ) cae_situation_id = Column( ForeignKey('cae_situation_option.id'), info={ 'colanderalchemy': { 'title': u"Nouvelle situation dans la CAE", 'description': u"Lorsque cette étape sera affectée à un \ porteur cette nouvelle situation sera proposée par défaut" } }) cae_situation = relationship( 'CaeSituationOption', primaryjoin='CaeSituationOption.id==CareerPath.cae_situation_id', info={ 'colanderalchemy': get_excluded_colanderalchemy(u'Situation dans la CAE'), 'export': { 'related_key': 'label' }, }, ) stage_type = Column(String(15), info={ 'colanderalchemy': { 'title': u"Type d'étape" }, 'export': { 'formatter': lambda val: dict(STAGE_TYPE_OPTIONS).get(val), 'stats': { 'options': STAGE_TYPE_OPTIONS }, } }) type_contrat_id = Column( ForeignKey('type_contrat_option.id'), info={'colanderalchemy': { 'title': u"Type de contrat" }}) type_contrat = relationship( 'TypeContratOption', info={ 'colanderalchemy': get_excluded_colanderalchemy(u'Type de contrat'), 'export': { 'related_key': 'label' }, }, ) employee_quality_id = Column( ForeignKey('employee_quality_option.id'), info={'colanderalchemy': { 'title': u"Qualité du salarié" }}) employee_quality = relationship( 'EmployeeQualityOption', info={ 'colanderalchemy': get_excluded_colanderalchemy(u"Qualité du salarié"), 'export': { 'related_key': 'label' }, }) taux_horaire = Column(Float(), info={'colanderalchemy': { 'title': u"Taux horaire" }}) num_hours = Column(Float(), info={'colanderalchemy': { 'title': u"Nombre d'heures" }}) goals_amount = Column( Float(), info={'colanderalchemy': { 'title': u"Objectif de CA / d'activité" }}) goals_period = Column(String(15), info={ 'colanderalchemy': { 'title': u"Période de l'objectif" }, 'export': { 'formatter': lambda val: dict(PERIOD_OPTIONS).get(val), 'stats': { 'options': PERIOD_OPTIONS }, } }) amendment_number = Column( Integer(), info={'colanderalchemy': { 'title': u"Numéro de l'avenant" }}) type_sortie_id = Column( ForeignKey('type_sortie_option.id'), info={'colanderalchemy': { 'title': u"Type de sortie" }}) type_sortie = relationship( 'TypeSortieOption', info={ 'colanderalchemy': get_excluded_colanderalchemy(u"Type de sortie"), 'export': { 'related_key': 'label' }, }, ) motif_sortie_id = Column( ForeignKey('motif_sortie_option.id'), info={'colanderalchemy': { 'title': u"Motif de sortie" }}) motif_sortie = relationship( 'MotifSortieOption', info={ 'colanderalchemy': get_excluded_colanderalchemy(u"Motif de sortie"), 'export': { 'related_key': 'label' }, }, ) @classmethod def query(cls, user): q = super(CareerPath, cls).query() q = q.filter(CareerPath.userdatas_id == user) return q.order_by(CareerPath.start_date.desc())
from sqlalchemy import Table, Column, String, Integer, Float, Boolean, MetaData, create_engine engine = create_engine('mysql+pymysql://root:1234@localhost:3306/db1') meta = MetaData() data = Table('data', meta, Column('name', String(255), unique=True), Column('count', Integer(), default=1), Column('amount', Float()), Column('valid', Boolean(), default=False)) meta.create_all(engine) print(repr(meta.tables['data']))
def test_compare_float_no_diff6(self): self._compare_default_roundtrip( Float(), "5", text("5.0"), diff_expected=False )
def load_unicode_test_data(): """Loading unicode test dataset from a csv file in the repo""" df = pd.read_csv(os.path.join(DATA_FOLDER, 'unicode_utf8_unixnl_test.csv'), encoding="utf-8") # generate date/numeric data df['date'] = datetime.datetime.now().date() df['value'] = [random.randint(1, 100) for _ in range(len(df))] df.to_sql( # pylint: disable=no-member 'unicode_test', db.engine, if_exists='replace', chunksize=500, dtype={ 'phrase': String(500), 'short_phrase': String(10), 'with_missing': String(100), 'date': Date(), 'value': Float(), }, index=False) print("Done loading table!") print("-" * 80) print("Creating table [unicode_test] reference") obj = db.session.query(TBL).filter_by(table_name='unicode_test').first() if not obj: obj = TBL(table_name='unicode_test') obj.main_dttm_col = 'date' obj.database = get_or_create_main_db() db.session.merge(obj) db.session.commit() obj.fetch_metadata() tbl = obj slice_data = { "granularity": "date", "groupby": [], "metric": 'sum__value', "row_limit": config.get("ROW_LIMIT"), "since": "100 years ago", "until": "now", "where": "", "viz_type": "word_cloud", "size_from": "10", "series": "short_phrase", "size_to": "70", "rotation": "square", "limit": "100", } print("Creating a slice") slc = Slice( slice_name="Unicode Cloud", viz_type='word_cloud', datasource_type='table', datasource_id=tbl.id, params=get_slice_json(slice_data), ) merge_slice(slc) print("Creating a dashboard") dash = ( db.session.query(Dash) .filter_by(dashboard_title="Unicode Test") .first() ) if not dash: dash = Dash() pos = { "size_y": 4, "size_x": 4, "col": 1, "row": 1, "slice_id": slc.id, } dash.dashboard_title = "Unicode Test" dash.position_json = json.dumps([pos], indent=4) dash.slug = "unicode-test" dash.slices = [slc] db.session.merge(dash) db.session.commit()
class SongStat(schema.Base): """ SongStat model, represent the score obtain by a user in a song """ __tablename__ = 'song_stats' GRADES = { 0: "AAAA", 1: "AAA", 2: "AA", 3: "A", 4: "B", 5: "C", 6: "D", 7: "F", } DIFFICULTIES = { 0: "BEGINNER", 1: "EASY", 2: "MEDIUM", 3: "HARD", 4: "EXPERT", } stepid = { 1: "hit_mine", 2: "avoid_mine", 3: "miss", 4: "bad", 5: "good", 6: "great", 7: "perfect", 8: "flawless", 9: "not_held", 10: "held" } id = Column(Integer, primary_key=True) song_id = Column(Integer, ForeignKey('songs.id')) song = relationship("Song", back_populates="stats") user_id = Column(Integer, ForeignKey('users.id')) user = relationship("User", back_populates="song_stats") game_id = Column(Integer, ForeignKey('games.id')) game = relationship("Game", back_populates="song_stats") hit_mine = Column(Integer, default=0) avoid_mine = Column(Integer, default=0) miss = Column(Integer, default=0) bad = Column(Integer, default=0) good = Column(Integer, default=0) great = Column(Integer, default=0) perfect = Column(Integer, default=0) flawless = Column(Integer, default=0) not_held = Column(Integer, default=0) held = Column(Integer, default=0) max_combo = Column(Integer, default=0) options = Column(Text, default="") score = Column(Integer, default=0) grade = Column(Integer, default=0, index=True) difficulty = Column(Integer, default=0) feet = Column(Integer, default=0, index=True) percentage = Column(Float(precision=5)) duration = Column(Integer, default=0) raw_stats = Column(LargeBinary) created_at = Column(DateTime, default=datetime.datetime.now) updated_at = Column(DateTime, onupdate=datetime.datetime.now) def __repr__(self): return "<SongStat #%s score=%s (%s%%)>" % (self.id, self.score, self.percentage) @property def lit_difficulty(self): """ Difficulty as a string (EASY, MEDIUM, HARD, ...) """ if self.difficulty is None: return "Unknown" return self.DIFFICULTIES.get(self.difficulty, str(self.difficulty)) @property def full_difficulty(self): """ Difficulty with feet as a string. eg EASY (3) """ return "%s (%s)" % (self.lit_difficulty, self.feet) @property def lit_grade(self): """ Grade as a string (AA, E, F, B, ...)""" if self.grade is None: return "Unknown" return self.GRADES.get(self.grade, self.grade) def pretty_result(self, room_id=None, color=False): """ Return a pretty result for the result """ color_func = with_color if color else lambda x, **_: x return "{difficulty}: {user_name} {grade} ({percentage:.2f}%) on {date}".format( difficulty=color_func(self.full_difficulty, color=nick_color(self.lit_difficulty)), user_name=self.user.fullname_colored(room_id) if color else self.user.fullname(room_id), grade=color_func(self.lit_grade), percentage=self.percentage, date=self.created_at.strftime("%d/%m/%y")) def calc_percentage(self, config=None): """ Calculate the percentage given the input """ if not config: config = { "not_held": 0, "miss": 0, "bad": 0, "good": 0, "held": 3, "hit_mine": -2, "great": 1, "perfect": 2, "flawless": 3 } max_weight = max(config.values()) percentage = 0 nb_note = 0 for note, weight in config.items(): nb = getattr(self, note, 0) percentage += nb * weight nb_note += nb * max_weight return percentage / nb_note * 100 if nb_note > 0 else 0 def calc_xp(self, config=None): if not config: config = { "miss": 0, "bad": 1, "good": 2, "great": 3, "perfect": 4, "flawless": 5 } xp = 0 for note, weight in config.items(): nb = getattr(self, note, 0) xp += nb * weight return int(xp / len(config)) @staticmethod def encode_stats(raw_data): binary = BinaryStats(nb_notes=len(raw_data), stats=[]) for stats in raw_data: binary["stats"].append({ "grade": stats["grade"], "stepid": stats["stepid"], "score": stats["score"], "combo": stats["combo"], "health": stats["health"], "time": stats["time"].seconds }) return binary.payload @staticmethod def decode_stats(binary): return BinaryStats.from_payload(binary)["stats"] @property def stats(self): return self.decode_stats(self.raw_stats) @property def nb_notes(self): return sum(getattr(self, note, 0) for note in self.stepid.values())
def load_long_lat_data(): """Loading lat/long data from a csv file in the repo""" with gzip.open(os.path.join(DATA_FOLDER, 'san_francisco.csv.gz')) as f: pdf = pd.read_csv(f, encoding="utf-8") pdf['date'] = datetime.datetime.now().date() pdf['occupancy'] = [random.randint(1, 6) for _ in range(len(pdf))] pdf['radius_miles'] = [random.uniform(1, 3) for _ in range(len(pdf))] pdf.to_sql( # pylint: disable=no-member 'long_lat', db.engine, if_exists='replace', chunksize=500, dtype={ 'longitude': Float(), 'latitude': Float(), 'number': Float(), 'street': String(100), 'unit': String(10), 'city': String(50), 'district': String(50), 'region': String(50), 'postcode': Float(), 'id': String(100), 'date': Date(), 'occupancy': Float(), 'radius_miles': Float(), }, index=False) print("Done loading table!") print("-" * 80) print("Creating table reference") obj = db.session.query(TBL).filter_by(table_name='long_lat').first() if not obj: obj = TBL(table_name='long_lat') obj.main_dttm_col = 'date' obj.database = get_or_create_main_db() db.session.merge(obj) db.session.commit() obj.fetch_metadata() tbl = obj slice_data = { "granularity": "day", "since": "2014-01-01", "until": "now", "where": "", "viz_type": "mapbox", "all_columns_x": "LON", "all_columns_y": "LAT", "mapbox_style": "mapbox://styles/mapbox/light-v9", "all_columns": ["occupancy"], "row_limit": 500000, } print("Creating a slice") slc = Slice( slice_name="Mapbox Long/Lat", viz_type='mapbox', datasource_type='table', datasource_id=tbl.id, params=get_slice_json(slice_data), ) misc_dash_slices.append(slc.slice_name) merge_slice(slc)
dt = datetime.strptime(s.strip(), DATEFMT) except ValueError: # Antelope convention dt = UTCDateTime(float(s)).datetime return dt algorithm = Column(String(15), info={ 'default': '-', 'parse': parse_str, 'dtype': 'a15', 'width': 15, 'format': '15.15s' }) amp = Column(Float(24), info={ 'default': -1.0, 'parse': parse_float, 'dtype': 'float', 'width': 11, 'format': '11.2f' }) ampid = Column(Integer, nullable=False, info={ 'default': -1, 'parse': parse_int, 'dtype': 'int', 'width': 9, 'format': '9d'
def __init__(self, name, db, db_schema=None, description=None, generate_days=10, drop_existing=False): # constants constants = [] physical_name = name.lower() # granularities granularities = [] # columns columns = [] columns.append(Column('TURBINE_ID', String(50))) columns.append(Column('TEMPERATURE', Float())) columns.append(Column('PRESSURE', Float())) columns.append(Column('STEP', Float())) # dimension columns dimension_columns = [] dimension_columns.append(Column('CLIENT', String(50))) dimension_columns.append(Column('ORGANIZATION', String(50))) dimension_columns.append(Column('FUNCTION', String(50))) # functions functions = [] # simulation settings sim = { 'freq': '5min', 'auto_entity_count': 10, 'data_item_mean': { 'TEMPERATURE': 22, 'STEP': 1, 'PRESSURE': 50, 'TURBINE_ID': 1 }, 'data_item_domain': { 'SITE': ['Riverside MFG', 'Collonade MFG', 'Mariners Way MFG'], 'ORGANIZATION': [ 'Engineering', 'Supply Chain', 'Production', 'Quality', 'Other' ], 'FUNCTION': [ 'New Products', 'Packaging', 'Planning', 'Warehouse', 'Logistics', 'Customer Service', 'Line 1', 'Line 2', 'Quality Control', 'Calibration', 'Reliability' ] }, 'drop_existing': False } generator = bif.EntityDataGenerator(ids=None, parameters=sim) functions.append(generator) # data type for operator cannot be inferred automatically # state it explicitly output_items_extended_metadata = {} super().__init__( name=name, db=db, constants=constants, granularities=granularities, columns=columns, functions=functions, dimension_columns=dimension_columns, output_items_extended_metadata=output_items_extended_metadata, generate_days=generate_days, drop_existing=drop_existing, description=description, db_schema=db_schema)
class Measurement(Base, StatusMixin): value = Column(Float(32)) process_info_id = Column(Integer, ForeignKey('status_processinfo.id')) pub_date = Column(DateTime, default=func.now())
from flask_sqlalchemy import SQLAlchemy from sqlalchemy import Table, Column, Float, Integer, String, Text, DateTime from services.database import Base, db_session, metadata import datetime from sqlalchemy.orm import mapper class ItemPrice(object): query = db_session.query_property() def __init__(self, item_type, category, subcategory, price): self.item_type = item_type self.item_category = category self.item_subcategory = subcategory self.price = price item_prices = Table('item_prices', metadata, Column('id', Integer(), primary_key=True), Column('item_type', Integer()), Column('item_category', Integer()), Column('item_subcategory', Integer()), Column('price', Float(), nullable=True), extend_existing=True) mapper(ItemPrice, item_prices)
class GeekTimeCoin(DBORM): __tablename__ = 'geektimecoin' coin_id = Column(Integer(), primary_key=True, autoincrement=True) coin_user_id = Column(Integer(), ForeignKey('geektimeuser.user_id')) total_coin = Column(Float(), default=0)
class Player(Base): __tablename__ = "player" player_id = Column(Integer, primary_key=True, autoincrement=True) height = Column(Float(3, 2))
class TextInt(TypeDecorator): '''Modify Text type for integers''' impl = Text def process_bind_param(self, value, dialect): return str(value) def process_result_value(self, value, dialect): return int(value) TINY_TYPE = SmallInteger MEDIUM_TYPE = Integer UNSIGNED_HUGE_TYPE = TextInt HUGE_TYPE = Integer PRIMARY_HUGE_TYPE = HUGE_TYPE FLOAT_TYPE = Float(asdecimal=False) class Team(Enum): none = 0 mystic = 1 valor = 2 instict = 3 def combine_key(sighting): return sighting['encounter_id'], sighting['spawn_id'] class SightingCache: """Simple cache for storing actual sightings
def test_compare_float_str(self): self._compare_default_roundtrip( Float(), "5.2", )
entity type, as well as the functions , constants and granularities that apply to it. The "widgets" entity type below has 3 input data items. Dataitems are denoted by the SqlAlchemy column objects company_code, temp and pressure. It also has a function EntityDataGenerator The keyword args dict specifies extra properties. The database schema is only needed if you are not using the default schema. You can also rename the timestamp. ''' entity_name = 'pump1' db_schema = None # replace if you are not using the default schema db.drop_table(entity_name, schema=db_schema) entity = EntityType( entity_name, db, Column('site', String(50)), Column('temp', Float()), Column('pressure', Float()), bif.EntityDataGenerator(ids=['A01', 'A02', 'B01'], data_item='is_generated'), **{ '_timestamp': 'evt_timestamp', '_db_schema': db_schema }) ''' When creating an EntityType object you will need to specify the name of the entity, the database object that will contain entity data After creating an EntityType you will need to register it so that it visible in the UI. To also register the functions and constants associated with the entity type, specify 'publish_kpis' = True. ''' entity.register(raise_error=False)
def test_compare_float_text(self): self._compare_default_roundtrip( Float(), text("5.2"), )
def init_postigis(self): """Creates all the tables and schema. The table schema is based on the [WazeCCPProcessor](github.com/LouisvilleMetro/WazeCCPProcessor) project in order to achieve maximum compatibility. It creates a schema: `waze` and the tables: - jams - irregularities - alerts - roads - alert_types - coordinates - coordinate_type """ if self.force_export: try: self.engine_postgis.execute('DROP SCHEMA waze CASCADE') except: pass try: self.engine_postgis.execute("CREATE SCHEMA waze") #create db except: pass metadata = sa.MetaData(self.engine_postgis) self.tables_postgis = {} metadata = sa.MetaData(self.engine_postgis) self.tables_postgis['alerts'] = sa.Table( 'alerts', metadata, Column( "id", INTEGER, primary_key=True, ), Column("uuid", Text, nullable=False, index=True), Column("pub_millis", BigInteger, nullable=False), Column("pub_utc_date", TIMESTAMP, index=True), Column("road_type", INTEGER, index=True), Column("location", JSON), Column("location_geo", Geometry('POINT')), Column("street", Text), Column("city", Text), Column("country", Text), Column("magvar", INTEGER), Column("reliability", INTEGER, index=True), Column("report_description", Text), Column("report_rating", INTEGER), Column("confidence", INTEGER, index=True), Column("type", Text, index=True), Column("subtype", Text, index=True), Column("report_by_municipality_user", BOOLEAN), Column("thumbs_up", INTEGER, index=True), Column("jam_uuid", Text, index=True), Column("datafile_id", BigInteger, nullable=False, index=True), schema='waze', ) self.tables_postgis['jams'] = sa.Table( 'jams', metadata, Column("id", INTEGER, primary_key=True, nullable=False), Column("uuid", Text, nullable=False, index=True), Column("pub_millis", BigInteger, nullable=False), Column("pub_utc_date", TIMESTAMP, index=True), Column("start_node", Text), Column("end_node", Text), Column("road_type", INTEGER), Column("street", Text, index=True), Column("city", Text), Column("country", Text), Column("delay", INTEGER, index=True), Column("speed", Float, index=True), Column("speed_kmh", Float, index=True), Column("length", INTEGER, index=True), Column("turn_type", Text), Column("level", INTEGER, index=True), Column("blocking_alert_id", Text), Column("line", JSON), Column("line_geo", Geometry('LINESTRING')), Column("type", Text, index=True), Column("turn_line", JSON), Column("turn_line_geo", Geometry('LINESTRING')), Column("datafile_id", BigInteger, nullable=False, index=True), schema='waze', ) self.tables_postgis['irregularities'] = sa.Table( 'irregularities', metadata, Column("id", INTEGER, primary_key=True, nullable=False), Column("uuid", Text, nullable=False, index=True), Column("detection_date_millis", BigInteger, nullable=False), Column("detection_date", Text), Column("detection_utc_date", TIMESTAMP, index=True), Column("update_date_millis", BigInteger, nullable=False), Column("update_date", Text), Column("update_utc_date", TIMESTAMP, index=True), Column("street", Text, index=True), Column("city", Text), Column("country", Text), Column("is_highway", BOOLEAN), Column("speed", Float, index=True), Column("regular_speed", Float, index=True), Column("delay_seconds", INTEGER, index=True), Column("seconds", INTEGER, index=True), Column("length", INTEGER, index=True), Column("trend", INTEGER, index=True), Column("type", Text, index=True), Column("severity", Float, index=True), Column("jam_level", INTEGER, index=True), Column("drivers_count", INTEGER), Column("alerts_count", INTEGER, index=True), Column("n_thumbs_up", INTEGER, index=True), Column("n_comments", INTEGER), Column("n_images", INTEGER), Column("line", JSON), Column("line_geo", Geometry('LINESTRING')), Column("cause_type", Text), Column("start_node", Text), Column("end_node", Text), Column("datafile_id", BigInteger, nullable=False, index=True), schema='waze', ) self.tables_postgis['coordinate_type'] = sa.Table( 'coordinate_type', metadata, Column("id", INTEGER, primary_key=True, nullable=False), Column("type_name", Text, nullable=False), schema='waze', ) self.tables_postgis['coordinates'] = sa.Table( 'coordinates', metadata, Column("id", VARCHAR(40), primary_key=True, nullable=False), Column("latitude", Float(8), nullable=False), Column("longitude", Float(8), nullable=False), Column("order", INTEGER, nullable=False), Column("jam_id", INTEGER, ForeignKey('waze.jams.id')), Column("irregularity_id", INTEGER, ForeignKey('waze.irregularities.id')), Column("alert_id", INTEGER, ForeignKey('waze.alerts.id')), Column("coordinate_type_id", INTEGER, ForeignKey('waze.coordinate_type.id')), schema='waze', ) self.tables_postgis['roads'] = sa.Table( 'roads', metadata, Column("id", INTEGER, primary_key=True, nullable=False), Column("value", INTEGER, nullable=False), Column("name", VARCHAR(100), nullable=False), schema='waze', ) self.tables_postgis['alert_types'] = sa.Table( 'alert_types', metadata, Column("id", INTEGER, primary_key=True, nullable=False), Column("type", Text, nullable=False), Column("subtype", Text), schema='waze', ) metadata.create_all(self.engine_postgis) try: self.engine_postgis.execute("""ALTER TABLE waze.roads ADD CONSTRAINT roads_unique_combo UNIQUE(value, name);""") except sa.exc.ProgrammingError: pass try: self.engine_postgis.execute("""ALTER TABLE waze.alert_types ADD CONSTRAINT alert_types_unique_combo UNIQUE(type, subtype);""") except sa.exc.ProgrammingError: pass # Insert elements with self.engine_postgis.connect() as conn: try: conn.execute(self.tables_postgis['coordinate_type'].insert(), [{ 'id': 1, 'type_name': 'Line' }, { 'id': 2, 'type_name': 'Turn Line' }, { 'id': 3, 'type_name': 'Location' }]) except sa.exc.IntegrityError: pass try: conn.execute(self.tables_postgis['roads'].insert(), [{ 'value': 1, 'name': 'Streets' }, { 'value': 2, 'name': 'Primary Street' }, { 'value': 3, 'name': 'Freeways' }, { 'value': 4, 'name': 'Ramps' }, { 'value': 5, 'name': 'Trails' }, { 'value': 6, 'name': 'Primary' }, { 'value': 7, 'name': 'Secondary' }, { 'value': 8, 'name': '4X4 Trails' }, { 'value': 9, 'name': 'Walkway' }, { 'value': 10, 'name': 'Pedestrian' }, { 'value': 11, 'name': 'Exit' }, { 'value': 12, 'name': '?' }, { 'value': 13, 'name': '?' }, { 'value': 14, 'name': '4X4 Trails' }, { 'value': 15, 'name': 'Ferry crossing' }, { 'value': 16, 'name': 'Stairway' }, { 'value': 17, 'name': 'Private road' }, { 'value': 18, 'name': 'Railroads' }, { 'value': 19, 'name': 'Runway/Taxiway' }, { 'value': 20, 'name': 'Parking lot road' }, { 'value': 21, 'name': 'Service road' }]) except sa.exc.IntegrityError: pass try: conn.execute(self.tables_postgis['alert_types'].insert(), [ { 'type': 'ACCIDENT', 'subtype': 'ACCIDENT_MINOR' }, { 'type': 'ACCIDENT', 'subtype': 'ACCIDENT_MAJOR' }, { 'type': 'ACCIDENT', 'subtype': 'NO_SUBTYPE' }, { 'type': 'JAM', 'subtype': 'JAM_MODERATE_TRAFFIC' }, { 'type': 'JAM', 'subtype': 'JAM_HEAVY_TRAFFIC' }, { 'type': 'JAM', 'subtype': 'JAM_STAND_STILL_TRAFFIC' }, { 'type': 'JAM', 'subtype': 'JAM_LIGHT_TRAFFIC' }, { 'type': 'JAM', 'subtype': 'NO_SUBTYPE' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_SHOULDER' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_OBJECT' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_POT_HOLE' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_ROAD_KILL' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_SHOULDER_CAR_STOPPED' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_SHOULDER_ANIMALS' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_SHOULDER_MISSING_SIGN' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_FOG' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_HAIL' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_HEAVY_RAIN' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_HEAVY_SNOW' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_FLOOD' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_MONSOON' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_TORNADO' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_HEAT_WAVE' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_HURRICANE' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_WEATHER_FREEZING_RAIN' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_LANE_CLOSED' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_OIL' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_ICE' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_CONSTRUCTION' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_CAR_STOPPED' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'HAZARD_ON_ROAD_TRAFFIC_LIGHT_FAULT' }, { 'type': 'WEATHERHAZARD/HAZARD', 'subtype': 'NO_SUBTYPE' }, { 'type': 'MISC', 'subtype': 'NO_SUBTYPE' }, { 'type': 'CONSTRUCTION', 'subtype': 'NO_SUBTYPE' }, { 'type': 'ROAD_CLOSED', 'subtype': 'ROAD_CLOSED_HAZARD' }, { 'type': 'ROAD_CLOSED', 'subtype': 'ROAD_CLOSED_CONSTRUCTION' }, { 'type': 'ROAD_CLOSED', 'subtype': 'ROAD_CLOSED_EVENT' }, ]) except sa.exc.IntegrityError: pass
def get_table_object(self,tablename,engine): metadata = MetaData(bind=engine) rs_tab_monitor = Table(tablename, metadata, Column('id', Integer(), primary_key=True), Column('schemaname', String(255), nullable=False), Column('tablename', String(255), nullable=False), Column('pct_mem_used', Float()), Column('unsorted_rows', Float()), Column('statistics', Float()), Column('is_encoded', String(255)), Column('diststyle', String(255)), Column('sortkey1', String(255)), Column('skew_sortkey1', String(255)), Column('skew_rows', Float()), Column('m1_num_scan', Float()), Column('m1_row_scan', Float()), Column('m1_avg_time', Float()), Column('w1_num_scan', Float()), Column('w1_row_scan', Float()), Column('w1_avg_time', Float()), Column('d1_num_scan', Float()), Column('d1_row_scan', Float()), Column('d1_avg_time', Float()), Column('h6_num_scan', Float()), Column('h6_row_scan', Float()), Column('h6_avg_time', Float()), Column('h3_num_scan', Float()), Column('h3_row_scan', Float()), Column('h3_avg_time', Float()), Column('last_modified_on', DateTime(), default=datetime.datetime.utcnow()+ist_delta)) rs_tab_monitor.create(checkfirst=True) #clear_mappers() mapper(RSTableMonitor, rs_tab_monitor) return RSTableMonitor