class GameEvent(Base):
__tablename__ = 'game_events'
id = Column(INTEGER, primary_key=True, nullable=False)
event_id = Column(INTEGER, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
event_num = Column(INTEGER, nullable=False)
wctimestring = Column(VARCHAR, nullable=True)
eventmsgtype = Column(INTEGER, nullable=True)
eventmsgactiontype = Column(INTEGER, nullable=True)
period = Column(INTEGER, nullable=True)
pctimestring = Column(VARCHAR, nullable=True)
game = relationship("Game",
foreign_keys=[sport, season, game_id],
back_populates="events")
event_players = relationship(
"GameEventPlayer",
collection_class=attribute_mapped_collection('player_num'),
back_populates="event",
cascade="all, delete, delete-orphan")
__table_args__ = (ForeignKeyConstraint(
[sport, season, game_id],
["games.sport", "games.season", "games.game_id"]),
UniqueConstraint(game_id,
event_num,
name='uix_game_events'),
Index('ix_game_event_ids', event_id),
Index('ix_game_events', game_id, event_num, eventmsgtype,
eventmsgactiontype),
Index('ix_game_events_container', sport, season, game_id,
event_num), {})
class ENR3_3_Signpointtable(Base):
__tablename__ = 'ENR3_3_4Significantpoints'
startend_id = Column(INTEGER, primary_key=True)
Route_Designator = Column(VARCHAR(length=1024))
Significant_point_name = Column(VARCHAR(length=1024))
Latitude = Column(FLOAT)
Longitude = Column(FLOAT)
class Experiment(Base):
__tablename__ = 'experiment'
id = COL(INTEGER, primary_key=True)
# hash = COL(VARCHAR(64))
name = COL(VARCHAR(64))
numPeople = COL(SMALLINT)
timeGroup = COL(TIME())
group = COL(VARCHAR(10))
outlier = COL(VARCHAR(1))
friendship = COL(SMALLINT)
notes = COL(VARCHAR(2048))
status = COL(VARCHAR(20))
def __init__(self, name, numPeople, timeGroup, group, outlier, friendship, notes):
# m = hashlib.sha512()
# m.update(str(id))
# self.hash = m.hexdigest()
self.name = name
self.numPeople = int(numPeople)
self.timeGroup = datetime.datetime.strptime(timeGroup, "%I:%M %p").time()
self.group = group
self.outlier = outlier
self.friendship = friendship
self.notes = notes
self.status = "Created" # or Running or Done
def __repr__(self):
return str(self.id)
class GameStarter(Base):
__tablename__ = 'game_starters'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
team_id = Column(INTEGER, nullable=False)
player_id = Column(INTEGER, nullable=False)
period = Column(INTEGER, nullable=True)
min = Column(VARCHAR, nullable=True)
min = Column(INTEGER, nullable=True)
start_position = Column(VARCHAR, nullable=True)
team = relationship("GameTeam",
foreign_keys=[sport, season, game_id, team_id],
back_populates="period_starters")
__table_args__ = (ForeignKeyConstraint([sport, season, game_id, team_id], [
"game_teams.sport", "game_teams.season", "game_teams.game_id",
"game_teams.team_id"
]),
UniqueConstraint(game_id,
team_id,
player_id,
period,
name='uix_game_starters'),
Index('ix_game_starters', game_id, player_id, period,
start_position),
Index('ix_game_starters_container', sport, season,
game_id, team_id, player_id, period), {})
class User(BaseModel):
__tablename__ = 'user'
id = Column(INTEGER, primary_key=True, autoincrement=True)
username = Column(VARCHAR(50))
mail = Column(VARCHAR(50))
password = Column(VARCHAR(60))
integral = Column(FLOAT, default=100000000)
in_shop = Column(VARCHAR(100), default="")
cookies = Column(INTEGER, default=0)
seckill = Column(INTEGER, default=1)
def check(self, password):
return bcrypt.checkpw(password.encode('utf-8'),
self.password.encode('utf8'))
def __repr__(self):
return '<User: %s>' % self.username
def pay(self, num):
return (self.integral - num) if (self.integral - num) >= 0 else -1
def check_login(self):
return 1 if self.cookies else 0
def __integral__(self):
return self.integral
class PostExperimentData(Base):
__tablename__ = 'post_experiment_data'
id = COL(INTEGER, primary_key=True)
timestamp = COL(DATETIME, default=datetime.datetime.utcnow)
annotationID = COL(INTEGER, sqa.ForeignKey('annotation.id'))
annotation = relationship('Annotation', backref=backref('post_experiment_response', uselist=False))
finalTime = COL(TIME)
rankNum = COL(INTEGER)
rankDenom = COL(INTEGER)
solved = COL(BOOLEAN)
howMuchSolved = COL(VARCHAR(100))
notes = COL(VARCHAR(2048))
def __init__(self, finalTimeMins, finalTimeSecs, rankNum, rankDenom, solved, howMuchSolved, notes, annotation):
self.finalTime = datetime.datetime.strptime(str(finalTimeMins) + " " + str(finalTimeSecs), "%M %S").time()
self.rankNum = rankNum
self.rankDenom = rankDenom
self.solved = True if (solved == "Yes") else False
self.howMuchSolved = howMuchSolved
self.notes = notes
self.annotation = annotation
def __repr__(self):
return str(self.id)
@property
def rank(self):
return round(self.rankNum / float(self.rankDenom), 3)
class OwnCommandsThatDependOn(Base):
__tablename__ = 'own_commands_depend_on'
pk = Column(INTEGER, nullable=False, primary_key=True)
sender_id = Column(VARCHAR(64), default='sender_1', nullable=False)
depends_on_worker_id = Column(VARCHAR(64), nullable=False)
depends_on_cmd_id = Column(VARCHAR(64), nullable=True)
depends_on_result_code = Column(Integer, default=0, nullable=False)
run_cmd_id = Column(VARCHAR(64), nullable=True)
created_date = Column(DATETIME,
default=datetime.datetime.now,
nullable=False)
status = Column(VARCHAR(10), default='Waiting', nullable=False)
"""
Possible status values:
Waiting - Default
Fail
OK
"""
"""
Below are the resulted data which when we receive word that the depends_on_cmd_id is executed, after we check the result code received
with what we have on our record is the same, then we execute it.
"""
resulted_result_code = Column(Integer, default=0, nullable=False)
resulted_datetime = Column(DATETIME, nullable=True)
__table_args__ = (UniqueConstraint('sender_id',
'depends_on_worker_id',
'depends_on_cmd_id',
'depends_on_result_code',
'run_cmd_id',
name='own_commands_depend_on_uix_1'), )
class User(BaseModel):
__tablename__ = 'user'
id = Column(INTEGER, primary_key=True, autoincrement=True)
username = Column(VARCHAR(50))
mail = Column(VARCHAR(50))
password = Column(VARCHAR(60))
integral = Column(FLOAT, default=1000)
def check(self, password):
return bcrypt.checkpw(password.encode('utf-8'),
self.password.encode('utf8'))
def __repr__(self):
return '<User: %s>' % self.username
def pay(self, num):
res = (self.integral - num) if (self.integral - num) else False
if res >= 0:
return res
else:
return False
def __integral__(self):
return self.integral
class GameOfficial(Base):
__tablename__ = 'game_officials'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
official_id = Column(INTEGER, nullable=False)
first_name = Column(INTEGER, nullable=False)
last_name = Column(INTEGER, nullable=False)
official_name = Column(INTEGER, nullable=False)
jersey_num = Column(VARCHAR, nullable=False)
game = relationship("Game",
foreign_keys = [sport, season, game_id],
back_populates = "officials")
__table_args__ = (ForeignKeyConstraint([sport, season, game_id],
["games.sport",
"games.season",
"games.game_id"]),
UniqueConstraint(game_id, official_id,
name='uix_game_officials'),
Index('ix_game_official', game_id, official_id),
Index('ix_game_officials_container', sport, season, game_id,
official_id),
{})
class StageSeason(Base):
__tablename__ = 'seasons'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
league_id = Column(VARCHAR(2), nullable=False)
season = Column(INTEGER, nullable=False)
date_from = Column(DATE, nullable=False)
date_to = Column(DATE, nullable=False)
season_type = Column(VARCHAR(15), nullable=False)
status_code = Column(INTEGER, nullable=False)
load_date = Column(DATETIME, nullable=False)
status_reason = Column(VARCHAR(12), nullable=True)
url = Column(VARCHAR(70), nullable=False)
json = Column(JSON, nullable=True)
processed = Column(BOOLEAN, nullable=True)
processed_date = Column(DATETIME, nullable=True)
__table_args__ = (UniqueConstraint(sport,
league_id,
season,
season_type,
url,
status_code,
load_date,
name='uix_seasons'), {})
class ENR3_ATS_ROUTES_table(Base):
__tablename__ = 'ENR3_ATS_ROUTES'
id = Column(INTEGER, primary_key=True)
Route_Designator = Column(VARCHAR(length=1024))
Significant_point_name = Column(VARCHAR(length=1024))
Latitude = Column(FLOAT)
Longitude = Column(FLOAT)
class StagePeriodStarters(Base):
__tablename__ = 'period_starter_records'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
league_id = Column(VARCHAR(2), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
period = Column(INTEGER, nullable=False)
status_code = Column(INTEGER, nullable=True)
load_date = Column(DATETIME, nullable=False)
status_reason = Column(VARCHAR(12), nullable=True)
url = Column(VARCHAR(70), nullable=False)
json = Column(JSON, nullable=True)
processed = Column(BOOLEAN, nullable=True)
processed_date = Column(DATETIME, nullable=True)
__table_args__ = (UniqueConstraint(sport,
league_id,
season,
game_id,
period,
url,
status_code,
load_date,
name='uix_period_starter_records'), {})
class GameTeam(Base):
__tablename__ = 'game_teams'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
team_id = Column(INTEGER, nullable=False)
team_nickname = Column(VARCHAR, nullable=True)
team_abbrev = Column(VARCHAR, nullable=True)
home_away = Column(BOOLEAN, nullable=False)
game = relationship("Game",
foreign_keys = [sport, season, game_id],
back_populates = "teams")
players = relationship("GamePlayer",
collection_class=attribute_mapped_collection('player_id'),
back_populates="team",
cascade= "all, delete, delete-orphan")
period_starters = relationship("GameStarter",
back_populates="team",
cascade= "all, delete, delete-orphan")
stats = relationship("TeamBoxScore",
uselist=False,
back_populates = "team")
__table_args__ = (ForeignKeyConstraint([sport, season, game_id],
["games.sport",
"games.season",
"games.game_id"]),
UniqueConstraint(game_id, team_id, home_away,
name='uix_game_teams'),
Index('ix_game_teams', game_id, team_id, home_away),
Index('ix_game_teams_container', sport, season, game_id,
team_id, home_away),
{})
class SrunT(Base):
__tablename__ = 'webuser'
id = Column('id', INTEGER, primary_key=True, autoincrement=True)
usrname = Column('username', VARCHAR(50), nullable=False)
passwd = Column('password', VARCHAR(50), nullable=False)
time = Column('timest', TIMESTAMP(timezone=True), nullable=False, default=func.now())
validated = Column('legal', BOOLEAN, nullable=True)
class ENR4_4table(Base):
__tablename__ = 'ENR4_4'
Id = Column(INTEGER,primary_key=True)
Name_Code_designator = Column(VARCHAR(length=1024))
Longitude = Column(FLOAT)
Latitude = Column(FLOAT)
ATS_route_or_other_route = Column(VARCHAR(length=1024))
Terminal_Area = Column(VARCHAR(length=1024))
class Stock(ModelBase):
"""
股票
"""
code = Column(VARCHAR(50), default="", nullable=False, comment="股票代码")
name = Column(VARCHAR(500), default="", nullable=False, comment="名称")
exchange = Column(VARCHAR(50), default="", nullable=False, comment="交易所")
offering_date = Column(DATE, nullable=False, comment="上市日期")
last_update_date = Column(DATE, nullable=False, comment="最后数据更新日期")
class ENR5_2table(Base):
__tablename__ = 'ENR5_2'
Id = Column(INTEGER, primary_key=True)
Name = Column(VARCHAR(length=1024))
Lateral_Limit_Latitude = Column(FLOAT)
Lateral_Limit_Longitude = Column(FLOAT)
Remark = Column(VARCHAR(length=1024))
Upper_Limi_Lower_Limit = Column(VARCHAR(length=1024))
Remarks_Time_of_Act = Column(VARCHAR(length=1024))
class Admin(BaseModel):
__tablename__ = 'admin'
id = Column(INTEGER, primary_key=True, autoincrement=True)
username = Column(VARCHAR(50))
password = Column(VARCHAR(60))
def __repr__(self):
return '<Admin: %s>' % self.username
class ENR4_5table(Base):
__tablename__ = 'ENR4_5'
Id = Column(INTEGER,primary_key=True)
Name_Ident_coordinates = Column(VARCHAR(length=1024))
Latitude = Column(FLOAT)
Longitude = Column(FLOAT)
Type_and_Intensity = Column(VARCHAR(length=1024))
Characteristics = Column(VARCHAR(length=1024))
Operating_Hours = Column(VARCHAR(length=1024))
Remark = Column(VARCHAR(length=1024))
class Shopcar(BaseModel):
__tablename__ = 'shopcar'
id = Column(INTEGER, primary_key=True, autoincrement=True)
name = Column(VARCHAR(200),
ForeignKey('commoditys.name'),
unique=True,
nullable=False)
desc = Column(VARCHAR(500),
ForeignKey('commoditys.desc'),
default='no description')
amount = Column(INTEGER, default=0)
class Fund(Base):
__tablename__ = 'Fund'
fund_code = Column(VARCHAR(), primary_key=True)
date = Column(DATETIME(timezone=False),
nullable=False,
default=datetime.now(),
primary_key=True)
price = Column(FLOAT, nullable=False)
accumulate = Column(FLOAT, nullable=True)
daily_rate = Column(FLOAT, nullable=True)
purchase_state = Column(VARCHAR(), nullable=True)
ransom_state = Column(VARCHAR(), nullable=True)
dividends = Column(VARCHAR(), nullable=True)
class Commodity(BaseModel):
__tablename__ = 'commoditys'
id = Column(INTEGER, primary_key=True, autoincrement=True)
name = Column(VARCHAR(200), unique=True, nullable=False)
desc = Column(VARCHAR(500), default='no description')
amount = Column(INTEGER, default=10)
price = Column(FLOAT, nullable=False)
def __repr__(self):
return '<commodity: %s="">' % self.name
def __price__(self):
return self.price
class GameShot(Base):
__tablename__ = 'game_shots'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
event_num = Column(INTEGER, nullable=False)
team_id = Column(INTEGER, nullable=False)
player_id = Column(INTEGER, nullable=False)
player_name = Column(VARCHAR, nullable=True)
event_type = Column(VARCHAR, nullable=True)
action_type = Column(VARCHAR, nullable=True)
shot_type = Column(VARCHAR, nullable=True)
shot_zone_basic = Column(VARCHAR, nullable=True)
shot_zone_area = Column(VARCHAR, nullable=True)
shot_zone_range = Column(VARCHAR, nullable=True)
shot_distance = Column(INTEGER, nullable=True)
x = Column(INTEGER, nullable=True)
y = Column(INTEGER, nullable=True)
shot_attempted_flag = Column(BOOLEAN, nullable=True)
shot_made_flag = Column(BOOLEAN, nullable=True)
event_player = relationship("GameEventPlayer",
foreign_keys=[game_id, event_num, player_id],
back_populates="shot_chart")
__table_args__ = (ForeignKeyConstraint(
[sport, season, game_id],
["games.sport", "games.season", "games.game_id"]),
ForeignKeyConstraint([game_id, team_id, player_id], [
"game_players.game_id", "game_players.team_id",
"game_players.player_id"
]),
ForeignKeyConstraint(
[game_id, event_num],
["game_events.game_id", "game_events.event_num"]),
ForeignKeyConstraint([game_id, event_num, player_id], [
"game_event_players.game_id",
"game_event_players.event_num",
"game_event_players.player_id"
]),
UniqueConstraint(game_id,
event_num,
player_id,
name='uix_game_shots'),
Index('ix_game_shots', game_id, event_num, player_id,
event_type, action_type, shot_type,
shot_attempted_flag, shot_made_flag),
Index('ix_game_shots_container', sport, season, game_id,
event_num, player_id), {})
class Category(Base):
__tablename__ = 'category'
idcategory = Column(INTEGER, primary_key=True)
fullname = Column(VARCHAR(100))
clcode = Column(VARCHAR(4))
numcode = Column(INTEGER)
def __init__(self, fullname, clcode, numcode):
self.fullname = fullname
self.clcode = clcode
self.numcode = numcode
def __repr__(self):
return '<Category %r, id:%r >' % (self.fullname,str(self.idcategory))
class Commodity(BaseModel):
__tablename__ = 'commoditys'
id = Column(INTEGER, primary_key=True, autoincrement=True)
price = Column(FLOAT, nullable=False)
english = Column(VARCHAR(200), unique=True, nullable=False)
spanish = Column(VARCHAR(500), default='no description')
german = Column(VARCHAR(500), default='no description')
russian = Column(VARCHAR(500), default='no description')
def __repr__(self):
return '<Commodity: %s>' % self.name
def __price__(self):
return self.price
class GameSequence(Base):
__tablename__ = 'game_sequences'
id = Column(INTEGER, primary_key=True, nullable=False)
sport = Column(VARCHAR(5), nullable=False)
season = Column(INTEGER, nullable=False)
game_id = Column(VARCHAR(10), nullable=False)
period = Column(INTEGER, nullable=True)
sec_remain = Column(INTEGER, nullable=True)
event_num = Column(INTEGER, nullable=True)
model_event_num = Column(INTEGER, nullable=True)
sub_event_num = Column(INTEGER, nullable=True)
eventmsgtype = Column(INTEGER, nullable=True)
home_poss = Column(INTEGER, nullable=True)
away_poss = Column(INTEGER, nullable=True)
home_pts = Column(INTEGER, nullable=True)
away_pts = Column(INTEGER, nullable=True)
neutral_description = Column(VARCHAR, nullable=True)
home_description = Column(VARCHAR, nullable=True)
away_description = Column(VARCHAR, nullable=True)
shot_event = Column(VARCHAR, nullable=True)
shot_type = Column(VARCHAR, nullable=True)
shot_zone_basic = Column(VARCHAR, nullable=True)
shot_zone_area = Column(VARCHAR, nullable=True)
shot_zone_range = Column(VARCHAR, nullable=True)
stat_category = Column(VARCHAR, nullable=True)
action_category = Column(VARCHAR, nullable=True)
action_sub_category = Column(VARCHAR, nullable=True)
person_id = Column(INTEGER, nullable=True)
person_name = Column(VARCHAR, nullable=True)
team_id = Column(INTEGER, nullable=True)
team_nickname = Column(VARCHAR, nullable=True)
opp_team_id = Column(INTEGER, nullable=True)
away_team_id = Column(INTEGER, nullable=True)
home_team_id = Column(INTEGER, nullable=True)
pers_foul = Column(INTEGER, nullable=True)
team_foul = Column(INTEGER, nullable=True)
official = Column(VARCHAR, nullable=True)
__table_args__ = (ForeignKeyConstraint([sport, season, game_id],
["games.sport",
"games.season",
"games.game_id"]),
UniqueConstraint(sport, season, game_id, model_event_num, sub_event_num),
Index('ix_game_seq_players', game_id, person_id),
Index('ix_game_seq_opp', game_id, opp_team_id),
Index('ix_game_seq_team', game_id, team_id),
{})
class StagePlayer(Base):
__tablename__ = 'players'
id = Column(INTEGER, primary_key=True, nullable=False)
player_id = Column(INTEGER, nullable=False)
status_code = Column(INTEGER, nullable=False)
load_date = Column(DATETIME, nullable=False)
status_reason = Column(VARCHAR(12), nullable=True)
url = Column(VARCHAR(70), nullable=False)
json = Column(JSON, nullable=True)
processed = Column(BOOLEAN, nullable=True)
processed_date = Column(DATETIME, nullable=True)
__table_args__ = (UniqueConstraint(player_id, url, status_code, load_date,
name='uix_rosters'),
{})
class AdjustFactor(ModelBase):
"""
每日股票复权因子
"""
code = Column(VARCHAR(50), default="", nullable=False, comment="股票代码")
date = Column(DATE, default="", nullable=False, comment="交易日期")
factor = Column(REAL, default=0, nullable=False, comment="开盘价格")
class Flag(BaseModel):
__tablename__ = 'flag'
id = Column(INTEGER, primary_key=True, autoincrement=True)
flag = Column(VARCHAR(200), unique=True, nullable=True)
roundd = Column(INTEGER, default=0)
def __repr__(self):
return '%s' % self.flag
@staticmethod
def clear():
result = db.query(Flag).filter(
Flag.roundd < getround(time.strftime('%H:%M', time.localtime()))
).delete()
db.commit()
print "[*]delete %d overdue flag" % result
@staticmethod
def getflag():
result = db.query(Flag).filter(Flag.roundd == getround(
time.strftime('%H:%M', time.localtime()))).all()
return result
@staticmethod
def ifexist(text):
result = db.query(Flag).filter(Flag.flag == text).count()
return result
class Task(Base):
__tablename__ = "tbl_task"
id = Column(INTEGER, primary_key=True)
task_show_name = Column(VARCHAR(20))
task_from_address = Column(VARCHAR(100))
task_to_address = Column(VARCHAR(100))
task_subject = Column(VARCHAR(100))
task_body = Column(TEXT)
task_atts = Column(VARCHAR(100))
task_status = Column(VARCHAR(10))
task_msg = Column(VARCHAR(100))
def __to_dict__(self):
return {
"id": self.id,
"task_show_name": self.task_show_name,
"task_from_address": self.task_from_address,
"task_to_address": self.task_to_address,
"task_subject": self.task_subject,
"task_body": self.task_body,
"task_atts": self.task_atts,
"task_status": self.task_status,
"task_msg": self.task_msg
}