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
