class Shelf(Base): """ Creates the table that will contain information for each shelf as a unit """ __tablename__ = 'shelf' id = Column(String(128), primary_key=True, nullable=False, comment='A unique identification for the shelf') ## --- Keys --- ## node_identification_id = Column(String(128), ForeignKey('node.id'), nullable=False) node = relationship('Node', back_populates='shelves') slots = relationship('Slot', back_populates='shelf') ## --- Light times --- ## sunrise_t = Column('sunrise_time', Time(), nullable=False, comment='Time to turn on shelf light.') sunset_t = Column('sunset_time', Time(), nullable=False, comment='Time to turn off shelf light..') ## --- Moisture Threshold --- ## soil_moisture_th = Column( Integer(), nullable=False, comment= 'This threshold will define when the irrigation system should act. ' 'Default value = .')
def get_time_column(*args, default_now=False) -> Column: if default_now: return Column(Time(), default=datetime.now().time(), nullable=False, *args) return Column(Time(), *args)
class OpeningHours(Base): __tablename__ = 'opening_hours' id = Column(Integer(), primary_key=True, autoincrement=True) from_date = Column(Time()) to_date = Column(Time()) saturday = Column(Boolean()) sunday = Column(Boolean()) monday = Column(Boolean()) tuesday = Column(Boolean()) wednesday = Column(Boolean()) thursday = Column(Boolean()) friday = Column(Boolean()) @property def serialize(self): """returns object data in easily serializable format""" return { 'from_date': self.from_date, 'to_date': self.to_date, 'saturday': self.saturday, 'sunday': self.sunday, 'monday': self.monday, 'tuesday': self.tuesday, 'wednesday': self.wednesday, 'thursday': self.thursday, 'friday': self.friday, }
class ScheduledTripFrequency(Base): __tablename__ = "scheduled_trip_frequency" pk = Column(Integer, primary_key=True) trip_pk = Column(Integer, ForeignKey("scheduled_trip.pk"), nullable=False) start_time = Column(Time(timezone=True), nullable=False) end_time = Column(Time(timezone=True), nullable=False) headway = Column(Integer, nullable=False) frequency_based = Column(Boolean, default=True, nullable=False)
class Round(Base): __tablename__ = "round" roundid = Column(String(40), primary_key=True) roundno = Column(Integer, nullable=False) quizid = Column(String(40), ForeignKey("quiz.quizid"), nullable=False) image1id = Column(Integer, ForeignKey("image.id"), nullable=False) image2id = Column(Integer, ForeignKey("image.id"), nullable=False) starttime = Column(DateTime(), nullable=False) preferredchoicetime = Column(Time(), nullable=False) upvotechoicetime = Column(Time(), nullable=False) preferredchoice = Column(Integer, ForeignKey("image.id"), nullable=False) upvotechoice = Column(Integer, ForeignKey("image.id"), nullable=False) correct = Column(Boolean, nullable=False)
class ScheduledTripStopTime(Base): __tablename__ = "scheduled_trip_stop_time" pk = Column(Integer, primary_key=True) trip_pk = Column(Integer, ForeignKey("scheduled_trip.pk"), nullable=False) stop_pk = Column(Integer, ForeignKey("stop.pk"), nullable=False) BoardingPolicy = parse.BoardingPolicy arrival_time = Column(Time(timezone=False)) departure_time = Column(Time(timezone=False)) stop_sequence = Column(Integer, nullable=False) headsign = Column(String) pickup_type = Column( Enum(BoardingPolicy, native_enum=False), nullable=False, default=BoardingPolicy.ALLOWED, ) drop_off_type = Column( Enum(BoardingPolicy, native_enum=False), nullable=False, default=BoardingPolicy.ALLOWED, ) continuous_pickup = Column( Enum(BoardingPolicy, native_enum=False), nullable=False, default=BoardingPolicy.NOT_ALLOWED, ) continuous_drop_off = Column( Enum(BoardingPolicy, native_enum=False), nullable=False, default=BoardingPolicy.NOT_ALLOWED, ) shape_distance_traveled = Column(Float) exact_times = Column(Boolean, nullable=False, default=False) stop = relationship("Stop", back_populates="scheduled_trip_times", cascade="") trip = relationship("ScheduledTrip", back_populates="stop_times", cascade="") __table_args__ = ( UniqueConstraint("trip_pk", "stop_sequence"), Index( "scheduled_trip_stop_time_trip_pk_departure_time_idx", "trip_pk", "departure_time", ), )
class ClassSchedule(Base): __tablename__ = 'class_schedule' id = Column(Integer, primary_key=True) course_id = Column(Integer, ForeignKey('course.id', ondelete='CASCADE')) course = relationship('Courses', back_populates='class_schedule') day = Column(CHAR(1), nullable=False) start_date = Column(Time(), nullable=False) end_date = Column(Time(), nullable=False) classroom = Column(String(5), nullable=False) state = Column(Boolean(), default=True) def __repr__(self): return '{0} - {1}'.format(self.start_date, self.end_date)
def _create_table_in_database(table_name, database): metadata = sqlalchemy.MetaData() table = sqlalchemy.Table( table_name, metadata, sqlalchemy.Column(PRIMARY_KEY, sqlalchemy.Integer, primary_key=True), sqlalchemy.Column(NAME_COLUMN, sqlalchemy.String(20)), sqlalchemy.Column(TEST_DATETIME_TZ, DateTime(timezone=True)), sqlalchemy.Column(TEST_DATETIME, DateTime(timezone=False)), sqlalchemy.Column(TEST_TIME_TZ, Time(timezone=True)), sqlalchemy.Column(TEST_TIME, Time(timezone=False)), sqlalchemy.Column(TEST_DATE, Date())) logger.info('Creating table %s in %s database ...', table_name, database.type) table.create(database.engine) return table
class Work(Base): """ 作品主表 """ __tablename__ = 'work' __table_args__ = { 'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB', } wid = Column(Integer, primary_key=True, doc=u'作品表') uid = Column(Integer, ForeignKey('user.uid')) w_title = Column(Unicode(128), nullable=False, doc=u'作品名') w_short_desc = Column(Unicode(256), doc=u'作品简述') w_long_desc = Column(Unicode(1024), doc=u'作品明细') w_type = Column(Integer, doc=u'作品类型') # TODO: 类型自动划分 w_save_path = Column(String(512), doc=u'存储路径') # 二进制存储 create_time = Column(Time(), default=datetime.now, doc=u'创建时间') def __init__(self, title, w_type, w_save_path, short_desc='', long_desc=''): self.w_title = title self.w_type = w_type self.w_save_path = w_save_path self.w_short_desc = short_desc self.w_long_desc = long_desc
class Source(Base): """ 资源主表 """ __tablename__ = 'source' __table_args__ = { 'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB', } sc_id = Column(Integer, primary_key=True, doc=u'资源id') uid = Column(Integer, ForeignKey('user.uid')) sc_title = Column(Unicode(128), nullable=False, doc=u'资源名') sc_short_desc = Column(Unicode(256), doc=u'简述') sc_long_desc = Column(Unicode(1024), doc=u'明细') sc_type = Column(Integer, doc=u'资源类型') sc_save_path = Column(String(512), doc=u'存储路径') create_time = Column(Time(), default=datetime.now, doc=u'创建时间') def __init__(self, title, sc_type, sc_save_path, short_desc='', long_desc=''): self.sc_title = title self.sc_type = sc_type self.sc_save_path = sc_save_path self.sc_short_desc = short_desc self.sc_long_desc = long_desc
class Article(Base): """ 资源主表 """ __tablename__ = 'article' __table_args__ = { 'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB', } at_id = Column(Integer, primary_key=True, doc=u'文章id') uid = Column(Integer, ForeignKey('user.uid')) at_title = Column(Unicode(128), nullable=False, doc=u'文章名') at_short_desc = Column(Unicode(256), doc=u'简述') at_long_desc = Column(Unicode(1024), doc=u'明细') content = Column(Text, doc=u'文章内容') create_time = Column(Time(), default=datetime.now, doc=u'创建时间') def __init__(self, title, at_type, at_save_path, short_desc='', long_desc=''): self.at_title = title self.at_type = at_type self.at_save_path = at_save_path self.at_short_desc = short_desc self.at_long_desc = long_desc
class loadDataByTime(CommonBase): __tablename__ = "fsch_declare_loadDataByTime" busiunitname = Column(String(length=100), primary_key=True) energy = Column(Integer()) sb_time = Column(Time(), primary_key=True) type = Column(Integer()) __table_args__ = (Index("unique_index", "trade_id", "trade_time", unique=True), {"comment": "复式撮合-申报阶段-购售双方提交的历史记录信息"})
class loadZpData(CommonBase): __tablename__ = "fsch_loadZpData" energy = Column(Integer(), nullable=False, default=1) price = Column(Float(), nullable=False, default=1) time = Column(Time(), nullable=False, default=1, primary_key=True) type = Column(String(10), nullable=False, default=1, primary_key=True) __table_args__ = (Index("unique_index", "trade_id", "trade_time", unique=True), {"comment": "复式撮合-chengjiaojilu"})
class Plage(Base): __tablename__ = 'plage' plage_id = Column(Integer(), primary_key=True) heure = Column(Time(), nullable=False) temperature = Column(Float(), nullable=False) jour_id = Column(ForeignKey('planning_jour.planning_id')) jour = relationship("PlanningJour", back_populates="plages") def __repr__(self): return "<Plage(planning=%s, heure='%s',t=%s)>" % (self.jour, self.heure, self.temperature)
def test_type_reflection(self): metadata = self.metadata # (ask_for, roundtripped_as_if_different) specs = [ (String(), String()), (String(1), String(1)), (String(3), String(3)), (Text(), Text()), (Unicode(), String()), (Unicode(1), String(1)), (Unicode(3), String(3)), (UnicodeText(), Text()), (CHAR(1), ), (CHAR(3), CHAR(3)), (NUMERIC, NUMERIC()), (NUMERIC(10, 2), NUMERIC(10, 2)), (Numeric, NUMERIC()), (Numeric(10, 2), NUMERIC(10, 2)), (DECIMAL, DECIMAL()), (DECIMAL(10, 2), DECIMAL(10, 2)), (INTEGER, INTEGER()), (BIGINT, BIGINT()), (Float, Float()), (NUMERIC(), ), (TIMESTAMP, TIMESTAMP()), (DATETIME, DATETIME()), (DateTime, DateTime()), (DateTime(), ), (DATE, DATE()), (Date, Date()), (TIME, TIME()), (Time, Time()), (BOOLEAN, BOOLEAN()), (Boolean, Boolean()), ] columns = [Column('c%i' % (i + 1), t[0]) for (i, t) in enumerate(specs)] db = testing.db t_table = Table('types', metadata, *columns) metadata.create_all() m2 = MetaData(db) rt = Table('types', m2, autoload=True) try: db.execute('CREATE VIEW types_v AS SELECT * from types') rv = Table('types_v', m2, autoload=True) expected = [len(c) > 1 and c[1] or c[0] for c in specs] for table in rt, rv: for i, reflected in enumerate(table.c): assert isinstance(reflected.type, type(expected[i])), '%d: %r' % (i, type(expected[i])) finally: db.execute('DROP VIEW types_v')
def define_columns(data_dict, class_name): """Dynamically define the class attributes for the ORM Parameters ---------- data_dict : dict A dictionary containing the ORM definitions class_name : str The name of the class/ORM. Returns ------- data_dict : dict A dictionary containing the ORM definitions, now with header definitions added. """ special_keywords = [ 'RULEFILE', 'FWERROR', 'FW2ERROR', 'PROPTTL1', 'TARDESCR', 'QUALCOM2' ] with open( os.path.join( os.path.split(__file__)[0], 'table_definitions', class_name.lower() + '.txt'), 'r') as f: data = f.readlines() keywords = [item.strip().split(', ') for item in data] for keyword in keywords: if keyword[0] in special_keywords: data_dict[keyword[0].lower()] = get_special_column(keyword[0]) elif keyword[1] == 'Integer': data_dict[keyword[0].lower()] = Column(Integer()) elif keyword[1] == 'String': data_dict[keyword[0].lower()] = Column(String(50)) elif keyword[1] == 'Float': data_dict[keyword[0].lower()] = Column(Float(precision=32)) elif keyword[1] == 'Decimal': data_dict[keyword[0].lower()] = Column(Float(precision='13,8')) elif keyword[1] == 'Date': data_dict[keyword[0].lower()] = Column(Date()) elif keyword[1] == 'Time': data_dict[keyword[0].lower()] = Column(Time()) elif keyword[1] == 'DateTime': data_dict[keyword[0].lower()] = Column(DateTime) elif keyword[1] == 'Bool': data_dict[keyword[0].lower()] = Column(Boolean) else: raise ValueError('unrecognized header keyword type: {}:{}'.format( keyword[0], keyword[1])) if 'aperture' in data_dict: data_dict['aperture'] = Column(String(50), index=True) return data_dict
class SingleLocationData(SnowData): """ Base class for points and profiles """ latitude = Column(Float) longitude = Column(Float) northing = Column(Float) easting = Column(Float) elevation = Column(Float) utm_zone = Column(String(10)) geom = Column(Geometry("POINT")) time = Column(Time(timezone=True))
class Event(db.Model): __tablename__ = 'events' event_id = Column(Integer, autoincrement=True, primary_key=True) name = Column(String(255), nullable=False) start_time = Column(Time(), nullable=False) end_time = Column(Time(), nullable=False) day = Column(String(50), nullable=False) event_description = Column(String(500)) section = Column(String(100)) user_id = Column(Integer, ForeignKey('users.user_id')) user = relationship("User", back_populates="events") def __init__(self, name=None, start_time=None, end_time=None, day=None, event_description=None, section=None, user_id=None): self.name = name.title() self.start_time = start_time self.end_time = end_time self.day = day self.event_description = event_description self.section = section self.user_id = user_id events = Table( 'events', MetaData(bind=engine), Column('event_id', Integer, autoincrement=True, primary_key=True, nullable=False), Column('name', String), Column('start_time', Time), Column('end_time', Time), Column('day', String), Column('event_description', String), Column('section', String), Column('user_id', Integer))
class Offer(Base): __tablename__ = 'offers' # Here we define columns for the table person # Notice that each column is also a normal Python instance attribute. id = Column(Integer, primary_key=True) name = Column(String(250), nullable=False) phone_number = Column(String(15), nullable=False) time_start = Column(Time(), nullable=False) time_end = Column(Time(), nullable=False) start_lat = Column(DECIMAL(), nullable=False) start_long = Column(DECIMAL(), nullable=False) end_lat = Column(DECIMAL(), nullable=False) end_long = Column(DECIMAL(), nullable=False) price = Column(DECIMAL(), nullable=True) seats_available = Column(Integer, nullable=False) @hybrid_method def start_distance(self, lat, lng): lat_scale_factor = 0.009032 long_scale_factor = 0.0146867 return (func.pow( ((self.start_lat - lat) / lat_scale_factor), 2) + func.pow( ((self.start_long - lng) / long_scale_factor), 2)) @hybrid_method def end_distance(self, lat, lng): lat_scale_factor = 0.009032 long_scale_factor = 0.0146867 return (func.pow( ((self.end_lat - lat) / lat_scale_factor), 2) + func.pow( ((self.end_long - lng) / long_scale_factor), 2)) def as_dict(self): ret = {c.name: getattr(self, c.name) for c in self.__table__.columns} ret["time_start"] = ret["time_start"].strftime('%H:%M') ret["time_end"] = ret["time_end"].strftime('%H:%M') for key, item in ret.items(): if type(item) == Decimal: ret[key] = float(item) return ret
def get_monitor_columns(data_dict, table_name): """Read in the corresponding table definition text file to generate ``SQLAlchemy`` columns for the table. Parameters ---------- data_dict : dict A dictionary whose keys are column names and whose values are column definitions. table_name : str The name of the database table Returns ------- data_dict : dict An updated ``data_dict`` with the approriate columns for the monitor added. """ # Define column types data_type_dict = { 'integer': Integer(), 'string': String(), 'float': Float(precision=32), 'decimal': Float(precision='13,8'), 'date': Date(), 'time': Time(), 'datetime': DateTime, 'bool': Boolean } # Get the data from the table definition file table_definition_file = os.path.join( os.path.split(__file__)[0], 'monitor_table_definitions', '{}.txt'.format(table_name)) with open(table_definition_file, 'r') as f: data = f.readlines() # Parse out the column names from the data types column_definitions = [item.strip().split(', ') for item in data] for column_definition in column_definitions: column_name = column_definition[0] data_type = column_definition[1] # Create a new column if data_type in list(data_type_dict.keys()): data_dict[column_name.lower()] = Column(data_type_dict[data_type]) else: raise ValueError('Unrecognized column type: {}:{}'.format( column_name, data_type)) return data_dict
class UserSubject(Base): """ 用户-话题-关注表 model """ __tablename__ = 'user_subject_relation' __table_args__ = { 'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB', } uid = Column(Integer, ForeignKey('user.uid'), primary_key=True) sid = Column(Integer, ForeignKey('subject.sid'), primary_key=True) subject = relationship('Subject', backref='sub_attender') create_time = Column(Time(), default=datetime.now, doc=u'关注时间')
class FCRB_Vital_Signs(Base): __tablename__ = 'vital_signs' __table_args__ = {'schema': 'fcrb'} patnr = Column(Integer, primary_key=True) falnr = Column(String, primary_key=True) idvs = Column(String, primary_key=True) vppid = Column(String) dttyp = Column(String) erdat = Column(DateTime(timezone=False)) vptim = Column(Time(timezone=False)) typevs = Column(String) vwert = Column(String) vbem = Column(String)
class MatchInfoD(Base): __tablename__ = 'tbl_matchinfod' matchid = Column(String(50), primary_key=True) match = Column(String(30)) date = Column(Date()) matchTime = Column(Time()) matchtype = Column(String(50)) matchtypename = Column(String(50)) matchzhu = Column(String(50)) matchke = Column(String(50)) matchzhuf = Column(String(50)) matchkef = Column(String(50)) zhuHScore = Column(Integer) keHScore = Column(Integer) zhuScore = Column(Integer) keScore = Column(Integer) mResult = Column(String(10)) status = Column(String(20)) wrate = Column(Float, default=0.0) drate = Column(Float, default=0.0) lrate = Column(Float, default=0.0) minrate = Column(Float, default=0.0) fixScore = Column(Integer) fixResult = Column(String(10)) wrateS = Column(Float, default=0.0) drateS = Column(Float, default=0.0) lrateS = Column(Float, default=0.0) minrateS = Column(Float, default=0.0) singleFlag = Column(String(10)) score = Column(Integer) s0 = Column(Float, default=0.0) s1 = Column(Float, default=0.0) s2 = Column(Float, default=0.0) s3 = Column(Float, default=0.0) s4 = Column(Float, default=0.0) s5 = Column(Float, default=0.0) s6 = Column(Float, default=0.0) s7 = Column(Float, default=0.0) ww = Column(Float, default=0.0) wd = Column(Float, default=0.0) wl = Column(Float, default=0.0) dw = Column(Float, default=0.0) dd = Column(Float, default=0.0) dl = Column(Float, default=0.0) lw = Column(Float, default=0.0) ld = Column(Float, default=0.0) ll = Column(Float, default=0.0) infoUrl = Column(String(200)) zhuRank = Column(Integer, default=7) keRank = Column(Integer, default=7) rankDValue = Column(Integer, default=0)
class Model(Base): __tablename__ = 'article' id = Column(Integer, primary_key=True) string_field = Column(String(32)) unicode_field = Column(Unicode(32)) text_field = Column(Text()) int_field = Column(Integer()) float_field = Column(Float()) bool_field = Column(Boolean()) date_field = Column(Date()) datetime_field = Column(DateTime()) interval_field = Column(Interval()) time_field = Column(Time()) enum_field = Column(Enum(MyEnum))
class Statistics(Base): __tablename__ = 'statistics' id = Column(Integer, primary_key=True) user_name = Column(String(50)) issue_id = Column(Integer()) issue_subject = Column(String()) time_issue = Column(Time()) day = Column(Date()) def __init__(self, user_name, issue_id, issue_subject, time_issue, day): self.user_name = user_name self.issue_id = issue_id self.issue_subject = issue_subject self.time_issue = time_issue self.day = day
class User(Base): __tablename__ = 'users' user_id = Column(Integer, primary_key=True) user_name = Column(String, default='Уважаемый', nullable=False) chat_id = Column(Integer, nullable=False, unique=True) recommendations = Column(Boolean, default=True) recommendations_time = Column(Time(timezone=True), server_default='17:30:00') is_admin = Column(Boolean, default=False) def __init__(self, user_id, user_name, chat_id, recommendations): self.user_id = user_id self.user_name = user_name self.chat_id = chat_id self.recommendations = recommendations
class initGroup_GPJY(Base): __tablename__ = "挂牌交易交易详细信息" NAME = Column(String(length=100)) CJ_ENERGY = Column(Float()) GP_ENERGY = Column(Float()) FZ = Column(Float()) YNS = Column(DATE()) SFM = Column(Time()) CJ_PRICE = Column(Float()) ZPF_ENERGY = Column(Float()) KEY = Column(String(length=100), primary_key=True) ZPF_COUNT = Column(Float()) STATU = Column(Float()) GP_COUNT = Column(Float()) updated= Column(Integer(), default=0)
def define_columns(class_attributes_dict, header_name): """Dynamically define the class attributes for the ORMs. Parameters: class_attributes_dict : dictionary Don't worry about it. header_name : string Rootname of the column definitions text file, located in 'table_definitions' subdirectory. Returns: class_attributes_dict : dictionary Don't worry about it. Outputs: nothing Notes: From pyql.database.databse_interface (fall 2015 commits). """ with open( os.path.join( os.path.split(__file__)[0], 'table_definitions', header_name + '.txt'), 'r') as f: data = f.readlines() header_keyword_list = [item.strip().split(', ') for item in data] for header_keyword in header_keyword_list: if header_keyword[1] == 'Integer': class_attributes_dict[header_keyword[0].lower()] = Column( Integer()) elif header_keyword[1] == 'String': class_attributes_dict[header_keyword[0].lower()] = Column( String(100)) elif header_keyword[1] == 'Float': class_attributes_dict[header_keyword[0].lower()] = Column(Float()) elif header_keyword[1] == 'Date': class_attributes_dict[header_keyword[0].lower()] = Column(Date()) elif header_keyword[1] == 'Time': class_attributes_dict[header_keyword[0].lower()] = Column(Time()) elif header_keyword[1] == 'DateTime': class_attributes_dict[header_keyword[0].lower()] = Column(DateTime) else: raise ValueError( 'header keyword type not recognized: {}:{}'.format( header_keyword[0], header_keyword[1])) return class_attributes_dict
class StatTrace(Base): __tablename__ = 'stattrace' id = Column(Integer(11), primary_key=True, autoincrement=True) date = Column(Date(), default=func.now(), nullable=False) time = Column(Time(), default=func.now(), nullable=False) ip = Column(String(39), default='') urlrequested = Column(Text, default='') agent = Column(Text, default='') referrer = Column(Text, default='') os = Column(String(128), default='') browser = Column(String(128), default='') searchengine = Column(String(128), default='') spider = Column(String(128), default='') feed = Column(String(128), default='') nation = Column(String(16), default='') realpost = Column(Integer(2), default=1)
class Messages(db.Model): __tablename__ = "messages" id = Column("id", primary_key=1) user = Column('user', String(100)) sender = Column('sender', String(14)) receiver = Column('receiver', String(14)) msg = Column('msg', Text()) date = Column('date', Date()) time = Column('time', Time()) def __init__(self, sender, receiver, msg, date, time): self.id = id self.sender = sender self.user = current_user.username self.receiver = receiver self.msg = msg self.date = date self.time = time