class UserVideoInfo(Base):
__tablename__ = 'user_video_info'
id = Column(INTEGER(11),
primary_key=True,
autoincrement=True,
comment='主键ID')
mid = Column(VARCHAR(100), default='', comment='用户ID')
bvid = Column(VARCHAR(100), default='', comment='视频ID')
video_url = Column(VARCHAR(500), default='', comment='视频URL')
video_source_url = Column(VARCHAR(500), default='', comment='视频图片URL')
video_title = Column(VARCHAR(100), default='', comment='标题')
video_reply = Column(VARCHAR(50), default='', comment='评论数')
video_favorite = Column(VARCHAR(50), default='', comment='收藏数')
video_ctime = Column(DATETIME, default=0, comment='发布时间')
def __str__(self):
return str(self.__dict__)
class CustomerOrderDtl(Base):
__tablename__ = 'customer_order_dtls'
order_id = Column(String(45))
user_id = Column(String(45))
product_type = Column(String(45))
prod_sub_type = Column(String(45))
category_type = Column(String(45))
brand_name = Column(String(45))
catalog_number = Column(String(45))
design_no = Column(String(45))
sahde_no = Column(String(45))
photo = Column(String(45))
delivery_date = Column(String(45))
message = Column(String(45))
is_invidulaystiched = Column(String(45))
total_count = Column(String(45))
id = Column(INTEGER(11), primary_key=True)
class CobraProjects(db.Model):
"""
Projects for all
"""
__tablename__ = 'projects'
__table_args__ = ({"mysql_charset": "utf8mb4"})
id = db.Column(INTEGER(unsigned=True), primary_key=True, autoincrement=True, nullable=False)
repository = db.Column(db.String(512), nullable=False, default=None)
url = db.Column(db.String(512), nullable=False, default=None)
name = db.Column(db.String(50), nullable=False, default=None)
author = db.Column(db.String(50), nullable=False, default=None)
framework = db.Column(db.String(32), nullable=False, default=None)
pe = db.Column(db.String(32), nullable=False, default=None)
remark = db.Column(db.String(512), nullable=False, default=None)
status = db.Column(TINYINT, nullable=False, default=1)
last_scan = db.Column(db.DateTime, nullable=False, default=None)
created_at = db.Column(db.DateTime, nullable=False, default=None)
updated_at = db.Column(db.DateTime, nullable=False, default=None)
def __init__(self, repository, url, name, author, framework, pe, remark, status, last_scan, created_at=None, updated_at=None):
self.repository = repository
self.url = url
self.name = name
self.author = author
self.framework = framework
self.pe = pe
self.remark = remark
self.status = status
self.last_scan = last_scan
self.created_at = created_at
self.updated_at = updated_at
current_time = time.strftime('%Y-%m-%d %X', time.localtime())
if created_at is None:
self.created_at = current_time
else:
self.created_at = created_at
if updated_at is None:
self.updated_at = current_time
else:
self.updated_at = updated_at
def __repr__(self):
return "<CobraProjects %r - %r>" % (self.id, self.name)
class Task(Base):
__tablename__ = 'task'
id = Column(
'id',
INTEGER(unsigned=True),
primary_key=True,
autoincrement=True,
)
user_id = Column('user_id', ForeignKey('user.id'))
content = Column('content', String(256))
deadline = Column(
'deadline',
DateTime,
default=None,
nullable=False,
)
date = Column(
'date',
DateTime,
default=datetime.now(),
nullable=False,
server_default=current_timestamp(),
)
done = Column('done', BOOLEAN, default=False, nullable=False)
def __init__(self,
user_id: int,
content: str,
deadline: datetime,
date: datetime = datetime.now()):
self.user_id = user_id
self.content = content
self.deadline = deadline
self.date = date
self.done = False
#デバッグ用
def __str__(self):
return str(self.id) + \
':user_id ->' + str(self.user_id) + \
', content ->' + self.content + \
',deadline ->' + self.deadline.strftime('%Y/%m/%d - %H:%M:%S') + \
', date -> ' + self.date.strftime('%Y/%m/%d - %H:%M:%S') + \
', done -> ' + str(self.done)
class RawFlightLeg(Base, DBModel):
__tablename__ = 'RawFlightLegs'
rawFlightLegsId = Column(String(100), primary_key=True)
legId = Column(String(100))
segmentsIds = Column(Text)
originStation = Column(String(100))
destinationStation = Column(String(100))
departureDatetime = Column(DateTime)
arrivalDatetime = Column(DateTime)
duration = Column(INTEGER(11))
journeyMode = Column(String(100))
stops = Column(Text)
carriers = Column(Text)
operatingCarriers = Column(Text)
directionality = Column(String(100))
flightNumbers = Column(Text)
writeDate = Column(DateTime)
apiCallId = Column(String(100))
def __init__(self, Id, SegmentIds, OriginStation, DestinationStation,
Departure, Arrival, Duration, JourneyMode, Stops, Carriers,
OperatingCarriers, Directionality, FlightNumbers, apiCallId):
# prefix는 table명 첫글자 소문자화
self.prefix = self.__tablename__[0].lower() + self.__tablename__[1:]
self.rawFlightLegsId = self.makeId(self.prefix)
self.legId = str(Id)
self.segmentsIds = str(SegmentIds)
self.originStation = str(OriginStation)
self.destinationStation = str(DestinationStation)
self.departureDatetime = datetime.strptime(Departure,
'%Y-%m-%dT%H:%M:%S')
self.arrivalDatetime = datetime.strptime(Arrival, '%Y-%m-%dT%H:%M:%S')
self.duration = int(Duration)
self.journeyMode = str(JourneyMode)
self.stops = str(Stops)
self.carriers = str(Carriers)
self.operatingCarriers = str(OperatingCarriers)
self.directionality = str(Directionality)
self.flightNumbers = str(FlightNumbers)
self.writeDate = datetime.now()
self.apiCallId = apiCallId
class Industry(Base):
__tablename__ = 'industry'
id = Column(INTEGER(11), primary_key=True)
cat_id = Column(INTEGER(11))
name = Column(String(20), server_default=text("''"))
en_name = Column(String(20))
link = Column(String(200))
cus_list_link = Column(String(200))
all_page_num = Column(INTEGER(11))
current_page_num = Column(INTEGER(11))
created_at = Column(INTEGER(11))
updated_at = Column(INTEGER(11))
class User(Base):
__tablename__ = 'user'
id = Column(String(255), primary_key=True, server_default=text("''"))
name = Column(String(255))
username = Column(String(255), index=True)
password = Column(String(255))
email = Column(String(255))
phone = Column(String(255))
company_id = Column(String(255), index=True)
role_id = Column(String(255), index=True)
created_at = Column(DateTime)
updated_at = Column(DateTime)
_del = Column('del', TINYINT(4), server_default=text("'0'"))
url = Column(String(255))
job = Column(String(255))
platform = Column(INTEGER(11))
area_id = Column(String(255))
# 加密密码
def hash_password(self, password):
self.password = pwd_context.encrypt(password)
# 校验密码
def verify_password(self, password):
return pwd_context.verify(password, self.password)
# 生成token
def generate_auth_token(self, expiration=6000):
s = Serializer(app.config['SECRET_KEY'], expires_in=expiration)
return s.dumps({'id': self.id})
# 验证token
@staticmethod
def verify_auth_token(token):
s = Serializer(app.config['SECRET_KEY'])
try:
data = s.loads(token)
except SignatureExpired:
return None
except BadSignature:
return None
user = User.query.get(data['id'])
return user
class M_Medias(Base):
__tablename__ = 'medias'
media_no = Column(INTEGER, primary_key=True, index=True, doc='歌曲编号')
media_name = Column(VARCHAR(200), nullable=False, doc='歌曲名称')
media_namelen = Column(TINYINT, nullable=False, doc='歌曲名称长度')
media_langtype = Column(TINYINT(4), nullable=True, doc='(歌曲语言类型) 用于显示歌曲信息,字幕时选择字库文件.0: 中文,1韩文,2日文')
media_langid = Column(INTEGER(11), nullable=True, doc='歌曲语言ID')
media_lang = Column(VARCHAR(32), nullable=True, doc='歌曲语言')
media_tag1 = Column(VARCHAR(128), nullable=True, doc='歌曲3D分类')
media_tag2 = Column(VARCHAR(128), nullable=True, doc='歌曲3D分类')
media_actname1 = Column(VARCHAR(128), nullable=True, doc='歌星信息（全部）')
media_actname2 = Column(VARCHAR(128), nullable=True, doc='歌星信息（全部）')
media_actname3 = Column(VARCHAR(128), nullable=True, doc='歌星信息（全部）')
media_actname4 = Column(VARCHAR(128), nullable=True, doc='歌星信息（全部）')
media_carria = Column(VARCHAR(32), nullable=True, doc='歌曲载体类型（DVD，MP3等）')
media_yuan = Column(TINYINT, nullable=False, server_default='1', doc='原唱')
media_ban = Column(TINYINT, nullable=False, server_default='2', doc='伴唱')
media_svrgroup = Column(TINYINT, nullable=False, server_default='1', doc='文件所在服务器组：1-主组，2-从组')
media_file = Column(VARCHAR(256), nullable=True, doc='文件路径（文件名）')
media_style = Column(TINYINT, nullable=True, doc='视频风格类型: 0-MV 1-现场版 2-流水影')
media_audio = Column(VARCHAR(32), nullable=True, doc='音频类型（mpeg）')
media_volume = Column(TINYINT, nullable=True, doc='音量')
media_jp = Column(VARCHAR(32), nullable=True, doc='简拼')
media_py = Column(VARCHAR(256), nullable=True, doc='拼音')
media_strok = Column(TINYINT, nullable=True, doc='笔画数')
media_stroks = Column(VARCHAR(64), nullable=True, doc='笔划序列')
media_lyric = Column(VARCHAR(512), nullable=True, doc='歌词,用于搜吧搜索')
media_isnew = Column(TINYINT, nullable=True, doc='是否新歌')
media_clickm = Column(INTEGER, nullable=False, server_default='0', doc='月点击量')
media_clickw = Column(INTEGER, nullable=False, server_default='0', doc='周点击量')
media_click = Column(INTEGER, nullable=False, server_default='0', doc='总点击量')
media_type = Column(TINYINT, nullable=False, doc='类型，1: 歌曲|2: 广告|3: 电影')
media_stars = Column(TINYINT(4), nullable=False, doc = 'star_level, 歌曲热度设定')
media_actno1 = Column(INTEGER, nullable=True, doc='歌星1 编号')
media_actno2 = Column(INTEGER, nullable=True, doc='歌星2 编号')
media_actno3 = Column(INTEGER, nullable=True, doc='歌星3 编号')
media_actno4 = Column(INTEGER, nullable=True, doc='歌星4 编号')
media_dafen = Column(TINYINT, nullable=False, server_default='0', doc='该歌曲是否支持打分')
media_climax = Column(TINYINT, nullable=False, server_default='0', doc='该歌曲是否有高潮信息')
media_climaxinfo = Column(VARCHAR(256), nullable=True, doc='该歌曲的高潮信息')
media_yinyi = Column(TINYINT, nullable=False, server_default='0', doc='该歌曲是否有音译信息')
media_light = Column(INTEGER, nullable=False, server_default='0', doc='灯光设置')
media_newadd = Column(TINYINT, nullable=False, server_default='0', doc='用于清除空纪录使用')
class ImplantCommands(Base):
__tablename__ = 'implant_commands'
log_id = Column(INTEGER(11), nullable=False, index=True, primary_key=True)
cid = Column(INTEGER(11), nullable=False, index=True)
uid = Column(INTEGER(11), nullable=False, index=True)
uik = Column(INTEGER(11), nullable=False, index=True)
time = Column(INTEGER(11), nullable=False, index=True)
log_entry = Column(TextPickleType(), nullable=False)
read_by_implant = Column(INTEGER(16),
nullable=False,
server_default=text("0"))
c2_protocol = Column(String(128))
command_id = Column(String(128))
class Bucket(Base):
# table name
__tablename__ = 'buckets'
__table_args__ = ({'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8mb4'})
# columns
id = Column('id',
INTEGER(unsigned=True),
primary_key=True,
autoincrement=True)
user_id = Column('user_id', String(16), nullable=False)
name = Column('name', String(32), nullable=False)
region = Column('region',
String(16),
ForeignKey('regions.name'),
nullable=False)
created_at = Column('created_at',
DATETIME(fsp=6),
nullable=False,
server_default=text('CURRENT_TIMESTAMP(6)'))
updated_at = Column(
'updated_at',
DATETIME(fsp=6),
nullable=False,
server_default=text(
'CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)'))
deleted_at = Column('deleted_at', DATETIME(fsp=6), nullable=False)
object = relationship('Object', backref='bucket') # One To Many
UniqueConstraint('user_id',
'name',
'region',
'deleted_at',
name='unq_bucket_user_id_name_region')
def serialize(self):
return {
'id': self.id,
'user_id': self.user_id,
'name': self.name,
'region': self.region,
'created_at': self.created_at,
'updated_at': self.updated_at,
'deleted_at': self.deleted_at
}
class QiInfoMapRule(Base):
__tablename__ = 'qi_info_map_rule'
insert_time = Column(INTEGER(11))
is_deleted = Column(SMALLINT(6),
nullable=False,
server_default=text("'0'"))
id = Column(INTEGER(11), primary_key=True)
template_id = Column(INTEGER(11), nullable=False)
template_name = Column(VARCHAR(50), nullable=False)
rule_type = Column(INTEGER(11), nullable=False)
rule_type_name = Column(VARCHAR(50), nullable=False)
rule_id = Column(INTEGER(11), nullable=False)
rule_name = Column(VARCHAR(50), nullable=False)
rule_score = Column(INTEGER(11), nullable=False)
show_desc = Column(String(255), nullable=False)
warning_id = Column(INTEGER(11), server_default=text("'0'"))
class UserMeal(ModelBase):
"""用户设备信息"""
__tablename__ = 'user_meal'
id = Column(INTEGER(11), primary_key=True)
uid = Column(INTEGER(11))
starttime = Column(DATETIME)
endtime = Column(DATETIME)
utype = Column(INTEGER(11))
addtime = Column(DATETIME)
details = Column(VARCHAR(1024))
state = Column(INTEGER(11))
totals = Column(INTEGER(11))
lasttotal = Column(INTEGER(11))
mealid = Column(INTEGER(11))
tradeno = Column(VARCHAR(64))
class User(DbBase):
__tablename__ = 'user'
attributes = ['id', 'name', 'addresses', 'phonenums']
detail_attributes = attributes
summary_attributes = ['name']
id = Column(INTEGER(11), primary_key=True, autoincrement=True)
name = Column(String(255), nullable=False)
# 只是方便引用,写在那边无所谓,单方引用
addresses = relationship(u'Address', back_populates=u'user', lazy=False, uselist=True, viewonly=True)
phonenums = relationship(u'PhoneNum', back_populates=u'user', lazy=False, uselist=True, viewonly=True)
# lazy=False,一次性加载出来phonenums的值,这样就可以让信息在一次sql查询中加载出来，而不是每次访问外键属性再发起一次查询。问题在于，lazy=False时sql被组合为一个SQL语句，relationship每级嵌套会被展开，实际数据库查询结果将是乘数级
# uselist=False,addresses=add1可以插入,但为True的话,报错:not list-like
# uselist=True, 必须以列表的形式添加: user1.phonenums = [phn1, phn2]
def __repr__(self):
return "<User(id={},name={},addresses={},phonenums={})>".format(self.id,self.name, self.addresses, self.phonenums)
class Ligand(db.Model):
__tablename__ = 'zincLigands'
zincID = Column(INTEGER(unsigned=True),
primary_key=True,
autoincrement=False)
protID = Column(Integer)
#name = Column(String)
smiles = Column(Text)
InChI = Column(Text)
weight = Column(Float)
ph = Column(Integer)
charge = Column(Integer)
logP = Column(Float)
trancheName = Column(String(6))
'''
class RuangPrestasiModel(db.Model):
"""
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| nama | varchar(255) | YES | | NULL | |
| jurusan | varchar(255) | YES | | NULL | |
| angkatan | varchar(255) | YES | | NULL | |
| prestasi | varchar(255) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
"""
__tablename__ = "ruang_prestasi"
id = Column(INTEGER(unsigned=True), primary_key=True)
nama = Column(String(255))
jurusan = Column(String(255))
angkatan = Column(String(255))
prestasi = Column(String(255), nullable=False)
class Transaction(BASE):
__tablename__ = 'transaction'
transaction_time = Column(TIMESTAMP, default=datetime.now(), nullable=False)
item_id = Column(String(100), nullable=False)
customer_id = Column(INTEGER, nullable=False)
quantity = Column(INTEGER(unsigned=True), nullable=False)
customer = relationship("Customer", backref=backref('transaction'))
__table_args__ = (
PrimaryKeyConstraint('transaction_time', 'item_id', 'customer_id', name='PRIMARY'),
ForeignKeyConstraint(['customer_id'],['customer.customer_id']))
def __init__(self, item_id, customer_id, quantity):
#self.transaction_time = transaction_time
self.item_id = item_id
self.customer_id = customer_id
self.quantity = quantity
class CreditCards(BASE):
"""Class defining the mdl_creditcard table of the database."""
__tablename__ = 'mdl_creditcards'
__table_args__ = ({'mysql_engine': 'InnoDB'})
idx_creditcard = Column(BIGINT(unsigned=True),
primary_key=True,
autoincrement=True,
nullable=False)
enabled = Column(INTEGER(unsigned=True), server_default='1')
ts_modified = Column(
DATETIME,
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
)
ts_created = Column(DATETIME, server_default=text('CURRENT_TIMESTAMP'))
class Test(Base): # 必须继承于declarative_base的实例
# 注:有的数据库有自己特殊的字段类型,在sqlclchemy中被称为方言,这些方言字段都在sqlalchemy.dialects中
# 定义表名(必须定义)
__tablename__ = 'test'
# 定义字段
# id字段,int型,主键,自增,主键是必须的
id = Column(Integer, primary_key=True, autoincrement=True)
# name字段,varchar型,48位,不许为空,唯一约束
name = Column(String(64), nullable=False, unique=True)
# nick_name字段,varchar型,48位,索引
nick_name = Column(String(64), index=True)
# date字段,datetime型,根据生成行的时间自动添加时间,在更新时会自动更新时间
from datetime import datetime
date = Column(DateTime, default=datetime.now, onupdate=datetime.now)
# 还可以自定义字段名,这个字段的默认值是创建这个表的时间(所有的行都是这个时间)
date2 = Column(DateTime, name='new_date', default=datetime.now())
# content字段,Text型,默认值1234,server_default是设置在表上的默认值
from sqlalchemy import text
content = Column(Text, server_default=text('1234'))
# deleted字段,布尔型,默认为0(布尔字段设置默认值方法比较特殊)
deleted = Column(Boolean, default=False, server_default=text('0'))
# 复合约束和索引
from sqlalchemy import UniqueConstraint, Index
__table_args__ = (
UniqueConstraint('id', 'name', name='id_name'), # 联合唯一键约束
Index('name', 'nick_name', 'date') # 联合索引,符合最左原则
)
# 其他的方言字段
from sqlalchemy.dialects.mysql import LONGTEXT, TINYINT, INTEGER # mysql的方言(还有好多)
integer = Column(INTEGER(unsigned=True)) # 无符号整数
from sqlalchemy.dialects.oracle import DATE # oracle的方言(还有别的数据库的方言都在dialects中)
# 如果表中的字段名和python的名词空间有冲突, 则可以改字段名
_import = Column(String(64), name='import')
class Project(db.Model):
__tablename__ = 'project'
id = Column(INTEGER(10), primary_key=True)
user_id = Column(ForeignKey('user.id'), nullable=False, index=True)
container_name = Column(String(191))
name = Column(String(191), nullable=False)
description = Column(String(191), nullable=False)
image_tag = Column(String(191), nullable=False)
cpu = Column(String(191), nullable=False)
memory = Column(String(191), nullable=False)
storage = Column(String(191), nullable=False)
is_custom = Column(BOOLEAN, nullable=False)
port = Column(String(191))
user = relationship('User')
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
