class Source_Metric(Base):
__tablename__ = 'source_metric'
__table_args__ = (UniqueConstraint(
'source_id', 'type', 'year',
name='uq_sourcemetric_sourceid_type_year'), )
id = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
source_id = Column(INTEGER(unsigned=True),
ForeignKey('source.id'),
primary_key=True,
nullable=False)
type = Column(VARCHAR(45), nullable=False)
value = Column(DECIMAL(13, 3), nullable=False)
year = Column(YEAR(4), nullable=False)
# Relationships
source = relationship('Source', back_populates='metrics')
def __init__(self, type, value, year):
self.type = type
self.value = value
self.year = year
def __repr__(self):
if self.is_integer():
return f'{self.type} {self.year}: {int(self.value)}'
return f'{self.type} {self.year}: {self.value}'
def is_integer(self):
return float(self.value).is_integer()
class SampleMetadata(Base):
__tablename__ = "SampleMetadata"
ID = Column(Integer, nullable=False, autoincrement=True, primary_key=True)
SampleStrain = Column(String(50))
SampleYear = Column(YEAR(4))
SampleHost = Column(String(30))
SampleSource = Column(String(100))
SampleCountry = Column(String(30))
CountryProvince = Column(String(100))
Latitude = Column(DECIMAL(10, 8))
Longitude = Column(DECIMAL(11, 8))
PubmedID = Column(Integer())
PlasmidID = Column(Integer, ForeignKey("Plasmid.ID"), nullable=False)
MSTID = Column(Integer, ForeignKey("MST.ID"), nullable=False)
TypingID = Column(Integer, ForeignKey("TypingMetadata.ID"), nullable=False)
MLVAID = Column(Integer, ForeignKey("MLVAProfile.ID"), nullable=False)
class isolateSubmission(Base):
__tablename__ = "isolateSubmission"
ID = Column(String(36), nullable=False, primary_key=True)
submissionType = Column(String(10), nullable=False)
SubmissionDate = Column(DateTime(timezone=True), server_default=func.now())
isolateName = Column(String(100), nullable=True)
isolateSource = Column(String(100), nullable=True)
isolateHost = Column(String(100), nullable=True)
placeOfIsolation = Column(String(100), nullable=True)
yearOfIsolation = Column(YEAR(4))
country = Column(String(100), nullable=True)
latitude = Column(DECIMAL(10, 8))
longitude = Column(DECIMAL(11, 8))
pubmedID = Column(String(50), nullable=True)
genomeAccession = Column(String(50), nullable=True)
email = Column(String(255), nullable=True)
submitterName = Column(String(255), nullable=True)
class Film(Base):
__tablename__ = "film"
film_id = Column(SMALLINT(5), primary_key=True)
title = Column(String(255), nullable=False, index=True)
description = Column(Text)
release_year = Column(YEAR(4))
language_id = Column(
ForeignKey("language.language_id",
ondelete="RESTRICT",
onupdate="CASCADE"),
nullable=False,
index=True,
)
original_language_id = Column(
ForeignKey("language.language_id",
ondelete="RESTRICT",
onupdate="CASCADE"),
index=True,
)
rental_duration = Column(TINYINT(3),
nullable=False,
server_default=text("'3'"))
rental_rate = Column(DECIMAL(4, 2),
nullable=False,
server_default=text("'4.99'"))
length = Column(SMALLINT(5))
replacement_cost = Column(DECIMAL(5, 2),
nullable=False,
server_default=text("'19.99'"))
rating = Column(Enum("G", "PG", "PG-13", "R", "NC-17"),
server_default=text("'G'"))
special_features = Column(SET)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
)
language = relationship(
"Language", primaryjoin="Film.language_id == Language.language_id")
original_language = relationship(
"Language",
primaryjoin="Film.original_language_id == Language.language_id")
class Race(Base):
__tablename__ = 'race'
id = sa.Column(sa.Integer, primary_key=True)
election_type = sa.Column(sa.Enum(u'primary', u'general'), nullable=False)
election_year = sa.Column(YEAR(4), nullable=False)
election_date = sa.Column(sa.Integer, nullable=False)
seat_status = sa.Column(sa.Enum(u'filled', u'open seat', u'retired'),
nullable=False)
race_order = sa.Column(sa.Integer,
nullable=False,
index=True,
server_default=sa.text("'0'"))
race_name = sa.Column(sa.String(64), nullable=False, index=True)
race_district = sa.Column(sa.Integer,
nullable=False,
server_default=sa.text("'0'"))
race_description = sa.Column(sa.Text)
num_candidates = sa.Column(sa.Integer,
nullable=False,
server_default=sa.text("'0'"))
parties_short_text = sa.Column(sa.String(16),
nullable=False,
server_default=sa.text("''"))
slug = sa.Column(sa.String(48), nullable=False)
is_statewide = sa.Column(sa.Integer,
nullable=False,
server_default=sa.text("'0'"))
area = sa.Column(sa.String(32),
nullable=False,
server_default=sa.text("''"))
def __repr__(self):
return '<Race election_type, election_year (%r %r)>' % (
self.election_type, self.election_year)
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class DouBanSeriesAwards(Base):
"""影视获奖列表信息"""
__tablename__ = "award"
__table_args__ = {"mysql_engine": "InnoDB"}
id = sqlalchemy.Column(sqlalchemy.BIGINT,
primary_key=True,
comment='豆瓣影视演职人员的 ID',
autoincrement=True)
sid = sqlalchemy.Column(
sqlalchemy.VARCHAR(20), \
sqlalchemy.ForeignKey("series_info.series_id", onupdate="CASCADE", ondelete="RESTRICT"), \
nullable=False, comment='豆瓣影视剧条目 ID'
)
host = sqlalchemy.Column(sqlalchemy.VARCHAR(40,
collation="utf8mb4_general_ci",
convert_unicode=True),
nullable=False,
comment='颁奖主办方')
year = sqlalchemy.Column(YEAR(display_width=4),
default=None,
comment='获奖年份')
name = sqlalchemy.Column(sqlalchemy.VARCHAR(130,
collation="utf8mb4_general_ci",
convert_unicode=True),
nullable=False,
comment='获奖类型名称')
person = sqlalchemy.Column(sqlalchemy.VARCHAR(
60, collation="utf8mb4_general_ci", convert_unicode=True),
default=None,
comment='获奖人姓名')
status = sqlalchemy.Column(TINYINT(1),
nullable=False,
default=1,
comment='最终获奖状态, 1 为获奖，0 表示只有提名')
create_time = sqlalchemy.Column(sqlalchemy.DATETIME,
server_default=func.now(),
comment='首次爬取数据')
update_time = sqlalchemy.Column(
sqlalchemy.DATETIME, server_default=func.now(), onupdate=func.now(), \
comment='更新爬取时间，没有更新的情况和首次爬取时间一致'
)
def __repr__(self):
format = "<%s data model object at %s>"
return format % (self.__class__.__name__, hex(id(self)))
def __str__(self):
format_ = "<{tbname} (%s)>".format(tbname=self.__tablename__)
items = ", ".join(f"{key}='{self.__getattribute__(key)}" \
for key in self.__dir__() if not key.startswith("_"))
return format_ % items
class DouBanSeriesInfo(Base):
"""影视数据表
关于豆瓣影视的列表信息
"""
__tablename__ = "series_info"
__table_args__ = {'mysql_engine': 'InnoDB'}
series_id = sqlalchemy.Column(sqlalchemy.VARCHAR(20),
comment="豆瓣影视 ID",
primary_key=True)
name = sqlalchemy.Column(
sqlalchemy.VARCHAR(length=150, convert_unicode=True, \
collation="utf8mb4_general_ci"), nullable=False, comment="豆瓣影视名称"
)
alias = sqlalchemy.Column(
sqlalchemy.VARCHAR(length=150, convert_unicode=True, \
collation="utf8mb4_general_ci"), comment="豆瓣影视别名"
)
rate = sqlalchemy.Column(sqlalchemy.DECIMAL(2, 1), comment='豆瓣影视评分')
rate_collection = sqlalchemy.Column(INTEGER(unsigned=True),
default=0,
comment='豆瓣影视评论人数')
main_tag = sqlalchemy.Column(
sqlalchemy.VARCHAR(10, convert_unicode=True), \
comment='豆瓣影视主要类型标签 eg:电影、电视剧、综艺、动漫、纪录片以及短片'
)
genres = sqlalchemy.Column(sqlalchemy.VARCHAR(200, convert_unicode=True),
comment='豆瓣影视类型，例如 恐怖、动作等')
product_country = sqlalchemy.Column(sqlalchemy.VARCHAR(
100, convert_unicode=True),
comment='豆瓣影视制片国家')
language = sqlalchemy.Column(sqlalchemy.VARCHAR(100, convert_unicode=True),
comment='豆瓣影视语言')
release_year = sqlalchemy.Column(YEAR(display_width=4), comment='豆瓣影视成片年份')
release_date = sqlalchemy.Column(sqlalchemy.VARCHAR(100),
comment='豆瓣影视上映日期，不同的国家可能日期不同')
play_duration = sqlalchemy.Column(sqlalchemy.VARCHAR(100),
comment='豆瓣影视播放时长，可能不同回家版本存在不同时长')
imdb_id = sqlalchemy.Column(sqlalchemy.VARCHAR(20), comment='IMDB 数据中的 ID')
tags = sqlalchemy.Column(sqlalchemy.VARCHAR(100),
comment='豆瓣影视中豆瓣成员常用标签 实际可能为豆瓣处理得到的结果')
directors = sqlalchemy.Column(
sqlalchemy.VARCHAR(100, convert_unicode=True, collation="utf8mb4_general_ci"), \
comment='豆瓣影视条目中导演，使用 / 分隔'
)
screenwriters = sqlalchemy.Column(sqlalchemy.VARCHAR(
100, collation="utf8mb4_general_ci", convert_unicode=True),
comment='豆瓣影视条目中编剧，使用 / 分隔')
actors = sqlalchemy.Column(sqlalchemy.VARCHAR(
400, collation="utf8mb4_general_ci", convert_unicode=True),
comment='豆瓣影视条目中演员，使用 / 分隔')
plot = sqlalchemy.Column(sqlalchemy.VARCHAR(3000,
collation="utf8mb4_general_ci",
convert_unicode=True),
comment='豆瓣影视条目剧情简介')
cover = sqlalchemy.Column(sqlalchemy.VARCHAR(150), comment='豆瓣影视条目中封面海报链接')
cover_content = sqlalchemy.Column(
sqlalchemy.BLOB,
nullable=True,
comment="豆瓣影视海报链接请求后的 content，避免后续无法请求的情况")
official_site = sqlalchemy.Column(
sqlalchemy.VARCHAR(200, convert_unicode=True), \
comment='影视条目上的官方网站'
)
recommendation_type = sqlalchemy.Column(
sqlalchemy.VARCHAR(20), comment='提取豆瓣推荐的类型，解析的内容页面上提供的"好于"类型的信息')
recommendation_item = sqlalchemy.Column(
sqlalchemy.VARCHAR(300, collation="utf8mb4_general_ci", convert_unicode=True), \
comment="提取豆瓣对当前内容推荐对相似条目"
)
create_time = sqlalchemy.Column(sqlalchemy.DATETIME,
server_default=func.now(),
comment='首次爬取数据')
update_time = sqlalchemy.Column(
sqlalchemy.DATETIME, server_default=func.now(), onupdate=func.now(), \
comment='更新爬取时间，没有更新的情况和首次爬取时间一致'
)
episodes = relationship("DouBanEpisodeInfo",
backref="episode_info",
lazy="joined")
workers = relationship("DouBanSeriesWorker",
backref="worker",
lazy="joined")
picutres = relationship("DouBanSeriesPic",
backref="picture",
lazy="joined")
awards = relationship("DouBanSeriesAwards", backref="award", lazy="joined")
def __repr__(self):
format = "<%s data model object at %s>"
return format % (self.__class__.__name__, hex(id(self)))
def __str__(self):
format_ = "<{tbname} (%s)>".format(tbname=self.__tablename__)
items = ", ".join(f"{key}='{self.__getattribute__(key)}'" \
for key in self.__dir__() if not key.startswith("_"))
return format_ % items
def __eq__(self, other):
"""比较两个对象是不是相等
"""
return self.series_id == other.series_id