class Beer(Base):
__tablename__ = 'beer'
__table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}
id = Column('id', INTEGER, primary_key=True)
abv = Column(FLOAT)
beer_id = Column(INTEGER(unsigned=True))
brewer_id = Column(INTEGER(unsigned=True))
beer_name = Column(VARCHAR(76))
beer_style = Column(VARCHAR(35))
review_appearance = Column(FLOAT(precision=3, scale=1, unsigned=True))
review_aroma = Column(FLOAT(precision=3, scale=1, unsigned=True))
review_overall = Column(FLOAT(precision=3, scale=1, unsigned=True))
review_palate = Column(FLOAT(precision=3, scale=1, unsigned=True))
profile_name = Column(VARCHAR(16))
review_taste = Column(FLOAT(precision=3, scale=1, unsigned=True))
text = Column(BLOB(2424))
time = Column(DATETIME)
def __init__(self, row):
self.abv = row[0]
self.beer_id = row[1]
self.brewer_id = row[2]
self.beer_name = row[3]
self.beer_style = row[4]
self.review_appearance = row[5]
self.review_aroma = row[6]
self.review_overall = row[7]
self.review_palate = row[8]
self.profile_name = row[9]
self.review_taste = row[10]
self.text = row[11]
f = '%Y-%m-%d %H:%M:%S'
t = to_datetime(row[12]).strftime(f)
self.time = t
class Rating(Base):
__tablename__ = "rating"
rating_id = Column(INTEGER(unsigned=True),
primary_key=True,
nullable=False)
user_id = Column(INTEGER(unsigned=True),
ForeignKey("user.user_id"),
nullable=False)
rating_type_id = Column(TINYINT(unsigned=True),
ForeignKey("rating_type.rating_type_id"),
nullable=False)
time = Column(INTEGER(unsigned=True), nullable=False)
mu = Column(FLOAT(), nullable=False)
sigma = Column(FLOAT(), nullable=False)
__table_args__ = (Index("user_id_rating_type_id", "user_id",
"rating_type_id"), )
user = relationship("User")
rating_type = relationship("RatingType")
def __repr__(self):
params = (
repr(self.rating_id),
repr(self.user_id),
repr(self.rating_type_id),
repr(self.time),
repr(self.mu),
repr(self.sigma),
)
return (
"Rating(rating_id=%s, user_id=%s, rating_type_id=%s, time=%s, mu=%s, sigma=%s)"
% params)
class Package(Base):
__tablename__ = 'jkxc_package'
package_id = Column(BIGINT(11), primary_key=True)
package_money = Column(FLOAT(10))
package_name = Column(VARCHAR(50))
package_pic = Column(VARCHAR(100))
package_describe = Column(TEXT)
package_class_hour = Column(FLOAT(5))
package_state = Column(BIGINT(9))
package_schooluid = Column(BIGINT(11))
package_detail = Column(TEXT)
package_abstract = Column(TEXT)
def __init__(self,
package_money=0,
package_name=None,
package_describe=None,
package_class_hour=0,
package_state=0,
package_schooluid=0,
package_detail=None,
package_pic=None,
package_abstract=None):
self.package_money = package_money
self.package_name = package_name
self.package_describe = package_describe
self.package_class_hour = package_class_hour
self.package_state = package_state
self.package_schooluid = package_schooluid
self.package_detail = package_detail
self.package_pic = package_pic
self.package_abstract = package_abstract
class Rating(Base):
__tablename__ = 'rating'
rating_id = Column(INTEGER(unsigned=True),
primary_key=True,
nullable=False)
user_id = Column(INTEGER(unsigned=True),
ForeignKey('user.user_id'),
nullable=False)
rating_type_id = Column(TINYINT(unsigned=True),
ForeignKey('rating_type.rating_type_id'),
nullable=False)
time = Column(INTEGER(unsigned=True), nullable=False)
mu = Column(FLOAT(), nullable=False)
sigma = Column(FLOAT(), nullable=False)
__table_args__ = (Index('user_id_rating_type_id', 'user_id',
'rating_type_id'), )
user = relationship('User')
rating_type = relationship('RatingType')
def __repr__(self):
params = (repr(self.rating_id), repr(self.user_id),
repr(self.rating_type_id), repr(self.time), repr(self.mu),
repr(self.sigma))
return 'Rating(rating_id=%s, user_id=%s, rating_type_id=%s, time=%s, mu=%s, sigma=%s)' % params
def save(self, table_name, msg, path):
con_str = f"mysql+mysqldb://{self._bt_info['USER_ID']}:{self._bt_info['NORM_PWD']}"\
f"@localhost/{self._bt_info['DB_NAME']}"
engine = create_engine(con_str)
type_dict = {
'grp': VARCHAR(10),
'code': VARCHAR(10),
'date': DATE(),
'prev_price': INTEGER(),
'price': INTEGER(),
'captured': FLOAT()
}
for day in self._result.columns[6:]:
type_dict[day] = FLOAT()
# Making DB from self._result_data
self._result.to_sql(name=table_name,
con=engine,
chunksize=1000,
index_label='idx',
dtype=type_dict)
with engine.connect() as con:
con.execute(f"ALTER TABLE {table_name} ADD PRIMARY KEY (idx);")
con.execute(f"ALTER TABLE {table_name} MODIFY idx INTEGER;")
date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
with open(path + 'backtest.log', 'a') as f:
f.write(f"[{date}] {table_name} : {msg}\n")
class Data(_db.Model):
__tablename__ = "data"
dev_id = _db.Column(TINYINT(3, unsigned=True), _db.ForeignKey(Node.dev_id))
room_number = _db.Column(TINYINT(3, unsigned=True),
_db.ForeignKey(Room.room_number),
primary_key=True)
time = _db.Column(_db.DateTime, primary_key=True)
temperature = _db.Column(FLOAT(7, 4))
humidity = _db.Column(FLOAT(8, 4))
brightness = _db.Column(TINYINT(3, unsigned=True))
class Entity(Base):
__tablename__ = 'entity'
id = Column(Integer, primary_key=True, autoincrement=True)
entity = Column(String(256))
type = Column(String(20))
salience = Column(FLOAT(precision=10, scale=2))
magnitude = Column(FLOAT(precision=10, scale=2))
score = Column(FLOAT(precision=10, scale=2))
weight = Column(FLOAT(precision=10, scale=2))
comment_id = Column(Integer, ForeignKey('comments.id'))
comments = relationship('Comment', back_populates='entity')
class PortfolioHistory(Base):
__tablename__ = 'portfolioHistory'
phid = Column(Integer, primary_key=True)
clientId = Column(String(100))
totalInvested = Column(FLOAT(precision=2))
netPosition = Column(FLOAT(precision=2))
date = Column(DATETIME)
def __str__(self):
return f'{self.date} { self.totalInvested}'
class MoneyFlowStatistic(Base):
"""
个股资金流向统计信息（沪深300）
"""
__tablename__ = 'moneyflowstatistic'
trade_date = Column(String(8), primary_key=True)
small_vol = Column(INTEGER(16))
small_amt = Column(INTEGER(16))
total_vol = Column(INTEGER(16))
total_amt = Column(INTEGER(16))
small_total_rate = Column(FLOAT(precision=4, scale=4))
small_buy_vs_sell = Column(FLOAT(precision=4, scale=4))
class TickDate(Base):
"""
分时成交数据
"""
__tablename__ = 'tick_data'
code = Column(String(6))
date = Column(String(10))
time = Column(String(8))
price = Column(FLOAT(precision=10, scale=2))
pchange = Column(FLOAT(precision=10, scale=2))
volume = Column(INTEGER(20))
amount = Column(FLOAT(precision=10, scale=2))
type = Column(String(8))
id = Column(String(40), primary_key=True)
class PortfolioHistory(Base):
__tablename__ = 'portfolioHistory'
phid = Column(Integer, primary_key=True)
clientId = Column(String(100))
totalInvested = Column(FLOAT(precision=2))
netPosition = Column(FLOAT(precision=2))
date = Column(DATETIME)
def __str__(self):
return f'{self.date} { self.totalInvested}'
# from dbconnect import getEngine
# Base.metadata.create_all( getEngine() )
class StarterPosition(BASE):
__tablename__ = 'StarterPosition'
team = Column(VARCHAR(3), primary_key=True)
year = Column(INTEGER(4), primary_key=True)
starter_1 = Column(VARCHAR(8))
starter_1_WAR = Column(FLOAT(5))
starter_2 = Column(VARCHAR(8))
starter_2_WAR = Column(FLOAT(5))
starter_3 = Column(VARCHAR(8))
starter_3_WAR = Column(FLOAT(5))
starter_4 = Column(VARCHAR(8))
starter_4_WAR = Column(FLOAT(5))
starter_5 = Column(VARCHAR(8))
starter_5_WAR = Column(FLOAT(5))
class ReliefPosition(BASE):
__tablename__ = 'ReliefPosition'
team = Column(VARCHAR(3), primary_key=True)
year = Column(INTEGER(4), primary_key=True)
closer = Column(VARCHAR(8))
closer_WAR = Column(FLOAT(5))
relief_1 = Column(VARCHAR(8))
relief_1_WAR = Column(FLOAT(5))
relief_2 = Column(VARCHAR(8))
relief_2_WAR = Column(FLOAT(5))
relief_3 = Column(VARCHAR(8))
relief_3_WAR = Column(FLOAT(5))
relief_4 = Column(VARCHAR(8))
relief_4_WAR = Column(FLOAT(5))
class Movies(db.Model):
__tablename__ = 'movies'
id = db.Column(GUID(), primary_key=True, default=uuid.uuid4)
name = db.Column(db.String(80), nullable=False, index=True)
director = db.Column(db.String(80), nullable=False, index=True)
popularity = db.Column(FLOAT(precision=10, scale=1))
genre = db.Column(db.Text, nullable=False, default="")
imdb_score = db.Column(FLOAT(precision=10, scale=1))
views = db.Column(db.Integer, default=0)
created_by = db.Column(GUID(), db.ForeignKey('admin_users.id'))
updated_by = db.Column(GUID(), db.ForeignKey('admin_users.id'))
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, default=datetime.now())
def __init__(self,
name,
director,
popularity,
genre,
imdb_score,
created_by,
views=0,
updated_by=None):
self.name = name
self.director = director
self.popularity = popularity
self.genre = genre
self.imdb_score = imdb_score
self.views = views
self.created_by = created_by
self.updated_by = updated_by
def serialize(self):
"""Return object data in easily serializable format"""
return {
'id': str(self.id),
'name': self.name,
'director': self.director,
'99popularity': self.popularity,
'genre': self.genre.split(','),
'imdb_score': self.imdb_score,
'views': self.views,
'created_by': self.created_by,
'created_at': self.created_at.strftime("%Y-%m-%d %H:%M:%S"),
'updated_by': self.created_by,
'updated_at': self.updated_at.strftime("%Y-%m-%d %H:%M:%S")
}
class Order_details(db.Model):
__tablename__ = "order_details"
# 主键,自增长
id = db.Column(db.Integer, primary_key=True)
# # '订单id；格式：年月日(8)+时分秒(6)+商家id(6)+订单号(4)',
# 商家id不够6位的前面填充0
order_id = db.Column(db.String(24), db.ForeignKey("order.order_id"))
# # '门店id',
goods_id = db.Column(db.Integer, db.ForeignKey("goods.id"))
# # '商品名称',
goods_name = db.Column(db.String(20), nullable=False)
# # '图片的url地址',
image_url = db.Column(db.String(100), nullable=True)
# # '单价',
price = db.Column(FLOAT(precision=10, scale=2), nullable=False, default=0)
# # 商品数量
num = db.Column(db.Integer, nullable=False, default=1)
# 总价格
count_money = db.Column(db.Integer, nullable=False)
def __repr__(self):
return "%s表创建好了" % (self.__class__.__name__)
class Goods(db.Model):
__tablename__ = "goods"
# 主键,自增长
id = db.Column(db.Integer, primary_key=True)
# '商品的门店id',
shop_id = db.Column(db.Integer, db.ForeignKey("shop.id"))
# '商品的菜单id
menu_id = db.Column(db.Integer, db.ForeignKey("menu.id"))
# 商品名称
goods_name = db.Column(db.String(10), nullable=False)
# '商品图片',
goods_image = db.Column(db.String(100), nullable=False)
# '商品单价',
goods_price = db.Column(FLOAT(precision=10, scale=2), nullable=False)
# '商品状态（默认为1：开启，0：未开启）',
goods_status = db.Column(db.SmallInteger, nullable=False, default=1)
# '商品备注；也就是商品简介',
goods_notes = db.Column(db.String(100), nullable=True)
# ------------关系映射------------------
# 与订单详情表的关系映射
good_datails = db.relationship('Order_details',
backref='good',
lazy='dynamic')
class MeteoData(Base):
__tablename__ = 'meteo_data'
id = Column(Integer, primary_key=True)
value = Column(FLOAT(nullable=False))
when = Column(DateTime)
type = Column(INTEGER(unsigned=True))
class Courses(Base):
__tablename__ = 'jkxc_courses'
courses_id = Column(BIGINT(11), primary_key=True, index=True)
courses_traineruid = Column(BIGINT(11))
courses_starttime = Column(DATETIME(25))
courses_endtime = Column(DATETIME(25))
courses_hour = Column(FLOAT(5))
courses_type = Column(BIGINT(9))
courses_state = Column(BIGINT(9))
courses_current_number = Column(BIGINT(9), index=True)
courses_limit_number = Column(BIGINT(9), index=True)
courses_epuid = Column(BIGINT(11))
courses_createtime = Column(DATETIME(25))
def __init__(self,
courses_traineruid=0,
courses_starttime=None,
courses_endtime=None,
courses_hour=0,
courses_type=0,
courses_state=0,
courses_current_number=0,
courses_limit_number=0,
courses_epuid=0,
courses_createtime=None):
self.courses_traineruid = courses_traineruid
self.courses_starttime = courses_starttime
self.courses_endtime = courses_endtime
self.courses_hour = courses_hour
self.courses_type = courses_type
self.courses_state = courses_state
self.courses_current_number = courses_current_number
self.courses_limit_number = courses_limit_number
self.courses_epuid = courses_epuid
self.courses_createtime = courses_createtime
class IndexStocks(Base):
"""
指数股票信息
"""
__tablename__ = "index_stocks"
code = Column(String(6), primary_key=True)
date = Column(String(10))
name = Column(String(255))
weight = Column(FLOAT(precision=10, scale=2))
class MyIndex(Base):
"""
我的指数
:param 交易日期
:param 指数点位
"""
__tablename__ = 'myindex'
date = Column(String(8), primary_key=True)
index = Column(FLOAT(precision=10, scale=2))
class HwChassis(Base):
__tablename__ = 'hw_chassis'
chassis_id = Column(u'chassis_id', INTEGER(), primary_key=True)
vendor = Column(String(length=20))
height = Column(u'height', INTEGER())
width = Column(u'width', FLOAT())
depth = Column(u'depth', FLOAT())
model = Column(String(length=20))
control = Column(Enum(u'digi', u'ipmi', u'libvirt', u'rlm', u'vmware'))
virtual = Column(BOOLEAN(), nullable=False, server_default='0')
__table_args__ = (
UniqueConstraint(u'vendor', u'model'),
{
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8',
},
)
class CurrentPortfolio(Base):
__tablename__ = 'currentPorfolio'
cpid = Column(Integer, primary_key=True)
typeOfInstrument = Column(String(255))
isin = Column(String(100))
ticker = Column(String(100))
quantity = Column(FLOAT)
clientId = Column(String(100))
averagePrice = Column(FLOAT(precision=2))
class Order(db.Model):
id = db.Column(db.String, primary_key=True)
customer_id = db.Column(db.String, db.ForeignKey('customer.id'))
date = db.Column(db.Date)
amount = db.Column(FLOAT(precision=10, scale=2))
def __init__(self, id, customer_id, date, amount):
self.id = id
self.customer_id = customer_id
self.date = date
self.amount = amount
class Product2Property(Base):
__tablename__ = 'product_property'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(30), nullable=False)
price = Column(FLOAT(precision=6, scale=2), nullable=False)
stock = Column(Integer, default=0)
product_id = Column(Integer, ForeignKey('product.id'), nullable=False)
def keys(self):
self.hide('product_id')
return self.fields
def build_tables(self):
self.temperature = Table(TEMP_TABLE, self.meta,
Column('temperature', FLOAT(6,3)),
Column('timestamp', DATETIME, default=datetime.datetime.now()))
self.settings = Table(SETTINGS_TABLE, self.meta,
Column('min_temp', FLOAT(6,3)),
Column('max_temp', FLOAT(6,3)),
Column('system', BOOLEAN),
Column('fan', BOOLEAN),
Column('timestamp', DATETIME, default=datetime.datetime.now())
)
self.climate = Table(CLIMATE_CONTROL_TABLE, self.meta,
Column('fan', BOOLEAN),
Column('ac', BOOLEAN),
Column('heat', BOOLEAN),
Column('timestamp', DATETIME, default=datetime.datetime.now()))
self.meta.create_all(self.engine)
class CurrentPortfolio(Base):
__tablename__ = 'currentPorfolio'
cpid = Column(Integer, primary_key=True)
typeOfInstrument = Column(String(255))
isin = Column(String(100))
ticker = Column(String(100))
quantity = Column(FLOAT)
clientId = Column(String(100))
averagePrice = Column(FLOAT(precision=2))
# from dbconnect import getEngine
# Base.metadata.create_all( getEngine() )
class DailyInfo(Base):
"""
日线数据
"""
__tablename__ = 'daily_info'
id = Column(String(16), Sequence('user_id_seq'), primary_key=True)
code = Column(String(6))
trade_date = Column(String(10))
open = Column(FLOAT(precision=10, scale=2))
close = Column(FLOAT(precision=10, scale=2))
high = Column(FLOAT(precision=10, scale=2))
low = Column(FLOAT(precision=10, scale=2))
pre_close = Column(FLOAT(precision=10, scale=2))
pchange = Column(FLOAT(precision=10, scale=2))
pct_change = Column(FLOAT(precision=10, scale=2))
vol = Column(INTEGER(20))
amount = Column(FLOAT(precision=10, scale=2))
class Coupon(Base):
id = Column(Integer, primary_key=True, autoincrement=True)
price = Column(FLOAT(precision=6, scale=2), nullable=False)
title = Column(String(24), nullable=False)
label = Column(String(50), nullable=False)
qty = Column(Integer, default=0)
n_price = Column(Integer)
n_product = Column(Integer)
n_category = Column(Integer)
dead_time = Column(Integer, nullable=False)
@staticmethod
def get_all_coupon(count, page):
current_time = int(int(datetime.now().timestamp()))
return Coupon.query.filter(
Coupon.dead_time > current_time).limit(count).offset(page).all()
class Species(Base):
__tablename__ = 'species'
# Here we define columns for the table species
# Notice that each column is also a normal Python instance attribute.
id = Column(INTEGER, primary_key=True)
genus_name = Column(String(250), nullable=False)
strain = Column(String(20), nullable=False)
serotype = Column(String(20), nullable=False)
description = Column(String(250), nullable=False)
accession = Column(String(15), nullable=False)
GC_percentage = Column(FLOAT(4), nullable=False)
genome_size = Column(INTEGER, nullable=False)
gene_count = Column(INTEGER, nullable=False)
plasmid_count = Column(INTEGER, nullable=False)
plasmids = Column(String(250), nullable=False)
PAI_count = Column(INTEGER, nullable=False)
sequence = Column(String(250), nullable=False)
sequencing_technology = Column(String(20), nullable=False)
class FlaskStateHost(db.Model):
__bind_key__ = Config.DEFAULT_BIND_SQLITE
__tablename__ = "flask_state_host"
id = db.Column(INTEGER(unsigned=True), autoincrement=True)
create_time = db.Column(DATETIME, server_default=func.now())
update_time = db.Column(DATETIME,
server_default=func.now(),
onupdate=func.now())
# host
cpu = db.Column(FLOAT(unsigned=True), server_default=text("0"))
memory = db.Column(FLOAT(unsigned=True), server_default=text("0"))
load_avg = db.Column(String(32), server_default="")
disk_usage = db.Column(FLOAT(unsigned=True), server_default=text("0"))
boot_seconds = db.Column(INTEGER(unsigned=True), server_default=text("0"))
ts = db.Column(BIGINT(unsigned=True), server_default=text("0"))
# redis
used_memory = db.Column(INTEGER(unsigned=True), server_default=text("0"))
used_memory_rss = db.Column(INTEGER(unsigned=True),
server_default=text("0"))
connected_clients = db.Column(SMALLINT(unsigned=True),
server_default=text("0"))
uptime_in_seconds = db.Column(INTEGER(unsigned=True),
server_default=text("0"))
mem_fragmentation_ratio = db.Column(FLOAT(unsigned=True),
server_default=text("0"))
keyspace_hits = db.Column(INTEGER(unsigned=True), server_default=text("0"))
keyspace_misses = db.Column(INTEGER(unsigned=True),
server_default=text("0"))
hits_ratio = db.Column(FLOAT(unsigned=True), server_default=text("0"))
delta_hits_ratio = db.Column(FLOAT(unsigned=True),
server_default=text("0"))
__table_args__ = (
db.PrimaryKeyConstraint("id"),
db.Index("idx_ts", ts.desc()),
{
"extend_existing": True,
},
)
def __repr__(self):
return "<FlaskStateHost cpu: {}, memory:{}, load_avg:{}, disk_usage:{}, boot_seconds:{}, ts:{}>".format(
self.cpu,
self.memory,
self.load_avg,
self.disk_usage,
self.boot_seconds,
self.ts,
)
