class Topic(Base): # 表的名字 __tablename__ = 'topic' # 表的结构 topicId = Column(Integer(), primary_key=True, autoincrement=True) content = Column(TEXT()) topicTime = Column(DATETIME()) userId = Column(Integer()) lastTime = Column(DATETIME()) picture1 = Column(String()) picture2 = Column(String()) picture3 = Column(String()) picture4 = Column(String()) picture5 = Column(String()) likes = Column(Integer(), default=0) replyNum = Column(Integer(), default=0) comNum = Column(Integer(), default=0) def __repr__(self): return "<Topic(topicId='%s', content='%s', topicTime='%s', userId='%s', lastTime='%s', picture1='%s', picture2='%s', picture3='%s', picture4='%s', picture5='%s', likes='%s', replyNum='%s', comNum='%s')>" % ( self.topicId, self.content, self.topicTime, self.userId, self.lastTime, self.picture1, self.picture2, self.picture3, self.picture4, self.picture5, self.likes, self.replyNum, self.comNum )
class OrmAnnotation(BaseOrmClass): __tablename__ = TABLE_ANNOTATIONS idk = Column(String(255, collation="NOCASE"), primary_key=True) kind = Column(String(255, collation="NOCASE")) text = Column(String(255, collation="NOCASE")) auto_text = Column(String(255, collation="NOCASE")) last_access = Column(DATETIME()) first_access = Column(DATETIME()) def __init__(self, idk, kind, text, auto_text): self.idk = idk self.kind = kind self.text = text self.auto_text = auto_text self.last_access = dt.now() self.first_access = dt.now() def __repr__(self): # Serialization return f"{self.idk}_{self.kind}_{self.text}_{self.auto_text}" def __str__(self): # Human readable return (f"[id:{self.idk}]" f"[kind:{self.kind}]" f"[text:{self.text}]" f"[auto_text:{self.auto_text}]") def __eq__(self, other): return (self.idk == other.idk) and (self.kind == other.kind) def __lt__(self, other): return self.idk < other.idk
class User(Base): # 表的名字 __tablename__ = 'users' # 表的结构 userId = Column(Integer(), primary_key=True, autoincrement=True) tel = Column(String(30)) password = Column(String()) username = Column(String(30)) gender = Column(String(2)) age = Column(Integer()) signTime = Column(DATETIME()) height = Column(DECIMAL()) weight = Column(DECIMAL()) area = Column(String()) job = Column(String()) integral = Column(Integer()) iconUrl = Column(String()) checkTime = Column(DATETIME()) def __repr__(self): return "<User(userId='%s', tel='%s', password='******', username='******' gender='%s' age='%s' signTime='%s height='%s', weight='%s', area='%s', job='%s', integral='%s'), iconUrl='%s', checkTime='%s'>" % ( self.userId, self.tel, self.password, self.username, self.gender, self.age, self.signTime, self.height, self.weight, self.area, self.job, self.integral, self.iconUrl, self.checkTime)
class Datasource(Base): __tablename__ = 'datasources' datasource_id = Column(String(25), primary_key=True) datasource_name = Column(String(100)) created_on = Column(DATETIME(timezone=True), server_default=func.now()) updated_on = Column(DATETIME(timezone=True), server_default=func.now(), onupdate=func.now()) def __repr__(self): return "Datasource(id='%s', name='%s')" % (self.datasource_id, self.datasource_name)
class Instance(Base): __tablename__ = 'instances' instance_id = Column(Integer, primary_key=True) device_datasource_id = Column(Integer) created_on = Column(DATETIME(timezone=True), server_default=func.now()) updated_on = Column(DATETIME(timezone=True), server_default=func.now(), onupdate=func.now()) def __repr__(self): return "Instance(instance_id='%d', device_datasource_id='%d')" % (self.instance_id,self.device_datasource_id)
class SysType(DB.base): __tablename__ = 'systypes' ID = Column('ID', Integer, primary_key=True, autoincrement=True, nullable=False) ParentID = Column('ParentID', Integer, index=True, autoincrement=False, nullable=False, default=-1) Name = Column('Name', String(255), index=True, nullable=False) Frozen = Column('Frozen', Boolean, index=True, nullable=False, default=False) SysStates = relationship('SysState', secondary='association', cascade='all, delete-orphan') CreateTime = Column('CreateTime', DATETIME(fsp=6), index=True, nullable=False, default=datetime.now()) ModifyTime = Column('ModifyTime', DATETIME(fsp=6), index=True, nullable=False, default=datetime.now(), onupdate=datetime.now()) Description = Column('Description', String(2000), nullable=True) UniqueConstraint('ParentID', 'Name', name='uix_SysTypes_ParentID_Name') def __init__(self, current_id=None, parent_id=-1, name=None, create_time=None, modify_time=None, frozen=False, description=None): super().__init__(current_id, create_time, modify_time, description) self.ParentID = parent_id self.Name = name self.Frozen = frozen def __repr__(self): return 'ID = {}, ParentID = {}, Name = {}, Frozen = {}, CreateTime = {}, ModifyTime = {}, Description = {}'.\ format(self.ID, self.ParentID, self.Name, '是' if self.Frozen else '否', self.CreateTime, self.ModifyTime, self.Description)
class Score(Base): """Create a data model for the scores table """ __tablename__ = 'scores' pred_id = Column(String(24), primary_key=True) event_id = Column(String(12), unique=False, nullable=False) startDate = Column(DATETIME(), unique=False, nullable=False) predictionDate = Column(DATETIME(), unique=False, nullable=False) willSellOut = Column(Boolean(), unique=False, nullable=False) confidence = Column(DECIMAL(), unique=False, nullable=False) howFarOut = Column(DECIMAL(), unique=False, nullable=False) def __repr__(self): return '<Score %r>' % self.id
class Stats(DeclarativeBase): """stats model""" __tablename__ = 'stats' #column definitions id = Column(u'id', BIGINT(), primary_key=True, nullable=False) lbid = Column(u'lbid', BIGINT(), ForeignKey('loadbalancers.id'), primary_key=True, nullable=False) period_start = Column(u'period_start', DATETIME(), nullable=False) period_end = Column(u'period_end', DATETIME(), nullable=False) bytes_out = Column(u'bytes_out', BIGINT(), nullable=False) status = Column(u'status', VARCHAR(length=50), nullable=False)
class Headers(Base): __tablename__ = 'proactive_monitoring_header' cycle_id = Column(VARCHAR(64), nullable=False, primary_key=True) line = Column(VARCHAR(32), nullable=False) asset = Column(VARCHAR(32), nullable=False) test_type = Column(VARCHAR(32), nullable=False) begin = Column(DATETIME(), nullable=False) end = Column(DATETIME(), nullable=False) duration = Column(INTEGER, nullable=False) missing_metrics = Column(Boolean(), nullable=False) def __repr__(self): return f"<Headers(cycle_id='{self.cycle_id}', line='{self.line}', asset='{self.asset}', test_type='{self.test_type}', begin='{self.begin}', end='{self.end}'', duration={self.duration}, missing_metrics={self.missing_metrics})>" # noqa
def user_schema(metadata): print("under user_schema") customer = Table('user', metadata, Column('userr_id', BIGINT, primary_key=True, nullable=False), Column('email', VARCHAR( length=255), nullable=False, unique=True), Column('password', VARCHAR( length=255), nullable=False, unique=False), Column('created_at', DATETIME(), nullable=False, default=func.utc_timestamp()), Column('updated_at', DATETIME(), nullable=False, default=func.utc_timestamp()) ) return customer
def sp_schema(metadata): print("under user_schema") sp = Table('sp_devices', metadata, Column('id', VARCHAR(length=11), primary_key = True), Column('deviceid', VARCHAR(length=100)), Column('publickey', VARCHAR(length=500)), Column('created_at', DATETIME(), nullable=False, default=func.utc_timestamp()), Column('updated_at', DATETIME(), nullable=False, default=func.utc_timestamp()), Column('userid',VARCHAR(length=100)), Column('devicetype',VARCHAR(length=100)), ) return sp
class Express(base): __tablename__ = 'express' express_number = Column(String(64), nullable=False) # 电话号码为主键 phone_number = Column(String(64), primary_key=True, nullable=False) name = Column(String(64)) time = Column(DATETIME(), primary_key=True, default=datetime.datetime.now)
class Feature(Base): """Create a data model for the events table """ __tablename__ = 'features' id = Column(String(12), primary_key=True) startDate = Column(DATETIME(), unique=False, nullable=False) categoryId = Column(Integer(), unique=False, nullable=False) formatId = Column(Integer(), unique=False, nullable=False) inventoryType = Column(String(30), unique=False, nullable=False) isFree = Column(Boolean(), unique=False, nullable=False) isReservedSeating = Column(Boolean(), unique=False, nullable=False) minPrice = Column(DECIMAL(), unique=False, nullable=False) maxPrice = Column(DECIMAL(), unique=False, nullable=False) venueName_simple = Column(String(255), unique=False, nullable=False) onSaleWindow = Column(Integer(), unique=False, nullable=False) eventWeekday = Column(Integer(), unique=False, nullable=False) startHour = Column(Integer(), unique=False, nullable=False) capacity = Column(Integer(), unique=False, nullable=False) locale = Column(String(10), unique=False, nullable=False) ageRestriction = Column(String(30), unique=False, nullable=False) presentedBy_simple = Column(String(30), unique=False, nullable=False) isSoldOut = Column(Boolean(), unique=False, nullable=False) soldOutLead = Column(Integer(), unique=False, nullable=False) def __repr__(self): return '<Feature %r>' % self.id
class SportPlan(Base): # 表的名字 __tablename__ = 'sportplan' # 表的结构 userId = Column(Integer(), primary_key=True) sport1 = Column(String()) sport2 = Column(String()) sport3 = Column(String()) sport4 = Column(String()) time1 = Column(String()) time2 = Column(String()) time3 = Column(String()) time4 = Column(String()) week1 = Column(String()) week2 = Column(String()) week3 = Column(String()) week4 = Column(String()) sportTime = Column(DATETIME()) def __repr__(self): return "<SportPlan(userId='%s', sport1='%s', sport2='%s', sport3='%s', sport4='%s', time1='%s', time2='%s', time3='%s', time4='%s', week1='%s', week2='%s', week3='%s',week4='%s', sportTime='%s')>" % ( self.userIdm, self.sport1, self.sport2, self.sport3, self.sport4, self.time1, self.time2, self.time3, self.time4, self.week1, self.week2, self.week3, self.week4, self.sportTime)
class Blog(Base): __tablename__ = 'blog' # PK id = Column(Integer, primary_key=True) # FK user_id = Column(Integer, ForeignKey('user.id')) user = relationship(Users) # FIELDS body = Column(Text(2500), nullable=False) caption = Column(String(100), nullable=False) date_uploaded = Column(DATETIME(), nullable=False) description = Column(String(150), nullable=False) likes = Column(Integer, nullable=False) name = Column(Text(100), nullable=False) image_url = Column(String(250), nullable=False) is_public = Column(Boolean, nullable=False) title = Column(String(100), nullable=False) @property def serialize(self): return { 'blog_body': self.body, 'blog_caption': self.caption, 'date_uploaded': self.date_uploaded, 'blog_description': self.description, 'blog_likes': self.likes, 'blog_name': self.name, 'blog_image_url': self.image_url, 'blog_title': self.title, 'id': self.id, }
class Absagen(Base): __tablename__ = 'absagen' absage_id = Column(Integer, primary_key=True, autoincrement=True) player_id = Column(Integer) training_id = Column(Integer) absagezeitpunkt = Column(DATETIME(timezone=True), server_default=func.now())
def _createMonitorErrorFlagsSchema(schemaName, metadata): schema = Table( schemaName, metadata, # Issue UID Column("uid", mysql.VARCHAR(length=_MAX_MONITOR_ISSUES_ID_LEN), primary_key=True, nullable=False), PrimaryKeyConstraint("uid", name=schemaName + "_pk"), # Issue name Column("name", mysql.VARCHAR(length=_MAX_MONITOR_ISSUES_ID_LEN), nullable=False), # Should report issue flag Column("should_report", BOOLEAN(), nullable=False), # Datetime of last issue occurrence Column("last_occurrence", DATETIME(), nullable=False, server_default=func.current_timestamp()), # Count of issue occurrences Column("occurrence_count", INTEGER, nullable=True, autoincrement=True), mysql_CHARSET=MYSQL_CHARSET, mysql_COLLATE=MYSQL_COLLATE, ) return schema
class CurrencyRate(Base): __tablename__ = "currencyRate" rateID = Column(INTEGER(), primary_key=True, comment="key") sourceCode = Column(String(20), comment="数据源") currencyCode = Column(String(20), comment="币种代码") sellPrice = Column(FLOAT(), comment="售汇价") buyPrice = Column(FLOAT(), comment="结汇价") date = Column(DATETIME(), comment="生效日期") createTime = Column(DATETIME(), comment="创建日期") def __init__(self, souceCode='', currencyCode='', sellPrice=0, buyPrice=0, date=datetime.today()): self.sourceCode = souceCode self.currencyCode = currencyCode self.sellPrice = sellPrice self.buyPrice = buyPrice self.date = date self.createTime = datetime.now() def __str__(self): return "rateID:%s souceCode:%s currencyCode:%s sellPrice:%s buyPrice:%s date:%s createTime:%s" % \ (self.rateID,self.sourceCode,self.currencyCode,\ self.sellPrice,self.buyPrice,self.date,self.createTime) @property def serialize(self): """Return object data in easily serializeable format""" return { 'rateID': self.rateID, 'souceCode': self.sourceCode, 'currencyCode': self.currencyCode, 'sellPrice': self.sellPrice, 'buyPrice': self.buyPrice, 'date': '{}-{}-{}'.format(self.date.year, self.date.month, self.date.day) }
class bilibili_info_base(bilibiliInformation): __tablename__ = "bili_info" id = Column('id', Integer(), autoincrement=True, primary_key=True) title = Column("title", TEXT()) # 标题 aid = Column('aid', Integer(), unique=True) # aid cid = Column("cid", TEXT()) # cid copyright = Column("copyright", TEXT()) # 版权:1、可以正常转载;2、无法转载 tname = Column("tname", TEXT()) # 类别 videos = Column("videos", Integer()) # page数量 ctime = Column("ctime", DATETIME()) # 创建时间 pubdate = Column("pubdate", DATETIME()) # 更新时间 duration = Column("duration", Integer()) # 视频时长 coin = Column("coin", Integer()) # 硬币 favorite = Column("favorite", BigInteger()) # 好评 likes = Column("likes", BigInteger()) # 收藏 archive = Column("archive", TEXT()) # 全部信息 update_time = Column("update_time", DATETIME())
class Message(Base): # 表名 __tablename__ = 'message_board' id = Column(INTEGER(), primary_key=True) content = Column(String(511)) name = Column(String(20)) time = Column(DATETIME()) is_deleted = Column(LargeBinary())
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')
class PathInfo(Base): __tablename__ = 'path_info' ''' CREATE TABLE path_info( id INT PRIMARY KEY auto_increment, path VARCHAR(255) UNIQUE NOT NULL, user VARCHAR(255) NOT NULL, snapshot_count INT NOT NULL default 0, create_date DATETIME NOT NULL DEFAULT current_timestamp, modification_date DATETIME ON UPDATE current_timestamp ) default charset='utf8mb4'; ''' id = Column(INT(), primary_key=True, autoincrement=True) path = Column(VARCHAR(255), nullable=False) user = Column(VARCHAR(255), nullable=False) snapshot_count = Column(INT(), nullable=False, server_default="1") create_date = Column(DATETIME(), server_default=func.now()) modification_date = Column(DATETIME(), server_onupdate=func.now())
def test_add_datetime_server_default_current_timestamp_bundle_onupdate( self): # note SQLAlchemy reflection bundles the ON UPDATE part into the # server default reflection see # https://github.com/sqlalchemy/sqlalchemy/issues/4652 self._run_alter_col( {"type": DATETIME()}, { "server_default": text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") }, )
class SnapshotInfo(Base): __tablename__ = 'snapshot_info' ''' CREATE TABLE snapshot_info ( id INT primary key auto_increment, path VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, create_date DATETIME NOT NULL DEFAULT current_timestamp, modification_date DATETIME ON UPDATE current_timestamp, FOREIGN key(path) references path_info(path) ) DEFAULT charset='utf8mb4'; ''' id = Column(INT(), primary_key=True, autoincrement=True) path = Column(VARCHAR(255), ForeignKey("path_info.path"), nullable=False) name = Column(VARCHAR(255), nullable=False) create_date = Column(DATETIME(), nullable=False, server_default=func.now()) modification_date = Column(DATETIME(), server_onupdate=func.now()) path_info = relationship('PathInfo', backref='my_snapshot')
class Host(Base): __tablename__ = "HAhosts" pxname = Column('HaName', String(40), primary_key=True) svname = Column('ServerName', String(40), primary_key=True) status = Column('Status', String(40)) scur = Column('CurrentConnections', Integer) addr = Column('Address', String(15)) port = Column('Port', Integer) algo = Column('Algoritm', String(20)) created_date = Column('DateTime', DATETIME(timezone=False), default=func.now())
class DeviceDatasource(Base): __tablename__ = 'device_datasources' # id,deviceId,dataSourceId,dataSourceName,dataSourceDescription,monitoringInstanceNumber,instanceNumber device_datasource_id = Column(Integer, primary_key=True) deviceId = Column(Integer) datasource_id = Column(Integer) datasourceName = Column(String(100)) monitoringInstanceNumber = Column(Integer) instanceNumber = Column(Integer) created_on = Column(DATETIME(timezone=True), server_default=func.now()) updated_on = Column(DATETIME(timezone=True), server_default=func.now(), onupdate=func.now()) def __repr__(self): return "DeviceDatasource(device_datasource_id='%d', deviceId='%d',datasource_id='%d', datasourceName='%s'," \ "monitoringInstanceNumber='%d', instanceNumber='%d')" % (self.device_datasource_id, self.deviceId, self.datasource_id, self.datasourceName, self.monitoringInstanceNumber, self.instanceNumber)
class Device(Base): __tablename__ = 'devices' device_id = Column(Integer, primary_key=True) device_name = Column(String(100)) device_ip = Column(String(500)) created_on = Column(DATETIME(timezone=True), server_default=func.now()) updated_on = Column(DATETIME(timezone=True), server_default=func.now(), onupdate=func.now()) def __repr__(self): return "Device(id='%s', name='%s', ip='%s')" % ( self.device_id, self.device_name, self.device_ip) @staticmethod def get_device(session, device_id): return session.query(Device).get(device_id=device_id) @staticmethod def get_all_devices(session): return session.query(Device).all()
class Observations(Base): __tablename__ = 'proactive_monitoring_observations' id = Column(INTEGER, primary_key=True, autoincrement=True) # Auto-increment should be default cycle_id = Column(VARCHAR(64), nullable=False) TimeStamp = Column(DATETIME(), nullable=False) normalized_time = Column(INTEGER(), nullable=False) tag = Column(VARCHAR(length=128), nullable=False) value = Column(FLOAT(precision=12), nullable=False) def __repr__(self): return f"<Observations(cycle_id={self.cycle_id}, TimeStamp={self.TimeStamp}, normalized_time={self.normalized_time}, tag={self.tag}, value={self.value})>" # noqa
class Game(Base): """A table that holds the schedule for each game""" __tablename__ = 'game_schedule' venue_id = Column(Integer(), primary_key=True) stadium = Column(String(50), nullable=False) game_location = Column(String(50), nullable=False) game_date = Column(String(50)) date_updated = Column(DATETIME(), default=datetime.now()) def __str__(self): game_record = 'Game details: venue_id={} stadium={} game_location={} game_date={} date_updated={}' return game_record.format(self.venue_id, self.stadium, self.game_location, self.game_date, self.date_updated)
class Metrics(Base): __tablename__ = 'proactive_monitoring_metrics' id = Column(INTEGER(), primary_key=True, autoincrement=True) # Auto-increment should be default cycle_id = Column(VARCHAR(64), nullable=False) TimeStamp = Column(DATETIME(), nullable=True) normalized_time = Column(INTEGER(), nullable=True) metric_name = Column(VARCHAR(64), nullable=True) value = Column(FLOAT(precision=12), nullable=True) def __repr__(self): return "<Metrics(cycle_id='%s', TimeStamp='%s', normalized_time='%s', " \ "metric_name='%s', value='%s')" % (self.cycle_id, self.TimeStamp, self.normalized_time, self.metric_name, self.value) # noqa